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,802 Comments
Thanks for the quick response.
Regarding: “The docs here shouldn’t be config/install dependent…”
https://blogs.oracle.com/post/pdb-lifecycle-management-with-ords
I tried to step through this example, trying to make the translation the 21.x steps to the 22.1 steps, but I’m probably failing to connect the correct dots. I ran a typical install of all features into the CDB as sys, then config the cdbAdmin/pdbAdmin properties with these:
ords config set db.cdb.adminUser
ords config secret db.cdb.adminUser.password
ords config set db.adminUser test_ords
ords config secret db.adminUser.password
I’m pretty sure this was right:
ords config user add admin “SQL Administrator” “System Administrator” “SQL Administrator”
I created the test_ords user in pdb$seed and ran the ords.enable_schema, made sure ords was bounced after the changes just in case, but it it’s not working. (likely I’m just doing something wrong).
When trying to reach: /ords/_/db-api/stable/database/pdbs/
The request gets back:
{
“code”: “NotFound”,
“message”: “Not Found”,
“type”: “tag:oracle.com,2020:error/NotFound”,
“instance”: “tag:oracle.com,2020:ecid/C3ZWGs5Nv2buTq582BMRYw”
}
And the ords output has:
WARNING :::got unchecked exception from user-defined connection labeling callback
I didn’t see any other details.
When I steppd through the blog with ords 21.x it worked fine.
You’re reading a blog from someone who’s talking about 21.4. For 22.1, the setup steps are similar, but different. You’ll need to follow the 22.1 Installation & Configuration Guide.
Thanks, I mentioned that I was trying to translate the steps from 21.x to 22.1. Also, it’s an oracle blog from just a couple of weeks ago so I was hoping it’d have used the new syntax/method instead since it’s something that’s going to come up when people are searching how to use the API.
Also, the 22.1 documentation was not updated, which is why I was trying to figure it out myself. Eg:
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.1/orddg/enabling-ords-database-api.html#GUID-DA068A37-9D6F-4E3E-B5FD-5FA6562E6B13
You need 22.1.1 apparently a bug in 22.1.0 is causing your problem.
Thanks I’ll give that a go.
Will the documentation be getting updated as well?
Thanks
With every release, of course.
Thanks, only asked because there are a number of places still using the pre 22.1 syntax in the 22.1 documentation, so were missed in that release and wanted to make sure they weren’t missed in future releases beyond whatever the normal documentation updates would be.
https://docs.oracle.com/search/?q=%22java+-jar+ords.war%22&category=database&product=en%2Fdatabase%2Foracle%2Foracle-rest-data-services%2F22.1
(not all are false positives)
Yes and those will be fixed up shortly.
Hi Jeff –
I’m working on prototyping the ability to create/manage/drop sparse/snapshot clones. I was hoping to make rest APIs via ORDS available in order to drive the process, but am running into issues and am not sure if it’s just my lack of understanding, or if things aren’t implemented/supported. Cutting to the chase:
– Is it possible to install ORDS_METADATA in a CDB so that I can ORDS Enable custom PL/SQL owned by a common user that has privs to manage the PDBS? I’ve tried installing ords (both 21.x and 22.1 into the CDB and ORDS_METADTA is only installed in PDBs. Or, is there a more correct way to rest-enable custom PLSQL in a CDB Common User?
In lieu of custom PL/SQL, I can probably deal with the PDB lifecycle API, but:
– The 22.1 PDB lifecycle documentation still uses 21.x install/config steps, not 22.1. I tried to figure out how to follow the blog from https://blogs.oracle.com/post/pdb-lifecycle-management-with-ords with ords 22.1, but didn’t get anywhere, so had to resort to testing it with 21.x.
– This worked, but I can’t seem to find how to create a sparse clone via the API. The only attribute I can find is
“sparseClonePath”:{“type”:”string”,”description”:”A Path specified for sparse clone snapshot copy. (Optional)”}
But I have no idea what the value would be. Normally wouldn’t supply a path, just “…SNAPSHOT COPY”.
Thanks
Unfortunately there’s no way for customers to run things on the CDB via ORDS. The only exception being those PDB lifecycle management APIs you’ve found.
The docs here shouldn’t be config/install dependent…
PDB REST API Docs
{“method”: “CLONE”,
“clonePDBName”: “api_clone”,
“fileNameConversions”: “NONE”,
“sparseClonePath”: “+SPRC1", -- this is the file system path on ASM
“unlimitedStorage”: true,
“reuseTempFile”: true,
“totalSize”: “UNLIMITED”,
“tempSize”: “UNLIMITED”,
“tdeExport”: true,
“tdePassword”: “WElcome11##11”,
“tdeKeystorePath”: “/var/opt/oracle/dbaas_acfs/ORDSDB2/wallet_root/tde/“,
“tdeSecret”: “WElcome11##11"
}
This assuming you’re on Exadata with sparse disks.
Thanks Brian for his help on this one.
Hi Jeff,
I have SQLcl working fine with windows cmd.
But unable to make it work with Cygwin.
Would appreciate if you have any steps that I can follow to use SQLcl with Cygwin?
Thanks in advance
Nik
No but Oracle-Base probably does.
Hi Jeff
We have paid for ADWs and on premise Oracle databases and ORDS. Is the database Actions suite free to use?
ORDS and Database Actions are no-cost features.
If you use it to burn CPU running queries or consume space by inserting lots of data, that will incur costs on the Cloud side for example. But the software/interfaces themselves aren’t charge items.
ORDS can run right on your laptop, no Cloud required at all!
I want to get a text file from a table in Oracle that is pipe-delimited and has NO double-quotes for characters. Below is the code I have, but the SET SQL DELIMITED | does not remove the double quotes whatsoever. Can please help how to resolve this issue?.
Note: Even if I try to use REPLACE “, still the output com es with double quotes.
ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYYMMDD’;
SET SQLFORMAT DELIMITED | ‘ ‘
SET HEADING ON
SET NULL ”
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET QUOTED_IDENTIFIER OFF
SPOOL
SELECT * from TABLE;
SPOOL OFF
use the unload command
set loadformat delimiter |
set loadformat enclosures off
unload hr.departments
Hi Jeff,
I am using the new APEX Instances in the Oracle Cloud. I found out that this does not support connecting to the related Autonomous Database from my local SQL Developer and so I must use SQL Web Developer. I need to run a script that calls many separate scripts in files using the @ command. I have set up a bucket in Oracle Cloud and I have a user and Auth token created, and I stored the credentials in the database using DBMS_CLOUD.create_credential. When I try to run @https:// I get
SP2-0738: Restricted command:
“@https://”
not available
I’ve searched everywhere, including this site, for guidance on how to do this. Any help would be appreciated.
Two things.
Autonomous APEX <> Autonomous Database.
Autonomous APEX uses Autonomous Database, but you’re paying for APEX, that’s why you don’t have direct access to the database.
Why aren’t you using the APEX SQL interface, they let you upload scripts and run them directly in APEX. The embedded @’s will work, too.
Hi Jeff,
Do you know of any free pl/sql static code analysis tools that work with SQL Developer – preferably integrated? If not – how about non-free (I know SonarQube Developer Edition and just found Visual Expert @ http://www.visual-expert.com).
Regards,
Justin
Just found PMD which looks promising. though it doesn’t appear to be integrated it does have a command line client.
https://pmd.github.io/latest/pmd_rules_plsql.html
I hav configured defaults.xml, is there any thing abnormal in it?
Saved on Sat Dec 04 03:54:11 PKT 2021
true
basic
WIN-1Q675V2L7CO
1521
XEPDB1
true
true
wwv_flow_epg_include_modules.authorize
plsql
10
80
10
true
true
Why? Is something not working?
Hi Jeff,
I have a quick question. We have an in house Oracle DB and we also have Oracle HCM cloud as a service with no developer tools. so if we need to query the cloud “database” we use BI publisher to create a SQL. I was made aware of a product call SQLConnect that leverage the BI publisher and API to mimic SQL developer. I was wondering if SQL Developer has some sort of similar way to do this.. here is the url https://sqlconnect.com/oracle-cloud-queries/
Thank you!
No, and that BI Publisher ‘hack’ isn’t exactly supported/endorsed by Oracle either as far as I know…
Do you have a good setup guide for setting up Apex with ORDS and WebLogic to take advantage of an LDAP for user authentication using a http header variable? The gig I’m on is converting from Forms to APEX and doesn’t have Apex configured yet..
Thank you!
Tony Miller
White Rock, NM
Hi Jeff,
Is there a way to import the code temaplates I have added in my sql developer. Also wanted to know the way how to import it.
If this feature is not there can we please get it added.
Looking forward for your response.
THanks,
Nitin Kolte
Looking for clarification if possible. For ORDS ,after the base mapping , i created the schema alias and the ords api is accessible using prefix ords/database/ . I renamed the schema alias to something else .
now, the url is accessible with the old schema alias as well as the new changed alias. I was expecting that the url should be accessible only with the new changed alias as it was renamed.
Trying to understand if there’s any session or expiry time settings makes both accessible . can you please throw some light if possible
Something’s not right. What does this query show?
select * from ORDS_METADATA.ORDS_URL_MAPPINGSThanks for responding. ORDS_METADATA.ORDS_URL_MAPPINGS shows the newly changed value .whenever i change the schema alias ,the table reflects the modified value .it’s only the url which shows the result with the old and new url.
I tried viewing your “Video: Oracle Database Tools State of the Union 2022” – and found that it is very blurry – is there a way you can make it clearer?
Sorry, it was a Zoom meeting broadcast recording…not sure what went wrong there. Is there a particular feature/topic you want to learn more about?
Hi Jeff, by looking at the latest version Sql Developer, the user snippet search function is still missing. I have asked this quite a few times over the year. I saw people have similar have request before.
When you have hundreds of snippets, it is very hard to go through each one on the list.
We have to fork the code from jdeveloper to make that happen.
I’ve put it on the roadmap for 22.2, but no promises.
It is about REST API protection with ORDS. Assume we have defined one module/template (/ords/hr/demo/employees) and two handlers (GET and POST). What is the best practice to allow one group of users only GET and the other group GET and POST handlers. How to protect an individual handler?
There’s no good way to do that. Privileges, what ORDS uses to protect the APIs from unauthorized users, is keyed onto either modules or URI templates. In either case, your user would be able to do both a GET and a POST.
Hi Jeff,
I am missing the right click trogger option to automatically create a sequnce and trigger for tables in SQLDatamodeller.
Can you think what I have done or configured wrong please? I’m using 21c on an ATP instance.
I wonder if I can automate this so any changes add these and journal tables when the DDL is generated, rather than having to do these steps each time?
Many thanks,
Phil
21c? Start using Defining 12c IDENTITY Columns in Oracle SQL Developer Data Modeler columns.
Hello Jeff, can you help with the following:
After coming back to standard time in America/Santiago the function tz_offset( sessiontimezone ) still returns -03:00 in sqlcl/SQL Developer (MacOS, Windows). It returns -4:00 in sqlplus.
Best regards.
SQLcl and SQLDev pull up regional info off the host machine via JDBC driver which affects NLS session parameters, UNLIKE how the native OCI things like SQLPlus operate.
By the way, sqlcl -oci has the same issue.
Best regards.
In the session trace file there is nothing related with ‘alter session’, nothing explaining why sessiontimezone shows -03:00.
There are 5 rows in V$TIMEZONE_NAMES for ‘America/Santiago’:
America/Santiago LMT
America/Santiago SMT
America/Santiago -05
America/Santiago -04
America/Santiago -03
I think sqlcl is always taking the last one.
Best regards.
Please open a SR with MOS.
Thank you Jeff.
Have a nice day.
Hi Jeff
question about date (and time) in sqlprompt.
# With sqlcl
$ LANG= ORACLE_HOME= PATH=/usr/bin:/usr/java/bin /u01/app/oracle/product/sqlcl/bin/sql system
SQLcl: Release 22.1 Production on Fri Apr 15 18:10:54 2022
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Password? (**********?) *********
Last Successful login time: Fri Apr 15 2022 18:10:59 +02:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.14.0.0.0
SQL> alter session set nls_date_format = ‘YYYY-MM-DD HH24:MI:SS Dy’;
Session altered.
SQL> set sqlprompt “_date ‘[‘_user’@’_connect_identifier’]’ SQL> ”
2022-04-15 18:11:19 Fri [SYSTEM@ORCL19] SQL> prompt a few seconds later
a few seconds later
2022-04-15 18:11:19 Fri [SYSTEM@ORCL19] SQL>
Notice: date (and time) in prompt has not changed!
# With sqlplus
$ sqlplus system
SQL*Plus: Release 19.0.0.0.0 – Production on Fri Apr 15 18:12:15 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Enter password:
Last Successful login time: Fri Apr 15 2022 18:10:59 +02:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.14.0.0.0
SQL> alter session set nls_date_format = ‘YYYY-MM-DD HH24:MI:SS Dy’;
Session altered.
SQL> set sqlprompt “_date ‘[‘_user’@’_connect_identifier’]’ SQL> ”
2022-04-15 18:12:28 Fri [SYSTEM@ORCL19] SQL> prompt a few seconds later
a few seconds later
2022-04-15 18:12:43 Fri [SYSTEM@ORCL19] SQL>
Notice: this time, date in prompt has changed!
Why date (and time) in prompt does not change in sqlcl?
Am i missing something with sqlcl?
Thanks!
A litlle up please.
Thanks!
Hi Jeff,
– We need to persist a timestamp in format —> “YYYY/MM/DD HH24:MI:SS”.
– ORDS (default) accepts format —> “DD-MON-RR HH.MI.SSXFF AM”
Our handler looks like following:
ORDS.define_handler(
p_module_name => ‘m’,
p_pattern => ‘t/’,
p_method => ‘POST’,
p_source_type => ORDS.source_type_plsql,
p_source => ‘BEGIN pkg.p(p_timestamp => :timestamp); END;’,
p_items_per_page => 0);
Inside procedure “p”
DBMS_SESSION.SET_NLS(‘nls_timestamp_format’, ‘”YYYY/MM/DD HH24:MI:SS”‘);
has been tried unsuccessfully.
How can the nls format be changed for the POST handler and db session?
What is the recommended approach?
Thanks in advance
Reinhard
If the data is correct, that’s paramount. How is the format coming into play? What does your package do? Is the data being received incorrectly?
1. How is the format coming into play?
-> no explicit format mask
-> timestamp comes from a JSON:
a) works:
{
timestamp”: “2022-03-31T11:53:02.358Z”
}
b) does not work:
{
timestamp”: “2022/03/31 11:53:02”
}
2. What does your package do?
-> executes “merge into” statement:
MERGE INTO t
USING (SELECT (p_timestamp) timestamp FROM dual) src
That’s it.
3. Is the data being received incorrectly?
-> Yes. Using DBMS_SESSION.SET_NLS works randomly even ORDS db sessions have been killed by ORDS restart (controlled by Tomcat)
Strange. We are using ORDS 20.4.3
Thanks
Reinhard
ORDS formats dates and timestamps to match our Oracle and json standards
“2022-03-25T16:53:21Z”
That’s what you’ll always send to ORDS. If you want to send “2022/03/31 11:53:02” instead, or use something other than Zulu (Z), send it in as a string and cast it yourself in your back-end API code.
Putting DBMS_SESSION.SET_NLS to the handler seems to work in principle. But under rare cicurmstances it flips to default format. Strange. Maybe the jdbc driver. (setup ist completely default)
ORDS.define_handler(
p_module_name => ‘m’,
p_pattern => ‘t/’,
p_method => ‘POST’,
p_source_type => ORDS.source_type_plsql,
p_source => ‘BEGIN
DBMS_SESSION.SET_NLS(‘nls_timestamp_format’, ‘”YYYY/MM/DD HH24:MI:SS”‘);
pkg.p(p_timestamp => :timestamp);
END;’,
p_items_per_page => 0);
Thanks
Hi Jeff,
We’ve been using SQL Dev for a couple of years now, and we do not know much about SVN.
We now need to manage our 2000 database sources with SVN. I’ve been able to connect to our SVN repository, which was a great start. Now I’m trying to determine the best way to import, or add, 2000 source files, and then, most importantly, how to efficiently do our daily work with SVN within SQL Dev, if possible.
In all articles I’ve seen here you were talking about using SVN with Data Modeler files. But database source is not the same since we have the extra step db -> file -> db.
Could you point me toward some doc that would list step by step what to do to best use the SVN integration?
Thanks!
Well, step one would be unloading all of your db objects to files, and then adding those to your SVN projects.
Heads-up, SVN is basically dead and has been replaced with Git.
Heads-up number 2, SQL Developer isn’t the best SVN or Git client. I would recommend you supplement your work with a dedicated browser/explorer extension for SVN or even a free-standing dedicated SVN gui to hep you out.
Thanks for the quick answer Jeff!
I get what you said about SVN. We’ve been told that SVN would be a temporary solution, but since our other teams code is already in there, we have to go there for now.
About unloading all db sources to files. I’ve tried the Export tool. It’s close to do the job, but I can’t get it to not put the 3 lines comment containing “– DDL for Function ABC…”.
Is there a way to only have the “create or replace [object_type] …” just as when I edit the source?
I’d write a shell script to walk those files and nuke the first 3 lines.
OK! Happy to hear that there was not a checkbox somewhere I had not seen or tried.
We’re co-workers ya know, you can always hit me up on Slack 🙂
Hi!
Trying to get Liquibase updatesql in sqlcl, get this message:
SQL> lb updatesql release_table.xml
Processing has failed for your request.
Messaqe can not be null
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.4.1.0 build: 21.4.1.17.1458
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
release_table.xml
<![CDATA[
xxxxx
RELEASE
REVISION_NBR
VARCHAR2
100
RELEASE_START_DATE
DATE
RELEASE_END_DATE
DATE
10
40
1
1048576
1048576
1
2147483645
0
1
1
DEFAULT
DEFAULT
DEFAULT
CMXCFGDATA
Y
N
]]>
Hi!
Trying to get Liquibase updatesql in sqlcl, get this message:
SQL> lb updatesql release_table.xml
Processing has failed for your request.
Messaqe can not be null
so i need to see your XML to help, also i need versions of everything
Hi!
Trying to get Liquibase updatesql in sqlcl, get this message:
SQL> lb updatesql release_table.xml
Processing has failed for your request.
Messaqe can not be null
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.4.1.0 build: 21.4.1.17.1458
release_table.xml
<![CDATA[
CMXCONFIG
RELEASE
REVISION_NBR
VARCHAR2
100
RELEASE_START_DATE
DATE
RELEASE_END_DATE
DATE
10
40
1
1048576
1048576
1
2147483645
0
1
1
DEFAULT
DEFAULT
DEFAULT
CMXCFGDATA
Y
N
]]>
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.4.1.0 build: 21.4.1.17.1458
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
release_table.xml
<![CDATA[
xxxxxx
RELEASE
REVISION_NBR
VARCHAR2
100
RELEASE_START_DATE
DATE
RELEASE_END_DATE
DATE
10
40
1
1048576
1048576
1
2147483645
0
1
1
DEFAULT
DEFAULT
DEFAULT
CMXCFGDATA
Y
N
]]>
ABOUT The Easy Way to get started with Oracle Database: our VirtualBox Appliance
Updated February 2021
THE password “oracle” does not work!
Can you help-me ?
Which account are you trying to use the password in combination with?
I type sqlplus / as sysdba
Enter password: oracle (I type oracle)
ERROR:
ORA-01017: invalid username/password; logon denied
Couple of things.
SQLPLus, no, SQLcl, yes.
Just connect as system.
It worked! Thank you very much!!!
Can you point me in the direction to get some clarification about the following error.xxx ORDS configuration properties:
According to documentation here: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/21.4/aelig/about-REST-configuration-files.html#GUID-37AA1468-DCB3-4D8B-868C-1910A0C04D68+
error.keepErrorMessages = Specifies whether to retain the error messages.
error.maxEntries = Specifies the total number of error messages to retain.
What does “retain” mean in this context and where are those messages are retained?
Thanks
Those are specific to the PL/SQL Gateway feature and not for REST APIs delivered by ORDS. They’re in fact obsoleted and about to be dropped from the program entirely.
It worked! Thank you very much!!!
Hi Jeff, I read your post about SQL Developer opening 2 connections.
https://www.thatjeffsmith.com/archive/2013/10/unshared-worksheets-in-oracle-sql-developer-4/
My company is setting up RADIUS 2FA authentication for our Oracle database. Each time we connect to the database, it prompts the RADIUS authentication twice. Seems to be related to the 2 connections you shared. Is there anyway we can stop SQL Developer from opening the 2nd connection?
Nope..can you tell me more about the two factor auth, did you get it working for SQLDev?
Yes, it worked for SQL Developer (somewhat).
At the Oracle database side, the user account is created with external authentication instead of password authentication, and we specify RADIUS as the external authentication mode in the sqlnet.ora file. When we log in to the database via any Oracle IDE, instead of entering the user account’s password, we enter the RSA pin. The database will send an authentication message to the RADIUS server, which then prompts the user on the mobile RSA SecurID app for approval, hence the 2-factor.
What we noticed was that after entering the RSA pin on SQL developer, we received 2 prompts on the mobile RSA SecurID app for approval (around 7 secs apart for each prompt), before we successfully connect to the database. After reading your article, we thought this might be the reason; SQL Developer establishes 2 connections with the database, hence the database sent the message to the RADIUS server twice and resulted in 2 prompts for approval. Similar attempts on other IDEs like Toad and DBeaver also resulted in 2 prompts, which might also be due to their completion insights feature. When we tried connecting via other methods, e.g. Python script, Excel ODBC connection, there is only 1 prompt.
Yup, we use 2 sessions for your IDE ‘connection.’