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,868 Comments
We are user the ORDS Database Management API since a few versions.
With ORDS 24.2 there were breaking changes and I think, the documentation doesn’t fit the implementation.
If I read the documentation correctly there should be a change regarding the PDBADMIN parameters:
Before 24.2: adminName and adminPwd
From 24.2. onwards: admin_user and admin_password
But in my environment using ORDS 24.4 admin_user and admin_password doesn’t work.
I get a bad request error with the message “too many parameters”
If I leave out the two parameters, it works
If I take the old parameter adminName and adminPwd, it works too – but the parameters have no effect.
Is this a known bug?
PS: I tried to create a pdb using the POST-Request at …/ords/_/db-api/stable/database/pdbs/
It’s working for me, example.
Hey,
thanks for the reply?
If I took the “old” attributes adminName and adminPwd fcr a request to create a pdb under 24.4. (or 24.3.) I could create the PDB. But I didn’t could connect to the admin user, because the user wasn’t created (If you take a other username than PDBADMIN).
Best Regards
How do you mean ‘i could not connect to the admin user’ – PDB operations have to run via the common user you create in the CDB that is there to handle your tasks that run in the container.
I meant that after creating the PDB I should be able to log in with the specified admin user and the specified password.
It looks like with ORDS 23.4 the adminPassword and adminUser parameters are ignored.
And this would be fine, if I could use the new parameter admin_user and admin_password. But using these parameters results in a “too many parameters” error. The new parameters doesn’t work.
The following body used for a POST-Request against …/ords/_/db-api/stable/database/pdbs/ results on ORDS 24.4 in the mentioned error “too many parameters”:
{
“source_pdb_name”: “xxx”,
“new_pdb_name”: “dev_xxx”,
“file_name_convert”: “(‘xxx’,’dev_xxx’)”,
“storage”: “UNLIMITED”,
“temp_file_reuse”: true,
“admin_user”: “PDBADMIN1”,
“admin_pwd”: “test1”
}
If I remove the admin-Parameters, it works:
{
“source_pdb_name”: “xxx”,
“new_pdb_name”: “dev_xxx”,
“file_name_convert”: “(‘xxx’,’dev_xxx’)”,
“storage”: “UNLIMITED”,
“temp_file_reuse”: true
}
We have tried this on two different CDBs and of course 2 different ORDS installations.
OK, now I understand. Thankfully I didn’t toss the pdb, I’ll take a quick look.
In my new pdb, PDB_SAMPLE, I can:
So I’m not seeing the request parameters being ignored.
By the way, I’m doing all this testing against the latest version of ORDS, 24.4.
The release page for ORDS (https://www.oracle.com/tools/ords/support.html) lists Jun 2027 as End of support for version 24.4, released last December. This seems to be at odds with the 18 months Lifetime Support Policy.
I’m assuming this is just a typo and it should read Jun 2026; am I right in that assumption (and can this be corrected)?
Good catch, having that fixed now. Yes, it should read 2026.
Using VSCode extension for the first time. One of my most used component of SQL Developer is the Real Time SQL Monitor. This looks good. A couple of questions. Sorting? Can the table data be sorted, sorted by date, buffer gets, etc., to pull latest or highest gets in order? I can’t seem to get anything like a sort to happen on the column headers. Activity column is showing a percentage but in SQL Developer showed percentage AND a relative “bar”, is the “bar” coming in VS code, helps with visualizing where time is spent? Last question, AWR’s, are these coming to VS Code?
Sorting/filtering coming in next update, we just ran out of time to include that in the December release.
Activity bar, I don’t believe so, but I can look into it.
AWR – most definitely. And it’ll be a BETTER AWR than what you see today, a completely changed up look and feel, and priority on giving information to show you were the performance problems are.
Hey Jeff,
On my Oracle SQL Developer Extension for VSCode v24.4.1, when I ran this package call returns a JSON array of object Clob that contain 1.7 mil chars, I am unable to see the result using “Run Statement” (but works with smaller results). When I try running the same package call using “Run Script”, the result is cut off (actually seems to be happening to any size results).
Probably a bug?
What do you have LONG set to for your session? that’ll limit the amount of text shown, i believe default is 80
I confirm that the clob for “Run Script” is 80.
Where do I find how LONG is set to for my session and change it?
Add it to your login script
I manage to debug plsql code from apex using JDWP from the sqldeveloper application
SQL Developer for VS Code there is also JDWP option for debug, When it start from VS Code there is not listen ip/port waiting for connections
Old sqldeveloper application start JDWP with IP and Port number so you can connect from external application (Apex) and start debug
exec DBMS_DEBUG_JDWP.CONNECT_TCP(‘127.0.0.1’, ‘65000’);
We haven’t built that, yet. You’ll be debugging the procedure directly in vs code, not attaching from apex.
im trying to upgrade my apex installation on RDS and EC2. i upgraded ORDS to 24 but getting this 404 error on the apex route. [ORDS-22001]. ORDS is serving fine but cant get apex to work, doesnt look like i have apex240100 in my db either. RDS is a little weird to run this on and i couldnt get much help from their support. i saw some comments about the APEX_PUBLIC_ROUTER and some issues with prehooks? but not sure if related. any insight?
What errors do you see in ORDS log at startup it when you access apex?
in standalone it doesnt throw me an error. i get a valid db pool but then the
404 : The procedure named apex could not be accessed, it may not be declared, or the user executing this request may not have been granted execute privilege on the procedure, or a function specified by security.requestValidationFunction configuration property has prevented access. Check the spelling of the procedure, check that the execute privilege has been granted to the caller and check the configured security.requestValidationFunction function. If using the PL/SQL Gateway Procedure Allow List, check that the procedure has been allowed via ords_admin.add_plsql_gateway_procedure.
in the pool.xml I have used
wwv_flow_epg_include_modules.authorize
Not sure exactly what user/proc is getting 404d here. and if my ords install is incomplete since im missing the upgraded schema
ORDS can’t execute an apex procedure if the apex procedure doesn’t exist. That’s step 1 for you to figure out, is the APEX schema there as expected, and everything compiled/avail w/o errors.
doesnt throw an error in the buffer of the standalone buffer that is. i get the 404 in the http access logs and shown in browser
No it isn’t there.
I can’t install apex from the apexins.sql script since RDS doesn’t give my admin user sysdba perms. I think the ords install added the apex schema last time I upgraded. But this install process didn’t do it. Are you familiar with a way to execute the apex install on an AWS RDS without access to a sysdba role?
Gotta sort apex first.
Indeed. That’s my question though. If it doesn’t install as part of ords and I can’t run the install script. Is there another way to accomplish this with the limited perms in RDS?
The RDS docs specify how to do this
their docs have the steps to add option groups, unpack the apex and ords installs, but thats in. the apex_listener section is deprecated and none of the steps in those docs actually install apex.
i found this doc most helpful to get ORDS running :https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.APEX.ORDSConf.html
and get the landing but again, no apex. none of their doc sections have a step that gets me APEX
I’m not on the apex team but I can put you in touch via email.
i would appreciate any help at all. thank you!
Consider opening a support ticket with AWS.
This would be much easier, cheaper, and faster than on OCI.
Hey Jeff,
Since it doesn’t look like it currently exist, is there a unit testing type of feature that is on the vision for the VS Code Extension? Probably something that act similar to Java JUnit in a way https://junit.org/junit5/docs/current/user-guide/
We’re about to publish a SDK that developers can use to add features to/with our extension, and that would include things like the open source ut/PLSQL project, which already has an extension for SQL Developer ‘classic.’
SQL Developer for VSCode version 24.3.1
SELECT queries incur EXCEPTION when table contains a column of type NESTED TABLE.
I have recreated this issue on two different tables.
SQL Developer 23.1.1 works fine, does not incur exception.
SELECT * FROM MYTABLE;
/* An error occurred while running the statement */
Same error occurs in the Data (tab) when browsing the table
/* Error executing ‘SELECT ID, NESTED_COLUMN, ROWID AS “ora_rowid” FROM (“MYSCHEMA”.”MYTABLE”) ‘ */
DDL / DML to reproduce the issue follows.
CREATE TYPE MonthDayType AS TABLE OF VARCHAR2(20);
/
CREATE TABLE MyTable
( id NUMBER PRIMARY KEY,
nested_column MonthDayType
)
NESTED TABLE nested_column STORE AS nested_month_day_tbl;
/
INSERT INTO MyTable (id, nested_column) VALUES (1, MonthDayType(‘January’, ’01’));
/
INSERT INTO MyTable (id, nested_column) VALUES (2, MonthDayType(‘February’, ’14’));
/
INSERT INTO MyTable (id, nested_column) VALUES (3, MonthDayType(‘March’, ’21’));
/
COMMIT;
/
SELECT * FROM MyTable;
The grid doesn’t support that object type yet, but we’re working on it, for now use ‘execute as script’ for a workaround.
Hello Jeff. I have a problem with sqlcl/liquibase (v24.3). I want to execute a “runOracleScript” changeset with sourceType=“FILE”. Even with a simple spool and “select * from dual;” I get the error:
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset db_changesets/testfile.sql::raw::includeAll:
Reason: liquibase.exception.DatabaseException: ORA-00900: invalid SQL statement
https://docs.oracle.com/error-help/db/ora-00900/ [Failed SQL: (900) spool spooltest_file.log
select * from dual;
spool off]
ORA-00900: invalid SQL statement
With sourceType=“STRING” and the statements directly in the changeset it works!
Do you have a working example for sourceType=“FILE” ?
By the way, the documentation at https://docs.oracle.com/en/database/oracle/sql-developer-command-line/24.3/sqcug/examples-using-liquibase.html is very short about the parameters for runOracleScript. For example: What is “objectName” and “ownerName” for?
Thank You, Flo.
It’s supported and we use it internally all the time.
The ‘raw’ indicates you’re not using runOracleScript.
Can we see your actual XML changeSet?
It should look something like this
Thank you for your answer. Here is the changeset listing:
db_changesets\testfile.sql
I was able to solve the problem. My mistake was that I used a root changelog with a
.
In the “db_changelogs” directory were the other changelogs with
AND THE SQL FILES next to it.
The “lb update” interpreted the SQL files as separate CHangelogs and threw the ORA-00900 error.
Solution for me:
in root-changelog to ignore *.sql files as separate changelog.
Hi Jeff. SQL Developer 21.2 on Windows 11 Pro. This behavior changed recently. I connect to a database in the Connections pane. Drilling down and clicking or opening any object, the new pane opens in the section with the Connections pane, rather than in the section with all the database worksheets/tabs/panes. I can drag the new object pane to where I want it. But I want it to start in a new tab next to the database worksheets/tabs/panes, like it used to.
Windows > Reset Desktop to Factory Settings
Thanks Jeff!
Hey Jeff, could you see the listing in my last reply? It seems that the XML code has been filtered?
Yes but we’re all on break till 2025
Hi Jeff. Having recently shifted away from good old EBS to Oracle Cloud Apps (Fusion), I’m REALLY missing SQL Developer. Is there any way to connect to the Fusion HCM/FIN/SCM databases using SQL Developer?
Your real question is do those SaaS offerings support access to the database?
In most cases, what they offer is the ability to copy your data on a routine basis to Autonomous Data Warehouse, and then you can query/connect with SQL Developer.
This isn’t a SQL Developer or ‘Jeff’ question, it’s more about what access the Applications are making available.
Hi Jeff,
https://www.thatjeffsmith.com/archive/2021/12/oracle-sqlcl-datapump/
I am attempting to utilize the data pump export/import feature from VSCode SQLcl. My objective is to export the schema directly into an AWS S3 bucket. However, I am struggling to formulate the appropriate command for this task. Could you please help me with a sample command that I can use to export/import the schema directly from S3?
Thanks in advance 🙂
We don’t support that, you’d export it to OCI object store.
I’m sure you could script it though…
Thanks for the confirmation.
In this case, I will check if I can directly export the schema to ATP storage. To optimize storage usage, I would like to implement the compression option for both data and metadata. Do you have any insights into potential issues or concerns related to the use of compression options in the export process?
Hi Jeff
I am trying to deploy REST API curl commands to query data and I am getting the OutOfMemoryError: Java heap space error occasionally. Do you have any advice on how to avoid this?
Thanks
Andy
I have a really, really trite reply to this, and I apologize in advance, but… give ORDS more memory?
To give you a better answer, I would need a better understanding of your runtime environment, load on ORDS, and types of requests/payloads you’re dealing with.
Hi Jeff, I’ve just installed, in my usual working machine, the new version of Sqldeveloper (24.3) and It’s came with some issues. I know this should be related to the Sqldeveloper preferences, because the previos version (23.1), I’m still got it and I can compare, doesn’t have it.
Examples:
1.- If I execute the query “select 1 numero, sysdate fecha, ‘HOLA’ caracter from dual;”, the number 1 is displayed like “oracle.sql.NUMBER.f18cf09”. The date and varchar data are ok.
2.- I cant’t select some item in the result grid and copy (Ctrl+c) to the clipboard, always remember the last characters copied in the worksheet…
I can’t figure out what kind of setting could be involved in this behavior
I would appreciate any tips
Thank you very much
Issue 1: Sounds like you have an oracle 21c or older client configured in sqldev – remove that, or replace with oracle 23ai client.
Issue 2: does using your mouse vs keyboard help? What OS are you on?
Thanks Jeff, both issues resolved….I’ve upgraded the intantclient to 23.6 and voilá…all is ok
kind regards
Mauricio
This is regarding ORDS and limiting downtime when using multiple database connections.
We run dual WebLogic servers, each running one ORDS servlet. Each ORDS has 7 DB pools connecting to 7 PDBs.
We are in the process of a rolling (PDB by PDB) upgrade of APEX. During the upgrade, we disable access to the PDB via web redirects, but do not shut down ORDS in order to retain access to the OTHER PDBs. After the upgrade, ORDS will no longer connect to the new APEX, and it seems that only a complete restart of the WebLogic managed server / ORDS fixes it. This causes a brief (about 1-2 minute) outage for ALL of our PDBs and the 30+ production, mission critical APEX applications we are running.
Question is: Is there some way around this full outage? Is there a way to tell ORDS to completely refresh a single connection pool without a full restart, so that it connects properly to an upgraded APEX? PS, this is also a problem whenever we have to change the ORDS_PUBLIC_USER password since ORDS does not seem to reread its wallet unless it is restarted.
Thanks!
That shouldn’t be happening, what version of ORDS?
Also, you’re not actually making any changes to ORDS config itself, it’s pools never get shutdown? Even if the PDB ‘goes down’, when you make a request, we should attempt to re-create the pool. If that fails, there’s a TIMEOUT before we try it again.
What about REST APIs on those pools or SQL Developer Web, are they reachable? Or is this only for APEX?
I am working on my home rig, not in the office, so unsure where to post bug reports.
Open & logon to legacy SQL Developer, and export my custom Type UTBAG. My backup copy.
Launch VS Code and update to version 24.3.1
I discovered the bug details below and have been waiting for this update. But the bug persists It is reproducible on my rig.
Logon to my Schema in VS Code and edit my UTBAG Type body and compile it. Note spec is not open in a worksheet.
Notice the output informs that the Body compiled ok.
Followed by PLS-00304 !
Type Body UTBAG compiled
LINE/COL ERROR
——— ————————————————————-
0/0 PL/SQL: Compilation unit analysis terminated
1/11 PLS-00201: identifier ‘UTBAG’ must be declared
1/11 PLS-00304: cannot compile body of ‘UTBAG’ without its specification
Errors: check compiler log
Next I open the UTBAG Type specification. It has been wiped out. All that remains is a single line.
create or replace
I’ve experienced this occasionally in Legacy SQL Developer previously. But, I am unable to consistently reproduce it. In VS Code it is easily reproduced. I suspect it a bug in Oracle DB.
Oracle Database 21c Standard Edition 2 Release 21.0.0.0.0 – Production
However this bug occurs repeatedly using VS Code making this platform unusable for me.
Using my backup file, I paste the spec into the worksheet & recompile the Type specification, then close the worksheet.
Type NEO.UTBAG compiled
Next re-open the Type body and recompile. SAME ERROR is reproduced.
I need a test case.
Reproduce the bug with sample bugtype provided. Please test with identical version of
Oracle Database 21c Standard Edition 2 Release 21.0.0.0.0 – Production
Compile both the Type spec, and body using legacy SQL Developer.
Logoff
Launch VS Code
Open / Edit Type Body in worksheet
Click compile.
SCRIPT OUTPUT (tab)
Type Body BUGTYPE compiled
LINE/COL ERROR
——— ————————————————————-
0/0 PL/SQL: Compilation unit analysis terminated
1/11 PLS-00201: identifier ‘BUGTYPE’ must be declared
1/11 PLS-00304: cannot compile body of ‘BUGTYPE’ without its specification
Errors: check compiler log
Open Type spec in worksheet. Spec now has 1 line only.
create or replace
————————————————————————————————
CREATE OR REPLACE EDITIONABLE TYPE “MYSCHEMA”.”BUGTYPE”
authid current_user
as object
(
label varchar2(4000 char)
, constructor function bugtype(self in out nocopy bugtype
, v_label_in in varchar2) return self as result
)
/
CREATE OR REPLACE EDITIONABLE TYPE BODY “MYSCHEMA”.”BUGTYPE”
AS
constructor function bugtype(self in out nocopy bugtype
, v_label_in in varchar2) return self as result
is
begin
label := v_label_in;
return;
end;
end;
/
I’m not going to test with 21c, i’ll test with 23ai and 19c though.
Hello Jeff,
Could you please shed a light about the circumstances under which the thick/instant client is the preferred option to be used instead of the thin one?
Thank you very much
Panagiotis
With the latest driver, 23ai, there should be little to no reason to opt for THICK connections.
Hi Jeff,
Thanks for the new SqlDeveloper 24.3 version but, besides the Excel nasty import/export bug, I noticed another annoying behavior.. In the status bar at the bottom there is a persistent “Waiting for StatementRunner Task to Finish” with a swinging bar. I have nothing running AFAIK, so I suspect that this is a regression (the previous version did this also, but rarely).
Thanks
Folks on forums are also reporting this, and i’m trying to put together a reproducible test case. Thanks for sharing that information as well, it helps!
Hi there,
Is there a handy function hidden somewhere in oracle dbms, apex or ords_metadata packages to parse the url query parameter json to a sql where predicate?
We need to build a sdk to do this
Ah, ok, I’ll have a go at it myself in the meantime.
newbie sqlcl question, and I may be missing the obvious
from my Windows machine, I have sqlcl working fine
– but how do I connect to all my other remote databases/pluggable dbs (mostly on Linux but some cloud) using my tnsnames.ora aliases??
FYI all those aliases work from sqlplus/tnsping, so no networking problems
i.e. have heaps of aliases that all work from sqlplus
– surely I don’t have to enter the whole address for each connection instead of using my tns aliases
Thanks
John
can’t use oci client?
Not required for TNSNames but it is supported – now requires a 23ai client if you want to use a thick connection vs thin (jdbc)
What does this commamd return?
show tns
Thanks –
put in 23ai client and it works fine now
First, thanks to you and the team for SQL Developer 24.3.
I bring up SQL Developer 24.3, import everything from the previous version, click on ‘Help’ and then click on ‘Check for Updates’. It brings up the ‘Select update source’ panel, which contains the following warning:
WARNING: You are running JDeveloper in non-Administrator mode. Hence, OPatch updates and other updates that depend on OPatch will not be available. To view OPatch updates, please run JDeveloper in an admin mode.
Please explain the warning and how to resolve it when running SQL Developer.
Check for Updates is just for extensions, you don’t need it for updating SQL Developer itself…so just ignore it.
Hi Jeff
I am using SQLcl on Windows – in PowerShell and a C# Form from where I call sqlcl.exe.
Works fine until I decided to migrate SQLcl to version 24.3 and JDK 17.
Now it says “Error Message = no ocijdbc23 in java.library.path” when I try to connect to a 19.24 DB.
Windows Server 2022 Standard with a Oracle 19 DB installed,
Using the following directory-structure:
sqlcl
jdk
sqlcl
bin
lib
That “jdk” directory is coming from the SQL Developer 24.3 installation, contains the Java 17.0.13 version.
What is missing ?
Last working setup: SQLcl 23.3 and Java 11
Regards, Martin
It’s trying to load the thick client – either because the oracle client is in your path, or you’re using a feature that requires it
in sqlcl now you can now use the ‘-thin’ flag to force the local jdbc driver to be used
sql -thin hr/oracle@localhost:1521/freepdb1
Hey Jeff,
I have a quick question regarding the Sqlcl CLI tool. When saving connections using the connect command with -save flag, is there a way to export these connections to a file or something with a passphrase that can be used to import to another PC? I would like to export my connections from Sqlcl, including passwords like I can in SQL Developer.
Many thanks,
Jack
They’re already in files. Just copy up the connections directory and move them to your other PC.
Hi Jeff, I’m trying to process a simple html form with its action invoking an ords post service.
The html code is:
Nombre:
Apellidos:
Profesión:
Edad:
The handler is: BEGIN www_ords_pruebas.complex_post_v1(p_nombre => :nombre, p_apellidos => :apellidos, p_profesion => :profesion, p_edad => :edad, p_body => :body); END;
And I have defined all the explicit parameter like this:
ORDS.DEFINE_PARAMETER(
p_module_name => ‘prueba04’,
p_pattern => ‘con_param’,
p_method => ‘POST’,
p_name => ‘pf_nombre’,
p_bind_variable_name => ‘nombre’,
p_param_type => ‘STRING’,
p_source_type => ‘URI’,
p_access_method => ‘IN’,
p_comments => NULL
);
The procedure parameters’s values are all null, except :body. My output is like this:
{
“NOMBRE”:”Sin data”
,”APELLIDOS”:”Sin data”
,”PROFESION”:”Sin data”
,”EDAD”:”Sin data”
}
———————– BODY INI ——————————-
pf_nombre=Jimmy&pf_apellidos=Page&pf_edad=70&btn_submit=Enviar
———————– BODY FIN ——————————-
How can I get the values from the form in the handler??
Thank you very much
This the html code
”
Nombre:
Apellidos:
Profesión:
Edad:
“
I’m sorry, but I don’t know how to post the html code.
The input are like this: input type=”text” name=”pf_nombre” id=”nombre” value=”Jimmy”
and the method=POST and action=”http://192.168.0.150:8080/ords/pru19c/prueba04/con_param”
Turns out we built some cool tech into ORDS for FORM POST actions, including multi-file uploads. The docs and some blog samples are pending any day/hour now.
Are there any plans to add ORDS to https://endoflife.date?
I have a job that scrapes https://www.oracle.com/tools/ords/support.html to alert our app teams when their ords version is getting old. It works, but using an endoflife.date api would probably be more reliable.
Today is the day I learned this site even exists.
I see they have APEX listed, if they’re scraping our pages, then they should be able to see the ORDS info as well.
Also remember that our new support windows for apex, ords, etc is 18 mos vs 5 yrs.
Since endoflife.date isn’t being populated for ORDS, is there a better lookup source of version dates than the support page?
It currently doesn’t have 24.3 on it.
It came out just last week, it’s 18 months from the month it was release, so you have a year and a half.