4.5+ million 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!
6,170 Comments
Dear Jeff,
is it really true that usage of this great tool SQL Developer now costs additional license fees for JDK though we already license an Oracle 12c database?!
In your post from 2019 you wrote https://www.thatjeffsmith.com/archive/2019/04/oracle-sql-developer-and-the-oracle-jdk-in-2019/ “Your existing licenses gives you access to the Oracle JDK at no additional cost.”
is that still true or not?!
Best wishes
Kai
You do not need to separately licensing Oracle Java to use Oracle SQL Developer. What I wrote in 2019 still applies in 2021, and will going forward.
Data Modeler v20.4
Reverse Engineer from an existing set of schema
About 40 partitioned tables with lots of partitions
About 400 Tablespaces
First try where I selected everything I wanted — failed on partition information error
Second try pick about 50 tables — not partitioned and the tablespaces — hung on trying to save
Third try — only picked 6 users, 400 tablespaces and 10 roles. => been running for over 2 hours. Will I be able to finish in a week?
Sounds like you need more help than I can afford to give at the moment – suggest posting over to the dedicated Data Modeler forums.
Hi Jeff,
We experience an issue that Oracle Developer removes all comments or commented lines in SQL.
All the comments made on a Worksheet are being removed after doing any changes in Query Builder.
The same I had both in new and old versions of the program.
Is there any way to sop that behavior and keep the comments?
Not that I know of…I’m pretty sure there is a bug logged there.
Hi Jeff,
I downloaded 20.4.1 rpm to my machine yesterday and now i cannot export any query results.
I switched back to 20.4.
Greetings
Since you’ve switched, I’m not going to be able to help you figure out what was going wrong. I can tell you there’s zero change in the code for sql developer between those two versions though.
Dear Jeff,
We’ve problem when switch from Toad to SQL Developer. In Toad, we could change font of data grid. We need it because our DB using USASCII7 and have specific font to show Vietnamese.
I can’t find anyway to do this in SQL Developer. Could you help us?
Thanks in advance.
TuanNV
Grid font is controlled by Code Editor Font preference.
hello there;
you mentioned about running multiple queris on oracle sql developer.
im just wondering that this method can be used in insert ordelete queries?
the link
https://www.thatjeffsmith.com/archive/2012/06/running-multiple-queries-in-oracle-sql-developer/
You can submit a script which has multiple inserts, updates, and deletes. Those will be processed sequentially.
You can have two connections, each running a script concurrently.
Hi Jeff,
I have an ORDS enabled schema which has one published module containing a POST handler. The resource is protected via a privilege protecting the pattern “/umr/*”. Both the published API and OAUTH2 are working fine, however /metadata-catalog/ does not show my module. Is this expected?
URL:
http://<>:7001/ords/brokasure/metadata-catalog/
Response:
{
“items”: [ ],
“hasMore”: false,
“limit”: 25,
“offset”: 0,
“count”: 0,
“links”: [
{
“rel”: “self”,
“href”: “http://<>:7001/ords/brokasure/metadata-catalog/”
},
{
“rel”: “first”,
“href”: “http://<>:7001/ords/brokasure/metadata-catalog/”
}
]
}
Thanks,
Sophie.
Begin
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => ‘***’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘***’,
p_auto_rest_auth => FALSE);
commit;
End;
/
what user has to execute statement
The user you’re wanting to enable.
So login as HR to REST enable HR.
Because we are not doing this manually, We are trying to launch Oracle SQL Developer session from CyberArk UI [which automatically takes the Remote Desktop Session of a Server where SQL Developer is installed, after Remote Desktop session an AutoIT script automatically launches SQL Developer and passes the username/password/ports etc from CyberArk to SQL Developer ]
The problem is Remote Desktop Session is initiated by a Random user[System Generated] so I can’t predict the name of this user, So I wanted to set something like Global Preferences which can be set for all the users.
Other way could be, If I know which files holds preferences information I can copy this file to all the users after RDP session is launched and before launching the SQL Developer.
Hi Jeff,
what if one has to deal with a 60k lines package body? The Sqldeveloper becomes really slow and sometimes I am forced to restart. Are there some parameters to enable or disable in order to make the task more feasible?
Thank you
Version of sqldev?
Latest, v 20.4
Maybe the slowness is the combined effect of outlining in realtime a big package with a slow remote database.
Anyway I noticed that all the four i7 cores are spinning while I try to edit that file. In order to load it without warnings I had to put “set hidden param parseThreshold = 500000;” in my login.sql file
yes, the parser is attacking that code, building a memory map of everything – when you set the parseThreshold higher, you’re telling it it’s ok to burn even more cpu/memory to support the insight/formatting/highlighting/etc – so, if you trim that back down to the default (30000), i’m guess you’ll notice it won’t slow down anymore
Lowering that number has the drawback that some code remains not parsed (many methods are missing from the code outline) and that I get an annoying message that advices to raise it :-(.
Anyway this makes the program more useable.
Thanks
It’s a pick your poison kind of bargain…
Hi!
Can I set the same preferences for all the users those login to a Server with their individual IDs* and launch Oracle SQL developer from this server? These preferences may should include jdbc drivers for other Databases like DB2, PostgreSQL.
IDs?
individual IDs , Like username… user1, user2, user3… these users takes RDP session to a server where Oracle SQL Developer is installed. User1 wants to access Oracle DB and User2 wants to access DB2. I want to set prefreance for all the users so that if User2 tries to connect DB2, he/she should not have to set JDBC drivers.
Yeah I don’t see why you couldn’t setup their environment on the server for them.
Because we are not doing this manually, We are trying to launch Oracle SQL Developer session from CyberArk UI [which automatically takes the Remote Desktop Session of a Server where SQL Developer is installed, after Remote Desktop session an AutoIT script automatically launches SQL Developer and passes the username/password/ports etc from CyberArk to SQL Developer ]
The problem is Remote Desktop Session is initiated by a Random user[System Generated] so I can’t predict the name of this user, So I wanted to set something like Global Preferences which can be set for all the users.
Other way could be, If I know which files holds preferences information I can copy this file to all the users after RDP session is launched and before launching the SQL Developer.
All of the preferences are stored in the Windows AppData, Roaming Profiles folder.
hi, it’s possible in Oracle Data Modeler have a model to customize the layout of a ddl script before it was generate?
and it’s possible to modify the order o visualization of the list of table in the browser pannel for the logical relational and physical model?
thanks in advance
Hi Jeff –
I was looking at ORDS and was wondering how, if at all, it could support something like OLS where the requests could be coming in on behalf of users with different labels. Can you somehow incorporate a call to sa_session.set_access_profile / sa_session.set_label / etc based on the user/profile of the person making the request?
Sorry, what is OLS, Label Security?
Everything runs through the rest enable schema user where the service is defined, so whatever you have setup for that user applies.
Hi Jeff,
We have a “zscaler “between client machine and database server .We are having a peculiar issue connecting to Oracle 19c database from Sqldeveloper version 20 . The connection gets reset .
Connection from old sqldeveloper version 17 works fine without any issue.
After implementing the work around suggested in this thread connection from sqldev v20 works fine.
https://community.zscaler.com/t/oracle-jdbc-drivers-19-3-0-0-and-up-connection-issue-workaround/8907
If this thread is true then many customers using java apps / SQLDev and connecting via zscaler to 19c database will get affected. Can you confirm the changes done in 19c JDBC drivers which may be causing this?
Thanks,
Would it connect but then at some point going forward, get reset? Or would it never connect? Or both? The more you can tell me the better, and also assuming this was for every single one of your deployments? The workaround is unfortunate as it will make it so you cannot cancel queries in a sql worksheet. So, I’d like to get this addressed/fixed!
Hola Jeff,
Estoy leyendo datos de un campo tipo CLOB que contiene un archivo texto que tiene 2876 líneas.
Ejemplo: contenido de archivo.
Al leer con DBMS_LOB.SUBSTR(archivo_clob,4000,1) , leer más de una línea, para mi caso quisiera poder leer línea por línea de modo a poder guardarlo posteriormente.
Me podrias dar una mano por favor.
Saludos.
¡Hola! Parece que esta pregunta sería mejor respondida por Chris o Connor en AskTom.
Yes Oracle Label Security. Seeing as each user could have different label privileges plus each user may want to be able to run a query at some label below their maximum label I guess it’s not really feasible to use with OLS if there’s no way to set the profile or label other than the user’s default.
Thanks
I’ve not ever really worked with label security…but, is that something that could handled in the code handler’s PL/SQL block? If..then…alter session…
I asked a question 4 weeks ago:
Using SQL Developer you can take DDL:s for different tables and take out the data for the same tables. But it’s tedious to repeat the tables every time. There exists a CLI for SQL Developer that I want to use. However, I haven’t found documentation that describes the CLI for SQL Developer (sometimes called SQLcl) in detail. Where is the detailed documentation which for instance describes what happens when you invoke “set ddl segment_attributes on”?
In your answer you referred to the online help which is very rudimentory and said they are transforms of dbms_metadata and a link to dbms_metadata dokumentation. I thought that usage was good enough but it’s not.
In particular the BLOB columns are dumped when I use the CLI directly, but not when I run SQL Developer interactively.
So I want the same result in the CLI as when I run SQL Developer interactively.
I use version 18.1.0.095 of SQL Developer (build 095.1630).
When I run the CLI directly I use the following commands:
set ddl pretty on
set ddl constraints off
set ddl ref_constraints off
set ddl constraints_as_alter off
set ddl partitioning off
set ddl segment_attributes off
set ddl storage off
set sqlformat insert
ddl TABLE
select * from TABLE;
# Maybe this line above is why BLOBs are included?
# The schema name is included in the output but in SQL Developer “Show Schema” is unchecked.
In SQL Developer I do the following:
Tools -> Database Export
Export DDL, Pretty Print, Add BYTE keyword, Terminator, Add Force to Views, Storage set
Export Data set, Format insert
I export only tables
The online command help for sqlcl is pretty extensive.
I can help you with sqldev, but you need to upgrade first, 18.1 is 3 years old.
What exactly are you trying to get, what is the end goal?
Dear Jeff,
I’m trying to implement your media upload/download example in my environment but I always get the following errors: An unexpected error with the following message occurred: InternalServerException [statusCode=500, reasons=[]]
From my understanding, could it come from my environment/the version of ORDS used as I have the feeling that the request does not reach the database. I use the version Oracle REST Data Services 19.2.0.r1991647 deployed on APACHE TOMCAT 9.0.21 with a Oracle Database 18c Express Edition Release 18.0.0.0.0 – Production Version 18.4.0.0.0.
No way to get it run successfully.
Can it come incompatibility between all those “components” ? Do you advice me to change something ?
Thanks a lot in advance for your reply
Look at the tomcat logs (catalina) for ORDS – those 500’s should have an Oracle ERROR message attached.
Turn on debug/print errors to screen while you’re in dev mode. You can also simply things by running ORDS standalone vs Tomcat.
Your ORDS is also quite old, although should work just fine for your scenario – just be aware tons of performance and security improvements since July or 2019 to now (version 20.4).
Version: 20.4.0.379 Build: 379.2205
When you use View/Files you get a nice presentation of your underlying file system.
Navigating to a zipped file it expands out and presents the embedded sql scripts that are sent to me regularly.
As it allows me to “Open” the SQL in the zip I then ran the update statement and it completed successfully.
I understand why I cannot edit the opened file in the connection I have. But I wanted to commit the action it said it did. So is my only way to commit an update/insert is to select F11? I selected the commit button without response from SQL Developer. And when I closed the connection was not asked if I wanted to save my work.
Or having run the update script from a zipped file there is no commit and it’s a test.
Looked for documentation around this feature but was not successful.
Kind regards
Steve
Stephen Williams
[email protected]
I have a dual monitor setup and I have noticed that pop-up windows will always appear on the screen where SQLDeveloper were started regardless of where the main application window resides.
Also if main application window is moved within the same desktop any pop-ups will still appear centered based on where main application window first launched, not based on where main application window currently resides
I have the following setup:
OS: Windows 10 64 bit 1909
SQLDeveloper: 20.4.0
JDK: 11.0.10
Multiple displays setting: [Extend these displays]
But this has been the behavior, at least for Windows, for as long as I can remember
Reproduce:
———————————————————————-
– Connect two monitors
– Start SQLDeveloper
– Move SQLDeveloper to the other desktop
– execute: select * from dual where dummy = upper(‘&letter’);
Expected result: Pop-up appears on same desktop, centered, in front of main window
Actual result: Pop-up appears on the desktop where SQLDeveloper first appeared when launched and centered on where the window was first positioned.
———————————————————————-
Sorry, for bad posting. I have now filed a bug report at Oracle Support
I am trying to connect SQL Developer 20.4 to a Postgres database that has SSL enabled. I found some help about making an SSQL connection to Postgres using SQL Developer here – https://www.spotonoracle.com/?p=345. But I can’t seem to get the connection string quite right to make the connection. My SSL connection requires 3 certificates and the example uses only 1 certificate. I have looked at the postgres JDBC documentation and tried multiple combinations in my connect string, but no luck. Is there a place where I can request an enhancement for SQL Developer? Thanks.
Are you looking to do a postgres to oracle migration?
Yes, there are a couple of “small” postgres databases that I would like to use SQL Developer to try and migrate to Oracle.
I’m using SQL Developer 19.2.1.247. My colleague uses SQL Detective. Recently we’ve often both been working in the same packages, we check that the other person is out before going in, but I’ve been opening the packages and his changes aren’t there. This even happens when I’ve disconnected and reconnected again. If I happen to know that I’m not seeing the latest version I close the package, right click on packages and refresh and then it appears but I’ve overwritten his code a couple of times and I don’t understand why. Can you shed any light/offer any suggestions?
Start using source control. And for extra credit, have your own personal schema/database where you do your work, and then merge your changes to a common environment/db. Also, you should be working off of files, not live db code. Finally, 19.2 is more than a year old, latest and greatest is version 20.4 – although that won’t have any impact on what you’re facing now.
Hi, thank you for your reply, i found the issue , i was testing the service on Tom cat server , i restarted the server and it worked, i guess when you alter database objects you must restart the Tomcat server in which ORDS is running?
Alter what database objects?
I mean by data base objects the user defined type ex:
CREATE OR REPLACE TYPE CR_DB_TOT_MAIN_OBJ AS OBJECT
(
)
/
CREATE OR REPLACE TYPE CR_DB_TOT_MAIN_TYPE AS TABLE OF CR_DB_TOT_MAIN_OBJ
/
We don’t cache anything, so a database change should be seen immediately by ORDS.
How do I create a root folder in the connections? When I try to create a folder I am not able to “unselect” a folder which results in all folders I create are sub-folders. I solved my problem by created the folder as a sub-folder and then drag it to the root.
You have connections, you have folder. Then you can add connections to folders. You can add a folder at the top level, or inside another folder.
Adding a folder at root level only works if you have not selected one of the existing folders. Once a folder, any folder, has been selected there is no way no un-select a folder except to close the Select Folder window and open it again. You can select any folder, but not “no folder” by, for instance, clicking where no folders are.
Reproduce:
——————————————————————–
– Right click on connection -> [Add to folder]
– Click any folder
– Try to create a folder at root level
——————————————————————–
Hi Jeff,
Thanks for the quick reply!
Yeah, i think it does, i’ll go away and have a look at the htp package just now and see how i get on.
I really appreciate the help, thanks again.
Michael
Hi Jeff,
I’m hoping you might be able to help me out with an ORDS/SQL Developer Web question.
I have a (always free!) Autonomous Transaction Database and have been using the SQL Web Developer & APEX tools very happily over there.
I’d like to be able to call an external API and insert the JSON returned from the API call into a JSON column on a table in one of the schemas in my database, but I’m pretty stuck as to how best to do this. It seems like ORDS may be a good fit here, but I have very little experience of using it or the tooling available on the Autonomous Database side, which is a bit different from what I’m used to (i.e. SQL Developer).
To try to add a bit of detail, I was wondering if there’s a way to do something like the following :
Call an external API e.g.
/2.0/?method=album.getinfo&api_key=YOUR_API_KEY&artist=Cher&album=Believe&format=json
returning JSON output and have this JSON inserted into a column on a table inside the autonomous database using ORDS?
I have managed to access the relevant API a number of times via curl commands, output the JSON returned into individual files on my local machine and could probably manually add this JSON to the relevant table on my database, but it’d be great if there was a way to programatically to this via ORDS?
Thanks, for the help!
Michael
Ords doesn’t help with calling web resources…it delivers web sources from an Oracle database
You would use the htp package to consume a resource from the web in the database.
Does that help?
Hi Jeff,
I am facing an issue with how to cast a returned data base object to a ref cursor type in order to be returned as a result set in ORDS , I wrote a PLSQL package that returns a nested data base object (type):
CREATE OR REPLACE PACKAGE PA04J3V06
AS
PROCEDURE A04J3V06 (INP_BRA_CODE IN NUMBER,
INP_TELL_ID IN NUMBER,
INP_LAN_IND IN NUMBER,
INP_CUS_NUM IN NUMBER,
MAIN_OBJ OUT CR_DB_TOT_MAIN_TYPE,
RETURN_STATUS IN OUT NUMBER);
END PA04J3V06;
the data base objects looks like :
CREATE OR REPLACE TYPE CR_DB_TOT_MAIN_OBJ AS OBJECT
(
ID VARCHAR2(50),
V_DESC VARCHAR2 (100),
TOT_1 NUMBER,
TOT_2 NUMBER,
TOT_3 NUMBER,
CHILDREN CR_DB_TOT_MAIN_2_TYPE
)
and
CREATE OR REPLACE TYPE CR_DB_TOT_MAIN_2_OBJ AS OBJECT
(
ID VARCHAR2(50),
V_DESC VARCHAR2 (100),
TOT_1 NUMBER,
TOT_2 NUMBER,
TOT_3 NUMBER,
CHILDREN CUS_LED_TOAL_BAL_TYPE
)
and the last one in the hierarchy
CREATE OR REPLACE TYPE CUS_LED_TOAL_BAL_OBJ AS OBJECT
(
ID VARCHAR2(50),
V_DESC VARCHAR2 (100),
TOT_1 NUMBER,
TOT_2 NUMBER,
TOT_3 NUMBER
)
and i also wrote the following in the sql developer:
DECLARE
return_status NUMBER;
v_main_obj cr_db_tot_main_type;
FUNS SYS_REFCURSOR;
BEGIN
v_main_obj := cr_db_tot_main_type ();
pa04j3v06.a04j3v06 (inp_bra_code => :inp_bra_code,
inp_tell_id => :inp_tell_id,
inp_lan_ind => :inp_lan_ind,
inp_cus_num => :inp_cus_num,
main_obj => v_main_obj,
return_status => :return_status);
OPEN :FUNS FOR SELECT * FROM TABLE(v_main_obj);
END;
where FUNS is a out parameter of result set.
This is working just fine , here is JSON being returned
{
“FUNS”: [
{
“id”: “CREDIT”,
“v_desc”: “TOTAL CREDIT”,
“tot_1”: 1015629,
“tot_2”: 1015629,
“tot_3”: 87530863,
“children”: [
{
“id”: “ASSET”,
“v_desc”: “ASSETS”,
“tot_1”: -9722.844,
“tot_2”: -9722.844,
“tot_3”: 0,
“children”: [
{
“id”: “AS1”,
“v_desc”: ” SPECIAL CURRENT”,
“tot_1”: 0,
“tot_2”: 0,
“tot_3”: 0
},
{
“id”: “AS2”,
“v_desc”: ” EQUITY NOT RECEIVED”,
“tot_1”: -10001,
“tot_2”: -10001,
“tot_3”: 0
},
{
“id”: “AS3”,
“v_desc”: ” SUSPENSE ACCOUNT / SECURITIES”,
“tot_1”: 278.156,
“tot_2”: 278.156,
“tot_3”: 0
}
]
},
{
“id”: “CONTRAASSETS”,
“v_desc”: “CONTRA ASSETS”,
“tot_1”: -1000,
“tot_2”: -1000,
“tot_3”: 0,
“children”: [
{
“id”: “CA1”,
“v_desc”: ” INCOMING B/C’S / IMPORT”,
“tot_1”: -1000,
“tot_2”: -1000,
“tot_3”: 0
}
]
}
]
},
{
“id”: “DEBIT”,
“v_desc”: “TOTAL DEBIT”,
“tot_1”: -11001,
“tot_2”: -11001,
“tot_3”: 0,
“children”: [
{
“id”: “LIABILITIES”,
“v_desc”: “LIABILITIES”,
“tot_1”: 1015351.393,
“tot_2”: 1015351.393,
“tot_3”: 87530863.401,
“children”: [
{
“id”: “LB1”,
“v_desc”: ” CURRENT A/C’s”,
“tot_1”: 10351.393,
“tot_2”: 10351.393,
“tot_3”: 87530863.401
},
{
“id”: “LB2”,
“v_desc”: ” CURRENT & DEMAND A/C’s”,
“tot_1”: 0,
“tot_2”: 0,
“tot_3”: 0
},
{
“id”: “LB3”,
“v_desc”: ” SAVING DEPOSITS / CUSTOMERS”,
“tot_1”: 1005000,
“tot_2”: 1005000,
“tot_3”: 0
}
]
},
{
“id”: “CONTRA_LIABILITIES”,
“v_desc”: “CONTRA LIBIALITIES”,
“tot_1”: 0,
“tot_2”: 0,
“tot_3”: 0,
“children”: []
}
]
}
]
}
The problem is when i change the data type of the columns in the data base objects (all three of then) to varchar2:
CREATE OR REPLACE TYPE CUS_LED_TOAL_BAL_OBJ AS OBJECT
(
ID VARCHAR2(50),
V_DESC VARCHAR2 (100),
TOT_1 VARCHAR2 (100),
TOT_2 VARCHAR2 (100),
TOT_3 VARCHAR2 (100)
)
/
i get this JOSN (the children are gone)
{
“FUNS”: [
{
“id”: “CREDIT”,
“v_desc”: “TOTAL CREDIT”,
“tot_1”: “1015629”,
“tot_2”: “1015629”,
“tot_3”: “87530863”
},
{
“id”: “DEBIT”,
“v_desc”: “TOTAL DEBIT”,
“tot_1”: “-11001”,
“tot_2”: “-11001”,
“tot_3”: “0”
}
]
}
I apologies for the long post , looking for your response , thanks alot.
Note i am using 11g database.
Version of ORDS? And I’m assuming you’re using the AUTO feature, not a custom restful web service module/template/handler for your plsql?