Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!
If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!
Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.
Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.
- I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
- I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
- I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.
Go!
7,955 Comments
Jeff,
Are there any known issues with sqlcl and local sysdba logins (/ as sysdba)?
Getting ” Error Message = ORA-12545: Connect failed because target host or object does not exist” when I try. I have sqlcl 25.1 with the 23ai instant client configured. I have confirmed that TWO_TASK is not set. sqlplus works fine.
> sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Fri May 9 16:13:13 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.25.0.0.0
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.25.0.0.0
[DBPCDB1]:oracle:tdbaora701:/u01/oracle/dba/sqlcl
> sqlcl / as sysdba
SQLcl: Release 25.1 Production on Fri May 09 16:13:20 2025
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connection failed
USER =
URL = jdbc:oracle:oci8:@
Error Message = ORA-12545: Connect failed because target host or object does not exist
Help: https://docs.oracle.com/error-help/db/ora-12545/
USER =
URL = jdbc:oracle:thin:@127.0.0.1:1521:DBPCDB1
Error Message = ORA-12541: Cannot connect. No listener at host 127.0.0.1 port 1521. (CONNECTION_ID=R4tRoLVgT9uC386BLCajgw==)
https://docs.oracle.com/error-help/db/ora-12541/
Hi Jeff, two things I’m missing in SQLDev- empty line as a sql separator (on top of semicolon)
and tabpages instead of dropdowns to navigate through schema objects..
Having those ‘d be just great
Blank space statement terminator, too dangerous. Highlight what you want to execute.
Tabs? You are using Toad I suppose? It would be like 10 lines of tabs to fit everything.
Hey,
Is there a way to change the look/theme of the query result tab?
For VSCODE extension
It’s 100% theme based.
Hey Jeff,
SQLcl is great. In the Statusbar, is there a way to show if we are spooling?
Thank you!
Ted L.
No, but that’s a great idea.
Using Oracle FREE 23.3
SQLCL v 25.1
SQL> datapump export
Initiating DATA PUMP
Database Time Zone: VERSION:43 CON_ID:0
Log Location: DATA_PUMP_DIR:ESQL_6684.LOG
Additional Information: ORA-04036: PGA memory used by the instance or PDB exceeds PGA_AGGREGATE_LIMIT.
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3545
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4168
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4757
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 6245
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 7674
ORA-06512: at line 48
Am I toast?
Not at all, just allocate more memory to your PGA.
Can we change the settings in FREE23ai?
Can we change the settings in FREE23ai?
I tried SGA 1448 and PGA 600. Does not accept.
Do you need help finding the database docs?
I know that SGA for Free is limited to a total of 2GB for both SGA and PGA – so adjust accordingly.
I’ve been using VS Code / SQL Developer for several months now and love it! However, there are a few things missing that make me open up the old SQL Developer occasionally. I figured I would post them here for future enhancements. or if already available, someone can clue me in.
1. Copying rows, columns or group of cells in the results grid.
2. Right click menu option to start an explain plan.
3. When using CTRL-END to get to the bottom of a result set, it buffers a 100 rows and then you have to repeatedly hit CTRL-END to eventually get to the bottom.
Thanks for all the work you guys have put into this. I’ll keep using and look forward to future updates and enhancements!
You can copy rows today
1. ctrl/cmd click the row number to the far left of the grid to select 1 or more rows
2. it’s mapped to a keyboard shortcut and has a toolbar button – if you want it on a right click, access via the Command Palette
3. you want to … force all the rows back, for…what reason?
It would be super helpful if you could leave a review on the Marketplace, Dave! That helps us attract other users so they can love it, too 🙂
Great, I didn’t realize you could copy rows that way. Having that ability on the columns would be “nice to have”.
For the cntr-end thing, it’s really just a lazy way to get row counts for small returns. Not a biggy, just a habit I got into after years of using SQL Developer.
I’ll certainly post a review.
Thanks!
Column/region copy/paste is being worked on.
Right-click ‘count rows’ will usually be faster than fetching down the entire resultset – and will definitely incur fewer resources client and db server side.
Awesome, thanks again Jeff!
Jeff, got the javascript working after installing graalvm 17 and it seems to work, but I was trying to test error handling and I’m not seeing it report errors?
I created a script with a select statement with invalid table name. And it runs as if there are no errors? Not sure what I’m doing wrong and can’t find any clear documentation on it (I pulled my error checks below from various blog sources).
SQL> script
2 var binds = {};
3
4 var objects2 = util.executeReturnList(
5 “SELECT ‘x’ col1, ‘y’ col2 from dBADual”
6 ,binds);
7
8 if(objects2) {
9 ctx.write(“successful\n”);
10 } else {
11 ctx.write(“Error :(\n”);
12 var err = util.getLastException();
13 ctx.write(“\nERROR:” + err + “\n”);
14 }
15 if (ctx.getProperty(“sqlcl.error”)) {
16 throw new Error(ctx.getProperty(“sqlcl.last.err.message”));
17 }
18 for (i = 0; i < objects2.length; i++) {
19 ctx.write("cols: " + objects2[i].COL1 + "\n");
20 }
21 ctx.write("invalid stmt executed\n\n");
22* /
successful
invalid stmt executed
Post to the sqlcl forums and I’ll get you an answer.
Posted 2 days ago. No response yet.
https://forums.oracle.com/ords/apexds/post/sqlcl-js-script-doesn-t-report-errors-5708
I’ve been on a work trip, and we take these as we can. Stay tuned.
Can we use a more recent version of Tomcat than v9 to deploy yet?
Tomcat 11 now out.
You can but you’ll need to sort Jakarta issues, and we don’t technically support it.
Tomcat 9 is still under support.
Jeff, do you have any instructions for getting sqlcl to work with Graalvm 21 (jdk 21?) with Javascript support? All the existing documentation I can find talks about using “gu” to install the js support for a graalvm installation, but “gu” is no longer shipped with graalvm 21. And i can’t make sense of the instructions on the graalvm js github site for adding it and can’t seem to find any post where others have documented the process.
Nope, because it’s a pain.
What’s wrong with 17?
See Ilmar’s post.
Ok. May try 17 then. I just picked 21 because it’s listed in the 24.4 documentation:
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/24.4/sqcug/working-sqlcl.html
and it’s the most recent LTS version of java. The doc link above (and for 25.1) doesn’t mention having to do anything special for 21. Implies you can still us “gu” to install js.
Is this available in Sqldeveloper vscode extension ?
PL/SQL is being executed from ‘somewhere else’.
– https://www.thatjeffsmith.com/archive/2019/05/debugging-pl-sql-in-apex/
Tnx
External/Just in Time plsql debugging where you start a local listener and attach to another session, not YET implemented in our VS Code extension.
Hi Jeff,
I’ve been scouring the web for a definitive way to do this, and have found varying things. I was hoping you could clarify how to create a tab-delimited file using SQLcl. I’ve seen mentions of using COLSEP CHR(9), an old post where you mentioned JS (but what JS?), etc. Is there a current way to do this with SET SQLFORMAT (or any other means) in SQLcl? Currently, I’m using 22.4, as the machine I’m using it on is required to have Oracle Client 19c only. Thanks for any help you can give!
You need to upgrade to 25.1
then you can do this –
--wrap this with a spool file.tsv and spool off
SQL> set sqlformat delimited \t ' '
SQL> select * from countries fetch first 5 rows only;
'COUNTRY_ID' 'COUNTRY_NAME' 'REGION_ID'
'AR' 'Argentina' 20
'AU' 'Australia' 40
'BE' 'Belgium' 10
'BR' 'Brazil' 20
'CA' 'Canada' 20
You’re a prince. Thank you!
I try! Thanks for the feedback!
Hey Jeff – do you know if there are any plans to add functionality in the new VS Code extension to give me a notification when I log into one of my connections that my password is going to expire soon? It used to happen in the old SQL Developer Java version which was useful so I knew to change it before it expired, since it wasn’t possible (as far as i know) to change the password after it expires through the SQL Developer app and I had to go to through the SQLPlus (now SQLcl CLI tool) to change the password.
Many thanks,
Jack
If we’re not surfacing that message, it’s a bug. I’ll look into it.
OK, I looked into it, it’s a bug! Will file and sort this for you.
Thanks for your report!
Okay great thanks Jeff!
We have our own tailored data dictionary viewer, implemented using node.JS.
Thinking it might be a nice feature if we could add a button to call out to an API that generates a model diagram, as SQL Developer does.
Theoretically yes, it’s there for SQL Developer Web –
but
1 – requires ORDS
2 – assumes dictionary is in an oracle database
Jeff, is there any prospect of an API to create models the way SQL Developer does when you click on a table and it returns the immediate neighbours?
An API to create a model? How exactly would you use this API, or what would the expected output look like?