Ask A Question

5,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.

  1. 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.
  2. 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.
  3. 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,932 Comments

  1. Avatar

    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.

  2. Avatar

    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

    • thatjeffsmith

      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.

  3. Avatar

    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

  4. Avatar
    Graham Measures Reply

    Hi Jeff
    We have paid for ADWs and on premise Oracle databases and ORDS. Is the database Actions suite free to use?

    • thatjeffsmith

      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!

  5. Avatar

    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

    • thatjeffsmith

      use the unload command


      set loadformat delimiter |
      set loadformat enclosures off
      unload hr.departments

  6. Avatar
    Rodney Bailey Reply

    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.

  7. Avatar

    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

  8. Avatar

    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

  9. Avatar

    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!

    • thatjeffsmith

      No, and that BI Publisher ‘hack’ isn’t exactly supported/endorsed by Oracle either as far as I know…

  10. Avatar
    Tony Miller Reply

    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

  11. Avatar
    Nitin Kolte Reply

    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

  12. Avatar

    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

    • thatjeffsmith

      Something’s not right. What does this query show?

      select * from ORDS_METADATA.ORDS_URL_MAPPINGS

    • Avatar

      Thanks 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.

  13. Avatar
    Greg Boergermann Reply

    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?

    • thatjeffsmith

      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?

  14. Avatar

    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.

    • thatjeffsmith

      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.

  15. Avatar

    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?

    • thatjeffsmith

      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.

  16. Avatar

    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

  17. Avatar
    Milton Quinteros Reply

    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.

    • thatjeffsmith

      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.

    • Avatar
      Milton Quinteros

      By the way, sqlcl -oci has the same issue.
      Best regards.

    • Avatar
      Milton Quinteros

      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’:

    • Avatar
      Milton Quinteros

      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.

  18. Avatar

    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 [[email protected]] SQL> prompt a few seconds later
    a few seconds later
    2022-04-15 18:11:19 Fri [[email protected]] 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 [[email protected]] SQL> prompt a few seconds later
    a few seconds later
    2022-04-15 18:12:43 Fri [[email protected]] 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!

  19. Avatar

    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

    • thatjeffsmith

      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?

    • Avatar

      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

    • thatjeffsmith

      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.

    • Avatar

      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

  20. Avatar

    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!

    • thatjeffsmith

      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.

    • Avatar

      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?

    • Avatar

      OK! Happy to hear that there was not a checkbox somewhere I had not seen or tried.

  21. Avatar

    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

    ]]>

  22. Avatar

    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

    • Avatar

      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

      ]]>

    • Avatar

      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

      ]]>

  23. Avatar

    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 ?

    • Avatar

      I type sqlplus / as sysdba
      Enter password: oracle (I type oracle)
      ERROR:
      ORA-01017: invalid username/password; logon denied

  24. Avatar

    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

    • thatjeffsmith

      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.

    • Avatar

      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.

Write A Comment