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,979 Comments
Jeff, yesterday ORDS 25.1.1(.141.1642) was released with, from what I could find, only 2 ‘minor’ bugfixes and a version bump.
This seems to be somewhat at odds with the “ORDS updates are provided quarterly. Minor releases, or “dot(s)” are published as required.” statement in the ORDS Best Practices webpage provided here: https://www.oracle.com/database/technologies/appdev/rest/best-practices/
From the release notes alone, one could argue this minor update is hardly ‘as required’. Can you shine a light on versioning regarding quarterly (25.1, 25.2, etc.) releases and the minor version updates (25.y.minor) and it’s security patching implications? Is it safe to assume only quarterly released versions contain critical security fixes?
Im asking because we serve a number of customers with (our) application software relying on ORDS functionality. These customers typically wait for us to release a new application software package including a new ORDS version. Should we release new application software package with _every_ ORDS release, including minor releases or is it – at least from a security standpoint – safe to stick to quarterly updates?
I guess the underlying question is; what triggers a minor release version?
The Jetty update was done to address a security issue, if you’re running Standalone ORDS, it would be a good idea to upgrade. If you take a look at the two bug fixes, they were significant problems, so if they affect you, I would say yes, take the upgrade.
I would feel Ok if you told me you patched your web tier at least 2-3x a year. I talk to customer running ORDS from 2019 or even older, that’s a very, very bad idea.
Read the release notes, and use your best judgement. The software is now supported for 18 months, where before it was 4 years.
Thanks for the quick response. We aim to follow the same cadance in providing customers with an updated software package as the quarterly ORDS updates that are released. But since there seem to be intermitted minor releases of ORDS (f.i.: 25.1.1 / 25.1 / 24.4 / 24.3.2 / 24.3.1 / 24.3 / 24.2.3 / 24.2 / 24.1.1 / 24.1) that makes this a bit confusing to our customers.
Upgrading – in general – is always a good idea ofcourse. We don’t want our customers to run outdated software, let alone unsupported software. Thanks for the advice!
Hello Jeff,
i really like that project command in SQLcl, a true game changer!
Although i appreciate that branch-based staging, i wonder if there still would be a way to do the implicit staging when deploying into production. We’ve got serveral productions and can’t rule out “wild” changes parallel to development.
I understand that the artifact only contains the release-files.
So to stay aligned with project-cmd principles, prior to deploy we’d have to export the target prod, stage against our old release and then changelog-sync to prevent redeploying wild changes.
After that we could stage the new release against the current prod snapshot, generate a target-specific release and deploy it. Quite some extra steps, but should work.
Do you see an alternative lb-approach (while still using project command) and do dynamic deployments based on the dev-snapshot in src?
Cheers
Moritz
You used the word, wild.
Going CI/CD route means killing those weeds, no more hands on in production. Everything goes thru the pipeline.
Not knowing what production looks like before you push a change seems intolerable.
The existing Liquibase command will be supported, so you could use that. But I’d still be nervous about it.
Hi Jeff,
I have an Apex related question I have been struggling with.
## Technical Question:
### Oracle APEX 24.1.0 – Application Export Fails with ORA-06502: PL/SQL: numeric or value error
Environment:
– Oracle APEX 24.1.0
– Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Issue:
When I attempt to export my APEX application (Application ID 109) via the command line or the APEX interface, I receive the following error:
“`
Error exporting flow 109. ORA-06502: PL/SQL: numeric or value error
ORA-06502: PL/SQL: numeric or value error
“`
What I’ve Tried:
– Checked the size of LOVs, computations, and processes to ensure none exceed 3900 characters.
– Reviewed recent changes to the application for any unusually large text or numeric values.
– Verified that no columns or attributes have values that exceed their defined limits.
Question:
– Is there a systematic way to identify which component (e.g., LOV, computation, process, page item, etc.) is causing the numeric or value error during export?
– Are there logs, debug settings, or export options in APEX 24.1.0 that can help pinpoint the problematic object or value?
– Has anyone encountered this error in a similar context, and what steps did you take to resolve it?
Any advice or troubleshooting steps would be greatly appreciated.
Seems like an apex issue to me, you should ask on the apex community forum, or open a SR with MOS.
https://forums.oracle.com/ords/apexds/domain/dev-community/category/apex
Dumb question of some sort. In the Monitor Sessions – Sessions Window, there is a red pin beside the Run Report, what is it actually for. It doesn’t show a balloon hint what is it? Is it supposed to be pinning something? Trying to check what is it for using the F1 Help 🙁
If you mouse over it, it says ‘Freeze Content.’
When activated, the report will stay open, even when you go to open another report. If you don’t pin it, and open a new report, the new report replaces it.
You see the same control on grids for query results and for objects like tables – it lets you have more than one thing open at a time.
In the vscode extension, is there a way to set defaults for the results export? (For example, instead of CSV and to File, defaulting to TEXT and to Clipboard. Thanks!
I have a ticket open for the dialog to remember the most recent export, so if you’re doing a bunch of exports, it’ll be way fewer clicks.
Hi Jeff – Do you know if there are any plans to incorporate the DBA functions available in Oracle SQL Developer (like being able to examine profiles and tablescpaces) into the VS Code extension for SQL Developer.
Yes, most definitely.
Hi Jeff,
Despite having the “Automatically check for updates at startup” checked, I never get prompted that an update is available or presented an option to download the current version.
Currently I am running 23.1.1.345.2114
Additionally, If I go to the check for updates (under help) I can also see a warning saying something along the lines of
“Warning: you are running JDeveloper in non-Administrator mode, hence OPatch updates and other updates that depend on OPatch will not be available” This occurs even if I run SQL Developer as administrator.
Regardless, if I click next with the search update centres checked, I still get no latest update presented to download and install? This is one Windows 10 enterprise.
Thanks
Neil
That feature is for extensions only, not the base product.
One of many reasons we’re transitioning sqldev to vs code.
24.3.1 is latest version, it has a few bug fixes over your version but no new features.
Hi Jeff,
when doing an SQLcl (25.1) LB Export (ges or geo) we experience a runtime issue / blocker when trying to export a table with a few thousand partitions (or example table has around 8000). The export of that 1 table ran for 30 Minutes before we cancelled it. 2 active db-sessions ran showing lb generate and direct path write temp.
Is there a way to exclude partitions already in the generate step? I know it’s possible to exclude those on update, but that won’t help with our current problem.
Bckground: we’re using SQLcl LB currently to sync a production schema back to dev. In Dev we normally would not have such numbers of partitions.
Cheers
Moritz
Run this to config your session before running the Liquibase commands like generate-object.
SQL> set ddl partitioning off
DDL Option PARTITIONING was set to OFF
set ddl partitioning off did the trick, only a few secs now! Makes it also easier to keep changelog files tidier as well. 🙂
thanks a lot!
No worries! Great to hear you’re sorted now.
Hi,
Is it possible the use a parameter for SQLFORMAT? I’m using the code but I’m getting “Bad Format specified”
define fmt = &1
— Optional: show what was passed
prompt Format selected: &fmt
— Use conditional logic
column format_value new_value fmt noprint
select case lower(‘&fmt’)
when ‘json’ then ‘json’
when ‘csv’ then ‘csv’
when ‘xml’ then ‘xml’
when ‘html’ then ‘html’
when ‘insert’ then ‘insert’
when ‘loader’ then ‘loader’
when ‘delimited’ then ‘delimited’
when ‘ansiconsole’ then ‘ansiconsole’
else ‘default’
end as format_value
from dual;
— Now set the format using the resolved value
set sqlformat &fmt
— Sample query
select sysdate from dual;
Kind regards
Peggy
Is it possible to display the connection name in the Tab column, without having to have a document with it in a comment on the top line? It’s been really useful to be able to jump straight to the tab I want when I have lots of connections open, and I’m not sure if I’m missing an option somewhere…
You can name it initially by connection, sure, but if you change the connection later vs code won’t let us update the name.
So for safety sake we dont do that as it could be wrong.
Hi Jeff,
Is there a way to secure single app through reverse proxy (nginx in this case) for oracle apex, because normally when you proxy some server you expose all oracle apex apps, so if someone guesses the id they have access to the app?
A great question for the apex community.
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?