Ask A Question

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.

  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.



  1. roopesh shanku Reply

    SQL Developer on a Dell laptop using a tnsnames.ora
    Issue: trying to connect as SYS to a 12.1 db get ORA-01017 invalid u/pwd.

    Can connect as SYSTEM and as a db user i created using the same cr. conn pop up box !

    Have tried all i could find on the web but of no use so far !

    How to resolve this ?

    • In connection properties, set role from default to AS SYSDBA

      That’s required for a SYS connection.

      But connecting as SYS should be done as infrequently as required.

  2. John Thomas Reply


    Season’s greetings. Here’s a thought for you to look at in 2023?

    PL/Scope will do things like detecting unused variables for PL/SQL code.

    We have large, sometimes very large, SQL queries used by non-PL/SQL application code. Sometimes these queries contain redundant columns which may have been used for debugging, or simply cut and pasted from another variant of the query.

    If there’s a parsing tool that will detect such columns, please put me right… otherwise another one for your list? I imagine something from PL/Scope might be extended for this. Have thought of temporarily embedding the SQLs in dummy PL/SQL and running PL/Scope against it, but I don’t think PL/Scope does redundant variable detection inside SQL statements.

    When I have time, I’ll wade through these SQLs removing unused columns. But they often involve joins across sub-query refactored queries, meaning manually walking up a tree to work out if some columns are actually used or not.

    Happy New Year when it comes.

  3. Hi Jeff, happy holidays.

    I was wondering if there were any updates/estimate on sqlcl being delivered as a native image with graalvm to reduce the startup time?


    • Bummer, thanks for the update.

      I understand why it takes longer / isn’t supposed to be a straight sqlplus replacement, but the way I/we do things really doesn’t lend itself to just starting sqlcl and churning away for the day, we’re in/out all the time so the startup time is a real barrier for our general use.

    • It’s like a second vs ms

      So for automation, calling thousands of times, I totally get it.

      It is meant to be a straight up replacement for sqlplus. If you look at what you get for that extra second, I think it more than pays that tax.

      How long is it taking for you? What Java are you using?

    • (sorry, replied to the wrong comment).

      time for a sqlplus to connect to a db and run a file that just calls ‘quit’: real 0m0.123s
      same for sql (21.4 is most recent we have in house handy, jdk17): real 0m3.922s / 0m5.000s (consistently around 4-5s).

      One use is like you say automation where that kind of overhead would, for most of the script we run, introduce a huge time issue for our large deployments.

      But even for daily use, I guess I’m the kind of person who types and works fast and is quickly jumping from one thing to the next. A ~4second overhead on every time I start sqlplus would drive me up the wall to the point where I just deal with the limited feature set compared to sqlcl, which I only use when I really need something specific, or for some standalone deployments without clients/etc.

      I’d love to try to migrate all of our use over to sqlcl because even basics like hitting the up arrow for command history would be great (rlwrap only kind of works).

    • Start it up, leave it up

      4 seconds is manageable. I’m guessing M1 Mac with arm Java would be even faster.

      Getting sqlplus to open fast is great, but then you’re stuck in sqlplus.

  4. Hi Jeff, How are you?
    We are currently using ORDS 19.4 and Oracle APEX 19.2 and we want to make an upgrade to latest versions of ORDS(22.3) and APEX (22.2). We were able to install both tools without issue but getting them work the way we have with the previous versions is getting tricky.

    We have installed APEX in a PDB and we have some ORDS Restful Services installed over the same PDB. In the ORDS 19.4 version we secured our Restful Webservices using key in the defaults.xml configuration file. Now that we are testing ORDS 22.3 we tried to use the same key in the pool.xml file but then ORDS is blocking access to APEX (403-Forbidden) as well.

    Since we want to secure the access to the RESTful services without blocking the access to the APEX apps, do you have any suggestion in how can we achieve this?

    I appreciate your time.


    • It’s a pool specific feature, so you your defaults shouldn’t not have any reference to it and your pool config should have an entry for

      If that procedure is VALID, returns a boolean TRUE or FALSE to allow/deny access, then it should work. If it’s not, we have a problem.

      However, prehooks aren’t applied to APEX sessions, only ‘each Oracle REST Data Services based REST Service’ call.

    • Thanks for your prompt response Jeff,

      Yes the procedure is valid and its working as expected for ORDS 19x

      Its strange, this prehook try to authenticate APEX session as well.

      Error: The user is not authorized to request the resource

    • time for a sqlplus to connect to a db and run a file that just calls ‘quit’: real 0m0.123s
      same for sql (21.4 is most recent we have in house handy, jdk17): real 0m3.922s / 0m5.000s (consistently around 4-5s).

      One use is like you say automation where that kind of overhead would, for most of the script we run, introduce a huge time issue for our large deployments.

      But even for daily use, I guess I’m the kind of person who types and works fast and is quickly jumping from one thing to the next. A ~4second overhead on every time I start sqlplus would drive me up the wall to the point where I just deal with the limited feature set compared to sqlcl, which I only use when I really need something specific, or for some standalone deployments without clients/etc.

      I’d love to try to migrate all of our use over to sqlcl because even basics like hitting the up arrow for command history would be great (rlwrap only kind of works).

  5. Hi Jeff,
    I went through your Blog for setting up a REST API, but must be missing something because when I try to call it I get a 404 Not found Error. I am trying to do this in OCI, PL/SQL, and APEX. My guess is I am missing something with getting security/authorization correct in OCI.
    Goal is to publish an OCI REST API to accept JSON payload secured by a token. Below is script used to create the API.
    Not sure how to setup any authentication based requirements beyond this script and publish
    When calling the API in Postman it generates a 404 error. Implicit parameter :body is assumed to contain the JSON payload
    p_enabled => TRUE,
    p_schema => ‘TEST_USER’,
    p_url_mapping_type => ‘BASE_PATH’,
    p_url_mapping_pattern => ‘orders’,
    p_auto_rest_auth => FALSE
    SELECT parsing_schema,
    FROM user_ords_schemas;
    p_module_name => ‘neworders’,
    p_base_path => ‘parts/’,
    p_items_per_page => 0);

    p_module_name => ‘neworders’,
    p_pattern => ‘orders/’);

    p_module_name => ‘neworders’,
    p_pattern => ‘orders/’,
    p_method => ‘PUT’,
    p_source_type => ORDS.source_type_plsql,
    p_source => ‘BEGIN
    Pkg_Inventory.Accept_Parts_Orders( p_Token => :Token,
    p_Body => :body);
    p_items_per_page => 0);

    Attempted API call:

    • 404 – your URL isn’t right.

      You rest enabled TEST_USER schema and aliased as ‘orders’

      So, for every rest enabled object and rest module in TEST_USER, you’ll address as

      For example
      PUT /ords/orders/neworders/parts

    • Thanks. I made the changes and still getting 404. What is required for authorization, or would that be a different error? Could OCI be blocking the API?

    • Joe Kerr

      Progress… Now getting 555 User Defined Resource error in Postman and using APEX SQL Workshop to manage definition of REST API. (ALOT better :)) Placed JSON validated test in raw Body area of Postman
      PUT Source code:
      Pkg_Inbound.Accept_Orders( p_Body => :body);
      Code does not appear to execute or log an application error.

    • Now it’s time to debug your handler plsql block.

      :body is a BLOB, does your plsql program accept that?

      Also we recommend SQL Developer Web’s REST IDE over Apex’s – it has many more features.

    • THANK YOU ! That is what I needed to figure it out. If I want “:body” to be CLOB then I would have to accept the input as a parameter, correct?

  6. Jeff –

    Trying out the lb ges on 22.3.1, and it is failing due to bugs 34731627 & 34696971 ([Method writeChangeLogs]: Unexpected internal error near index 1).

    Per the notes, it was not present in 22.3.0 (which is no longer available for download). I see that I have two options –
    (1) Wait for 22.4.x
    (2) Guess which of the prior sqlcl has the “best” lb functionality (as they all seem to have a number of issues with lb over the past year or so).

    Any guidance for that to do until the 22.4 release?

    • Hi,
      I had the same problem, so I switched from SQLcl 22.3 back to sqlcl., and the problem “[Method writeChangeLogs]: Unexpected internal error near index 1” disappeared.

  7. Hi Jeff,
    I am trying to load MySQL data into a Oracle Database. The Select extract from MySQL looks something like the below…
    1|1003|2008-05-12|2012-12-31|TEMP_EXMPT|\N|2015-08-29 14:40:50|MSDMIGR|2015-08-29 14:40:50|MSDMIGR|2022-12-07 00:32:29
    2|1064|2011-09-08|2011-11-30|TEMP_EXMPT|\N|2015-08-29 14:40:50|MSDMIGR|2015-08-29 14:40:50|MSDMIGR|2022-12-07 00:32:29
    The \N in the ‘I’ delimited file is a NULL in the MySQL DB. During the SQLDeveloper Import, a warning sign is displayed for Column No. 6 and the import is failing. The source column in MySQL and the target column in Oracle are both DATE type nullable. It appears that the Oracle DATE column is not accepting the ‘\N’ data.
    Any thoughts on how to overcome this ?

    • Extract.

      Are you saying you have an offline migration with the MySQL data offloaded to flat files?

    • The easiest thing to do would be use a text editor and search and remove them so that Oracle will see them as NULLs.

      My question, how was the CSV file generated?

  8. Jean-Claude Hasoon Reply

    Hi Jeff,

    Regarding creating sub-folders in SQL-Developer, I know we can and I have done them for connections. But I was wondering if we can extend something like this to group db objects like tables and views too.
    I have a situation where we only have a DB schema being shared among many data analyst. We give them RO access to views and they access these via the ‘Other Users – schema – views’ of their connections. But because there are so many of them (some having multiple versions which they all still want to be able to access for comparisons etc). So, it become too long list of views mixed all over in the same schema which of course cause a lot of grief when going to determine which ones is the one version I really want to use. Obviously, there are many ways one would think this can be solved – versioning, separate schemas etc but we do not have these luxuries for now nor training the guys into new stuffs. Just want an easy simple way to do that like one said to me – put them all in different folders by name so they can easily get the one they need quicker.

    so is there a way I could get them to organise by sub-foldres the views or objects they have been granted access in SQL Developer? May be this exist and I have not been watching – sorry. I try looking. Let me know if this exist or if you have an idea for me. Thank you.

  9. Hello Jeff,

    We miss many Administrative features when APEX is Runtime Mode. For example report subscription to internal system reports like “Application Errors” or “Application Changes, detailed”.

    I was hoping to achieve this via API call but could not find one. Can we do via PL/SQL? If so, can you give some pointers?

    Thanks in advance

  10. Hello,
    We have a lot of “SQL*Net break/reset to client” when ORDS reads the ORDS_METADATA.HANDLERS table.
    The database goes in WAIT mode for each request and takes a long time to execute the request.
    A request takes 300ms for one user, but takes 30s with 100 users.

    SQL*Net break/reset to client (%)
    The server is sending a break or reset message to the client. The session running on the server is waiting for a reply from the client.

    In this case, ORDS is the client. What’s happen ?
    We have the same behavior with 21.4 and 22.3, with complex queries (28 parameters) and simple SELECT without parameters.

    Have you an idea ?

  11. Hi Jeff,

    My network / security admin asked me today why he sees tens (67 today) of database listener connections to various database servers around our enterprise from me. The timing of the connections coincide with starting SQL Developer. And I surely have tens of connections configured. But I have not actually opened any connections inside SQL Developer / authenticated to any database. Does SQL Developer perform it’s own connection test or tnsping type activity upon starting?


    • Yes. If you mouse over the connection in the list, you can see what the ‘ping’ time is, what your latency might be.

      You can disable this by adding this line to your sqldeveloper or product.conf file
      AddVMOption -Dsqldev.tnsping=false

      More discussion here.

  12. Andrew Hayes Reply

    Hi Jeff,

    In previous versions of ORDS it was possible to deploy multiple ords.war files at the same time simply by renaming them and configuring each one. These could then be deployed under Tomcat side by side.

    I can see that the way everyone seems to deploy ORDS 22.3 under Tomcat is to specify the configuration file in the Java options, and for 1 ORDS war file that seems to work OK. But I can’t see how you might deploy a second without a separate Tomcat environment?

    Reading through the documentation for ORDS 22.3 under “Deploying ORDS on Apache Tomcat” the notes say:

    “The recommended approach is to generate a Web Application archive file with the configuration directory location specified for that war file.”

    This sounds like what I want to do – but I’m struggling to achieve this as there are no examples. I’ve tried…

    ords –config /path/to/config war /path/to/war/ords.war

    and several other flavours of this but all I seem to be able to create is a 1KB war file – with very little in it.
    Any tips here?

  13. Jeff – is there a way to disable the bouncy social media icons in the lower right ? Your articles are valuable but those bouncing-as-you-scroll social icons are just just incredibly distracting. I think the intention was good but the practical effect is that I for one avoid your articles for this very bouncy reason because I mouse-scroll a lot.

    • You’re the 3rd to complain, so I’ll nuke it now.

      Thanks for taking the time to share this.

  14. Hi Jeff,
    A question about silent installation with TNS in ORDS 22.3
    When specifying
    installation completes, but omitting those and specifying instead
    i’m getting these errors:

    ERROR: Cannot have different connection types specified on the command-line.

    ERROR: Invalid option(s) specified on the command-line.

    Any idea?
    Pre-22.x we would need to specify in the parameterfile:
    But there does not seem to be an equivalent in 22.3; at least it is not in the documentation and also none of the examples use tns.

  15. Hi Jeff,
    I’m in the process of assessment sqlcl – liquibase features for our use-case, and it looks promising
    But I have problems with queue tables, as I can’t get it to work with:

    — logged as appropriate user
    lb generate-object -obt AQ_QUEUE_TABLE -obn
    gives me: “Unable to retrieve object information from the database. Please check object name and object type”
    Generation of XML for queue itself runs fine.
    The version of sqlcl is latest at this time,
    Oracle SQLDeveloper Command-Line (SQLcl) version: build:

    I tried dbms_metadata directly and it works as expected:
    dbms_metadata.get_ddl(OBJECT_TYPE => ‘AQ_QUEUE_TABLE’, NAME => , SCHEMA => );


    • You forgot to say ‘table’ after -obt

      SQL> lb generate-object -object-type table -object-name QT
      --Starting Liquibase at 09:21:37 (version 4.17.0 #0 built at 2022-11-02 21:48+0000)

      Changelog created and written out to file qt_table1.xml

      Operation completed successfully.

    • Well, if I create a XML for type TABLE, then it will be a regular table and not the AQ table.
      The AQ table should be created with call to

      For example, with
      lb generate-object -obt AQ_QUEUE -obn queue_name
      I get a xml which will be executed as DBMS_AQADM.CREATE_QUEUE, and that’s the right call.

      But with just
      lb generate-object -obt TABLE -obn qt_name
      I’ll get XML for regular table, which will be executed as create table and not DBMS_AQADM.CREATE_QUEUE_TABLE

      For that reason I tried to use -obt AQ_QUEUE_TABLE, as documentation suggests


    • In our latest internal build, it seems to be working

      SQL> lb generate-object -object-type AQ_QUEUE_TABLE -object-name QT
      --Starting Liquibase at 12:11:18 (version 4.17.0 #0 built at 2022-11-02 21:48+0000)

      Changelog created and written out to file qt_aq_queue_table.xml

      Operation completed successfully.

      Now connect to a fresh env and use updatesql with the changeLog and see what the DB will do with it –
      SQL> lb update-sql -changelog-file qt_aq_queue_table.xml
      --Starting Liquibase at 12:13:51 (version 4.17.0 #0 built at 2022-11-02 21:48+0000)

      -- Loaded 1 change(s)
      -- *********************************************************************
      -- Update Database Script
      -- *********************************************************************
      -- Change Log: qt_aq_queue_table.xml
      -- Ran at: 11/15/22, 12:13 PM
      -- Against: HR@jdbc:oracle:thin:@dbtools-dev:2213/DB213P
      -- Liquibase version: 4.17.0
      -- *********************************************************************
      -- Changeset qt_aq_queue_table.xml::841c3e8e912f2f2a5e25d744b6730b3e148549fc::(PDBDBA)-Generated
      Queue_table => '"QT"',
      Queue_payload_type => '"EMBEDDED_VARRAY"',
      storage_clause => ' PCTFREE 10 PCTUSED 40 INITRANS 1
      Sort_list => 'ENQ_TIME',
      Multiple_consumers => FALSE,
      Compatible => '10.0.0',
      Secure => FALSE);

      So this should be working when we push our next release.

  16. Hi Jeff!
    Is there a posibility in SQL Developer to configure different paths depending on the context (see examples below) or one global standard path where to open files (on the local filesystem or on an network-share) when using the File => Open-menue path?

    In our currently used SQL Developer version ( Build 173.2018, Java(TM)-Plattform, Windows 10 Enterprise Version 21H2 Build 19044.2130) we determined three (?!) different behaviours when using the File => Open-menue path:
    active but new / blank SQL worksheet tab with database connection => C:\Users\[username]\AppData\Local\Temp
    active SQL worksheet tab with an opened file (with or without database connection) => the files path
    active database object tab such as table => C:\Users\[username]\AppData\Roaming\SQL Developer\system22.\o.sqldeveloper\projects


  17. Hi Jeff, I use the drag and drop functionality a lot to open pkb and pks files in sql developer.
    However. when I open a pkb file by dragging it on a sql window connected to a staging database, compile it, save and close sql developer, then when I open sqldeveloper again, connect to a different database and then drag the same pkb file from file explorer into the sql window connected to that new connection the pkd is opened but often with the connection still pointing to the staging database (the one used when I last opened it). This especially happens when dragging multiple files into a sql window.
    Is there a setting or preference to always only use the connection of the sqlwindow I’m dragging the files into ?

    • We work at the same company, you can just hit me up on Slack 🙂

      But yes, disable ‘Link Stored Procedures to Files’ preference under Code Editor sections.

  18. Arun Rajagopalan Reply

    select *
    from (select *
    from x
    where y between 1990 and 2000
    match_recognize (
    order by y
    all rows per match
    pattern (i ln u{\?\})
    i as winner like ‘I%’,
    ln as length(song) >= 18,
    u as host = ‘ISS’

    even with escape character it does not work on sqlcl.

    Error at Command Line : 9 Column : 20
    Error report –
    SQL Error: ORA-00911: invalid character
    00911. 00000 – “invalid character”
    *Cause: The identifier name started with an ASCII character other than a
    letter or a number. After the first character of the identifier
    name, ASCII characters are allowed including “$”, “#” and “_”.
    Identifiers enclosed in double quotation marks may contain any
    character other than a double quotation. Alternate quotation
    marks (q’#…#’) cannot use spaces, tabs, or carriage returns as
    delimiters. For all other contexts, consult the SQL Language
    Reference Manual.
    *Action: Check the Oracle identifier naming convention. If you are
    attempting to provide a password in the IDENTIFIED BY clause of
    a CREATE USER or ALTER USER statement, then it is recommended to
    always enclose the password in double quotation marks because
    characters other than the double quotation are then allowed.
    SQL> show version
    Oracle SQLDeveloper Command-Line (SQLcl) version: build:

    • Arun Rajagopalan

      Yes, it is working in SQL Developer but not SQLCl

      pattern (i ln u{\?\})

    • Weird, they both share a JDBC driver and script execution engine, and parser. What version of both?

    • Arun Rajagopalan

      I have tried it on SQLcl: Release 22.3 and SQL Developer Version

  19. Pavel Pospíšil Reply

    Hi Jeff,

    Thanks for your site..
    I’m using version
    I use DBMS_DEBUG (a great option) – it works quite as expected, but during the step it is not able to open the source from TYPE BODY – it just opens a dialog asking where to take the source code – It is necessary to find a text source saved somewhere. Then it works.. (for the given debug session – I have to do it again the next time I debug). Packages/procedures/functions works as expected..
    Where am I making mistake ?

    (I tried debugging in a competing product as well, and there is no such problem – normally, debugging works through the TYPE BODY..)


  20. Hi Jeff!
    I have an standalone ORDS install version 18.4 pointing to an database.
    Now I want to move the ORDS_PUBLIC_USER and metadata to a new database19c.
    Do you have a process for this? What I must do on the frontend(standalone side) and the backend database side?



    • Do a fresh install on your 19c database.

      Then, recreate your rest APIs, privs, roles, and OAuth2 clients.

      Hopefully you have these as SQL scripts in source control.

      You should be able to extract these from your 18.x environment as SQL scripts to be replayed on the box.

      Kudos on decommissioning your 11g instance and moving to 19c!

  21. Hi Jeff,
    Does the info or info+ command from SQLcl have a line number option similar to “set desc linenum ON” in SQL*Plus?
    If not, may I suggest that feature for a new SQLcl release? 🙂

  22. Hi Jeff,
    Can you use the SQL*Plus “set” commands like “set rowlimit” or “set feedback on sql_id” in SQLcl?
    If not, will they eventually be ported to a new release in the near future?

    • No plans for
      set rowlimit — you’re the first to ask

      SQL_ID, already there!

      SQL> set feedback on SQL_ID
      SQL> select 5 from dual;


      1 row selected.

      SQL_ID: 3b3s1w98d2g81

    • Thanks Jeff!
      Confirmed: “set feedback on SQL_ID” works.

      If I may make a suggestion, please tell Oracle to add “set rowlimit” to SQLcl (or let me know how to do it). 🙂

  23. Hi Jeff, love your content. With the new versions completion insight does not take into account the alias provided to limit the results to that table. This was very handy in the past to find column names in that table for the select statement and is no longer usefull because of this. Is this design or a bug?

    Thank you,


  24. Attie Taljaard Reply

    Hi Jeff,

    I read the article : “Linking Excel to AUTO REST Enabled Tables…with the Oracle Visual Builder Plugin”.

    Do you know if there is a sample/tutorial (VBA Script/VBA Function) on how to call a REST API in ORDS for a lookup into Oracle ORDS from a VBA script?

    A sample of calling a basic lookup from Excel VBA (VBA Script/VBA Function) into and ORDS Rest Service to just do a basic lookup of a value?


  25. Hi Jeff,
    I have a liquibase/sqlcl issue I was hoping you could give me some pointers on how to solve –
    I have 2 ATP DBs (Always Free v19c). I am just trying to create a POC where I can gen schema from one and update the other.
    SQLcl v22.3
    JDK 11
    JRE 18.9
    Liquibase v4.17

    I can connect to my DEV DB and run ‘lb ges -split’ which works and the relevant files are output as expected with changelogs and a controller.xml file.

    I then connect to my TEST_LOW DB and run ‘lb up -chf controller.xml’ (Pointing to the controller generated for DEV db)
    and I get ‘No such host is known (test_low)’

    Not sure if I’m missing any steps – any idea what could be wrong?

    Pretty new to lb so could be something I’m missing. any help would be really appreciated/


Write A Comment