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!
8,035 Comments
Jeff – I’m setting up a test/dev x86 Oracle Linux server for experimenting outside of OCI for Oracle AI/ML development etc. I’ve included all the Oracle specific AI/ML libraries, SQLcl, vscode w/SQL Developer, and I’m able to run 23ai Free database natively on this Linux instance or the container version and both versions contain Apex and ORDS pre-installed.
The 23ai Free version doesn’t contain any of the AI/ML specific parts of the OCI version of 23ai other than Vector. Would it be worth installing OML4Py Server on my machine to capture as much as the features I’m missing in 23ai Free? Is there another way to install a full set of 23ai AI/ML features in the 23ai Free DB? TNX!
All of the 23ai features should be there, even in FREE.
When you say there are missing ‘AI/ML’ libraries or features, can you be more specific?
Hello Jeff.
I’m using Sql Developer’s (Version 24.3.1.347) Data Modeler to show the parent/child relationship hierarchy for a set of tables. So, start with the parent table and show all the child tables (and their dependents) in the tree. Is there a setting in Sql Developer that controls the depth level of the dependency tree? The problem I’m having is: it doesn’t show any of the dependent tables below the 3rd level. In other words, I have six tables: T1 -> T2 -> T3 -> T4 -> T5 -> T6, but Data Modeler only shows T1 -> T2 -> T3, but not T4 -> T5 -> T6.
Any thoughts?
Thanks.
If you have imported the entire schema in your SQL Developer Data Modeler design, it will show everything.
If you are looking at the diagram in SQL Developer’s Table page, then yeah by design we only grab the first couple of layers of relationships.
Hey Jeff, Thank you for this amazing tool Sqlcl. Quick question ive downloaded sqlcl 25.2.2.0 and i connect to an external oracle database and im trying to run sql apex export application command and it throws me “Apex is not installed error”.
1. Does this version support oracle apex commands or should i download any extension (.jar files) that i have to download manually and place it under sqlcl/lib?
2. Should i try downloading any other previous versions of sqlcl where apex command is supported with sqlcl?
The apex commands rely on apex plsql apis in the database. If apex isn’t installed in the database, than the apex commands aren’t going to be able to do much.
If you do have apex installed, can you confirm the version?
APEX version installed is 21.2. i’m connecting to a remote database via sqlcl where apex applications are created/stored.. im running the commands on the weblogic server where sqlcl and liquibase are installed.
commands don’t run on weblogic….they get executed on the oracle database you’re connected to
what do these commands come back with –
SQL> apex version
APEX Version 24.2.0 is installed.
SQL> apex list
WORKSPACE_ID WORKSPACE APPLICATION_ID APPLICATION_NAME BUILD_STATUS LAST_UPDATED_ON LAST_UPDATED_BY
10 INTERNAL 4000 Oracle APEX App Builder Run and Develop
10 INTERNAL 4020 Oracle APEX Create App Wizard Run and Develop
10 INTERNAL 4050 Oracle APEX Internal Administration Run and Develop
10 INTERNAL 4100 Oracle APEX Home Run and Develop
10 INTERNAL 4155 Scheme Authentication Login Run and Develop
10 INTERNAL 4300 Oracle APEX Data Workshop Run and Develop
10 INTERNAL 4350 Oracle APEX Workspace Administration Run and Develop
10 INTERNAL 4411 Oracle APEX Builder, Wizard Messages and Native Plug-Ins Run and Develop
10 INTERNAL 4470 Oracle APEX Runtime Messages Run and Develop
10 INTERNAL 4500 Oracle APEX SQL Workshop Run and Develop
10 INTERNAL 4550 Oracle APEX Workspace / INTERNAL Login Run and Develop
10 INTERNAL 4600 Oracle APEX Issues Run and Develop
10 INTERNAL 4650 Oracle APEX Feedback Run and Develop
10 INTERNAL 4700 Oracle APEX New Service Signup Run and Develop
10 INTERNAL 4750 Oracle APEX Productivity and Sample Applications Run and Develop
10 INTERNAL 4850 Oracle APEX RESTful Services Run and Develop
12 COM.ORACLE.CUST.REPOSITORY 8842 Universal Theme Legacy Run and Develop
12 COM.ORACLE.CUST.REPOSITORY 8842.242 Universal Theme 24.2 Run and Develop
SQL>
After connecting to the DB and servicename I tried running this
SQL>SELECT STATUS FROM DBA_REGISTRY
WHERE COMP_ID = ‘APEX’;
COMP_ID COMP_NAME VERSION STATUS MODIFIED NAMESPACE CONTROL SCHEMA PROCEDURE STARTUP PARENT_ID OTHER_SCHEMAS
__________ _____________________________ __________ _________ _______________________ ____________ __________ ______________ ________________ __________ ____________ ________________
APEX Oracle Application Express 21.2.0 VALID 10-FEB-2022 00:53:43 DBTOOLS SYS APEX_210200 VALIDATE_APEX
SQL> apex version
APEX is not installed stopping execution.
SQL> apex list
APEX is not installed stopping execution.
Hi Jeff, thanks for the previous responses. any further suggestions that i could check to make this work?
What do you see when you read this query?
select version_no,
(
select table_owner
from all_synonyms
where synonym_name = 'APEX_T_EXPORT_FILES'
and owner = 'PUBLIC'
) export_coll_owner
from apex_release
Hello Jeff,
After upgrading ORDS from 20.2 to 25.2, facing issue wherein i cannot connect to Rest Development from sql developer client. It keeps giving error “HTTP/1.1 401 Unauthorized”
ORDS logs showing below error:
OAuthException [error=INVALID_CLIENT, description=The related client is invalid, cause=null]
at oracle.dbtools.oauth.OAuthException.invalidClient(OAuthException.java:173)
at oracle.dbtools.oauth.OAuthClientAuthenticatorBase.authenticate(OAuthClientAuthenticatorBase.java:80)
Wondering if something changed in new version with respect to connectivity from sql developer client?
Please note that ORDS is deployed over weblogic and EnforceValidBasicAuthCredentials is set to false
Thanks
You don’t need to use that interface, anymore.
You can manage your REST APIs directly in the Connection Tree…or log into SQL Developer Web, and use the REST Workshop there.
Any idea why this is failing? It works fine anywhere other than in this extension.
update ps_group_control
set process_instance = 0
where group_bu = ‘XXXXX’
AND GROUP_ID = ‘6389’;
ERROR:
Error starting at line : 2 in command –
AND GROUP_ID = ‘6389’
Error report –
Unknown Command
Thanks!
increase logging level to TRACE
run the DML, again
share what you see in the Output panel, with filter set to SQL Developer – Log
Here is the trace. I will say it works if I select the whole block of SQL.
[07/29/25, 13:41:28.822] [Worksheet ] [INFO ] Action details
{
“action”: “Attach”,
“message”: “Attached”,
“connection”: {
“name”: “FNPLT”
},
“session”: “http://localhost:58062/20221610/databases/connections/sessions/t8LFfbyz4dffpoydmTFzhA/”,
“worksheet”: {
“uri”: “/c:/Users/itxxxx/Documents/SQL Scripts/FNPLT.sql”
}
}
[07/29/25, 13:42:10.928] [SQLcl ] [INFO ] Terminal SQLcl – FNPLT closed due to exit code undefined and reason 2)
What else is in the worksheet, around your
@FNPLT.sql
?
What’s in that file?
Thanks Jeff!
There was a ton of SQL in that file, and towards the bottom I found an unmatched quote. I wouldn’t have expected anything below the line I was running to impact it, but now I know.
The parser can be finicky…thanks for sticking with me/us there!
Hi Jeff,
Installing version 24.3.1.347 Build 347.1826 on windows 10. I used powershell to install openjdk 17, and then unzipped the latest sqldeveloper with no jre. I also attempted to remove the version 11 java jdk. The current situation is, sqldeveloper launches, and puts two consecutive messages up saying that it’s not compatible with jdk version 11 which must be dismissed. Then it launches, and seems to run fine. I haven’t tested it extensively, but it imported my connections from my previous version and it’s able to connect to my instance OK. This is an annoyance really, because as I said, it seems to work. Thanks for any insight you can provide.
Best
Bruce
Figured it out. I had to find the location of my installed jdk. I used powershell and winget to install the Microsoft build of openjdk 17. I used the winget defaults to install it. I found my jdk at C:/Program Files/Microsoft/jdk-17.0.16.8-hotspot
So I opened %APPDATA%/sqldeveloper/24.3.1/product.conf, and edited line 20 from:
# SetJavaHome /path/jdk
to
SetJavaHome C:/Program Files/Microsoft/jdk-17.0.16.8-hotspot
My sqldeveloper now opens without issue. Sorry I didn’t find this before posting the question.
Bruce
the download includes java, no need to download a new one or configure anything
Hi Jeff,
i’m currently evaluating the usage of the vscode plugin in my team. We have a ISO-8859-1 encoding in our databases. In the “old” sql-developer i was able to change the encoding, but i cannot find the setting in the vs-code plugin.
That leads to the problem, that german umlauts are not correctly compiled to the package.
I can enter an umlaut, but on compilation it seems like its converted to utf-8 and is broken. Any way to change NLS_CHAR?
best regards,
David
That encoding setting is working with files.
Were unicode and always have been.
I believe there’s a vs code setting to define the default file encoding m for your editors.
Yes, this setting exists (files.encoding) – and the pks file is opened with this encoding i’ve configured, Nevertheless, it does not seem to be recognized at save (compilation) as the umlaut is saved in utf-8, even if i explicity say that the file should be saved as iso-8859-1.
Can you provide an example of the problem you are encountering?
Lets say i have following procedure:
create or replace PROCEDURE PRINTINVALIDPACKAGES
as
object_name varchar2( 1000);
CURSOR c1 IS SELECT object_name from user_objects where status=’INVALID’ and object_type like ‘PACKAGE%’;
begin
OPEN c1;
loop
fetch c1 into object_name;
exit when c1%NOTFOUND;
dbms_output.put_line( ” || object_name);
— Ä Ü Ö ä
end loop;
close c1;
END PRINTINVALIDPACKAGES;
Opened from the database, i included the comment with the german umlauts (hope it displays right here…)
Now, i save this via STRG+S.
Procedure is compiled – and content of the procedure is changed:
create or replace PROCEDURE PRINTINVALIDPACKAGES as
object_name varchar2(1000);
CURSOR c1 IS
SELECT object_name
from user_objects
where status = ‘INVALID’
and object_type like ‘PACKAGE%’;
begin
OPEN c1;
loop
fetch c1 into object_name;
exit when c1%NOTFOUND;
dbms_output.put_line(” || object_name);
— à à à ä
end loop;
close c1;
END PRINTINVALIDPACKAGES;
(see the comment)
Hey Jeff,
If we’ll implement a tomcat within a container and ORDS just like a VM will it still be supported from oracle?
Also how can we create ORDS for K8S when we got external ORACLE DB (outside the cluster) with an operator?
Thanks,
Dolev
Yes
Ords is the k8s operator for oracle.
Thanks for your answer,
about the second question will it actually work if the ORDS is on the cluster and the DB is outside the cluster?
I think if you want to use ords as the k8s operator on the databases, like to create or clone a pdb…then it needs to be on the same cluser as the db.
Sorry if I have misread your question. If that’s the case, i need more details. I only spell K8s, I don’t work with it.
Hi Jeff,
I have just started to use the VS code version of SQLDEV and it is really good. Keep up the good work 🙂
I have noticed that some of my PL/SQL source files have been flagged in VS code as having a syntax error, but when I compile it, it succeeds without error or warning. Is this a bug in VS code or the extension?
Also are you planning to enhance the source code formatting for PL/SQL?
Many thanks,
Mark.
Were using the same parser here and sql developer… do you have any examples you can share?
Hi Jeff,
Thanks for the reply.
The following snippet of code is within one of my PL/SQL package specification that compiles without error or warning but reported by SQLDEV and VScode as a syntax error.
c_max_json_key_length CONSTANT SYS.STANDARD.PLS_INTEGER := 128;
SUBTYPE json_key_st IS
SYS.STANDARD.VARCHAR2(c_max_json_key_length BYTE);
It is the VARCHAR2 portion of the SYS.STANDARD.VARCHAR2 clause in the subtype definition that is reported as a syntax error – “Syntax error. Partially recognized rules (railroad diagrams):”.
Also on the subject of parsing, do Oracle publish a complete specification of the PL/SQL grammar used in 23c? I could create such a grammar file myself using the many railroad diagrams in the documentation, but using a vendor supplied and supported grammar would be my choice if possible.
Many thanks in advance,
Mark.
The same parser issue we see in ALL of our tools…and yes, our parser uses the syntax diagrams published in the docs to build our grammar.
set serveroutput ondeclare
x SYS.STANDARD.VARCHAR2(250);
begin
x := 'weird';
dbms_output.put_line(x);
end;
/
Just curious, do you do that with ALL of your data types? That’s some intense security precautions.
Hi Jeff, did you get chance to see my latest reply?
Thanks,
Yes.
Hey Jeff,
My team is getting a LLM/AI code chat bot that will be hosted on our internal network.
It has been trained on a variety of programming language including Java and basic SQL but not PL/SQL.
The goal is to maximize the AI so we can code more efficiently. Would you happen to have any resources/tip/reference on how we can train this AI on PL/SQL??
Mmmmm, not really. I could show you how to augment your LLM via RAG, or how to hook up your LLM directly to our database via MCP Server, however.
Hello Jeff,
we are currently trying to move our ORDS to openshift,
Is there any good operator for this from oracle or the only way is to perform a helm installation?
I’ll appreciate your comment.
Idan
Openshift doesn’t support oracle java, so ords isn’t supported on that platform
Doesn’t mean it won’t work…
Hello Jeff,
Thank you for this great ORDS product and all the help/documentations. Recently i upgraded Apex 20.2 to 24.2, ORDS 21.4 to 25.2 , OHS 12c to 14c. Post upgrade i am able to run ORDS API’s however apex is not launching with error “Failure of Web Server bridge:” Internal Server Failure. Cannot Continue.
It seems this is a bug 38150252 as per MOS note 3080572.1 as i see similar messages in OHS log. Not sure if this bug is with ORDS or weblogic and whats the ETA as i am stuck. Also the workaround mentioned in the note doesn’t help.
Will be creating SR as well
Thanks,
Vivek
It’s a bug between WLS and OHS. If you straight up run just ORDS (standalone), it’s fine.
If you just run ORDS in WLS, with no OHS, it works.
Thanks for the revert Jeff. Yes direct access to ORDS working fine and after i reverted back to 12c OHS everything works. Clearly something related to OHS14c and WLS. Created SR with OHS team and will follow up. Thanks again
Hi Jeff
I’m facing some issue with Oracle APEX 24.2 to get proper RESFul API based on a package for three tables.
In fact I could generate the table packages and then created the AutoREST objects, but the final endpoints creted does not seem what I expected.
I followed the steps :
1o) Open SQL Workshopand acess Utilities ==> Method on tables
2o) Put package name : PKG_Customer
3o) Select the three tables to crete the package : tcustomer, tcity, tsalesperson
4o) Created the package PKG_Customer
5o) Then I went to SQL Workshop ==> RESTFul Services == Create AutoREST objects, it generated the endpoints on full URL : http://localhost:8080/ords/wsgcash/RESTpkg_customer/
6o) Open SQL Developer Web to check the REST Objects ==> REST ==> AutoREST
7o) Check the PKG_Customer API code PKG_Customer ==> View OpenAPI
a) Two of the some endpoints created for the tcity table were /GET_TCITY/1 and /GET_TCITY/2
b) Although they were named as GET_TCITY they were defined as POST verbs and required a parameter in the request body
c) This is the endpoint url : http://localhost:8080/ords/wsgcash/RESTpkg_customer/GET_TCITY/1
d) this is the cURL call
curl -X ‘POST’ \
‘http://localhost:8080/ords/wsgcash/RESTpkg_customer/GET_TCITY/1’ \
-H ‘accept: application/json’ \
-H ‘Content-Type: application/json’ \
-d ‘{
“p_cit_id”: 1
}’
e) it does ont run and showed the error :
404 Error: Not Found
Response body
{
“code”: “NotFound”,
“message”: “Not Found”,
“type”: “tag:oracle.com,2020:error/NotFound”,
“instance”: “tag:oracle.com,2020:ecid/aDbnmbom6-qQqzCqNT_tuQ”
}
f) I also run this requst in Postman but I had the same 404 error
I also opened this request on stackoverflow and provided screenshots of each step.
https://stackoverflow.com/questions/79675561/oracle-apex-24-restful-services-url-endpoints-verbs-does-not-work
Why the endpoints and verbs created by using “SQL Workshop ==> RESTFul Services ==> Create AutoREST Objects” based on package are generating only POS|T verbs and those are not working as expected?
Did I miss some step or am I using the SQL Web Developer in the wrong way?
I appreciate your comments on this.
Thanks.
I answered your Question on StackOverflow.
If you do need to build your own API vs rely on Auto feature, you might find this article helpful.
Hi Jeff,
I am trying “PROJECT” feature in SQLcl. Is there a config option to set liquibase schema?
No, that’s not supported with Projects command, directly.
However, if you inspect the install.sql script, you can see the liquibase commands…and you could add the -liquibase-schema-name parameter to the update changeSet command. And if you do that, it’s on you to do it, correctly, going forward as you would risk breaking your installer, as we won’t know what you’re doing.
Hi Jeff, I was wondering if you knew if there is an (official) effort at Oracle to create a model context protocol (MCP) server to allow Gen-AI enabled tools like VS Code to be context aware of Oracle databases? Thanks!
Yes I know.
I think you will like the answer, very very very soon.
Thanks Jeff. I’ll keep an eye out for the MCP announcement.
I appreciate you for taking the time to answer the question..and thank you for the tip.
Of course, any time!
Hi Jeff,
I started using the extension 25.1.1 recently and connected it to EBS 12.2.14, while I can use sql worksheet and run queries I am not having any success populating the object tree, should this be working or is there a trick to it. I am wondering if the reason is the amount of data that needs to be pulled in for browsing through the objects. Every presentation on line that I saw is using a simple DB, just not with number of items EBS database contains.
Thank you
Peter
We don’t care how ‘big’ your database is.
We’re querying the dictionary for objects, and it tells us what is there.
You can see the queries we’re running in the Output panel, with category switched to SQL Developer Log. In Extension preferences, set the extension logging to ‘TRACE.’
We’re basically querying the contents of the ALL_ views like ALL_OBJECTS, ALL_TABLES, etc.
never mind sorry about that, I figured it out after going into Other Users and then applying filter, it works as would expect. I need little more time discovering the tool.
Thank you