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. Hi Jeff

    Bug report maybe. Interface lockup. How to trigger: Open a file containing a package (maybe whatever file), then rename it using the O.S. (I am using SqlDeveloper on Linux, so I did “mv “), then try to save the file from SqlDeveloper. A warning dialog appears with the message “File pathname has changed, reload?” and two buttons “Yes” and “No” but none of them is clickable. The dialog cannot be closed, so I have to bring down the application and restart.

  2. Any chance for “Tree Based Object Browser” pane in SQL Developer to have: “Login Schema”, “Favorite Schema”, and “Other Schema” top level nodes?

    • No, and that’s mostly because SQL Developer is largely in maintenance mode as we undergo launching the SQL Developer ‘of the future.’

    • I guess it is called “Connections” with the object tree under each connection.

      I would also be nice to have a “connection transaction status” pane, that could be refreshed manually to quickly see if the session has uncommitted transactions.

    • on the Transaction status, we have that for SQLcl, we could/should totally add that to the VS Code extension.

    • For the upcoming SQL Developer VSCode version ( and plugin )

  3. Hi,
    is SQL Developer vscode plugin project still alive? I cannot find any update about this, but if I remember well, q2 2023 was the planned release term for it?

  4. Tracey Voss Reply

    Hi Jeff,

    I’m using ORDS 22.1 Database API services to allow developers to export schema app data on demand. The API works( testing through Postman) but it generates a large number of 500 MB dump files instead of the usual one 3GB file (through expdp).
    Is there a way of changing this behaviour? ie filesize?

    I’ve been trawling MOS and the interwebs on this but no joy.


    • I’m guessing our API implements the data pump EXPORT job creation with a FILESIZE parameter set to 500M, most likely because we’re also going to be giving you links to download the files, and this helps keep it manageable. Is there a problem with having multiple dump files? If you’re not happy with how we’ve implemented data pump, you could roll your own REST API exactly the way you prefer.

      Seems like a reasonable ask to update our API to accept this as a parameter on the POST request.

  5. Jacques Tourigny Reply

    How can I change the encoding of the xml result when I’m using lb generate-schema -split command?
    The XML encoding is UTF-8 and I need ISO-8859-1.
    Here is the BAT portion. (I also tried to create a file in the working directory just before)
    echo liquibase.outputFileEncoding: iso-8859-1
    ) >
    echo connect %connect_name%
    echo set ddl storage off
    echo set ddl partitioning off
    echo set ddl segment_attributes off
    echo set ddl tablespace off
    echo set ddl emit_schema off
    echo lb generate-schema -split
    ) | sql /nolog


    • Jacques Tourigny

      When i try to install the apex application with database using this script
      :: ——————————————————————–
      :: Install APEX application and apply database schema object changes
      :: ——————————————————————–
      echo connect %connect_name%
      echo @setup.sql
      echo @install.sql
      echo set ddl storage off
      echo set ddl partitioning off
      echo set ddl segment_attributes off
      echo set ddl tablespace off
      echo set ddl emit_schema off
      echo show ddl
      echo lb update -changelog-file database/controller.xml
      ) | sql /nolog

      I receive the following error:
      2023-06-26 20:55:09.565 SEVERE liquibase.parser Invalid byte 2 of 3-byte UTF-8 sequence.
      liquibase.exception.SetupException: Error parsing line 29 column 9 of procedure/enregistrer_rapports_procedure.xml: Invalid byte 2 of 3-byte UTF-8 sequence.

      At the line 29 column 9 in that xml file I have the following :
      — R�cup�rer la liste des fichiers dans le ZIP
      where ? are french characters. é

      I’m assuming that if the xml file was created with encoding iso8859-1 the install process would work.
      Any Idea how to solve that?

  6. Thanks Jeff, for the writeup. We’ve been experimenting with the MongoDB API feature, that Oracle indicates to set by entering “ords config set mongo.enabled true” and to do so by running ORDS as a standalone. I’ve gotten it to work, by being able to use a client tool typically for MongoDB like Compass, and “create collections” and “add documents” through that interface, which result in JSON Data Stores being created on the Oracle database’s ORDS-enabled schema that I had specified. However, ideally, I was wanting to use this ORDS process to *also* be able to support APEX and RESTful calls. It seems to be an either/or situation, at least with the standalone scenario. I notice that when I don’t have the “mongo.enabled” parameter set to “true” that the standard port of 8080 is being listened on when I do an “lsof -i -P -n”, and the ORDS and APEX URLs then work. Meaning, I can do tests of the ORDS-enabled schema and the REST services with a URL of http://:8080/ords//metadata-catalog, and I can reach the APEX login page with http://:8080/ords. Accordingly, the “lsof” command now does show port 8080 being listened on. Once I set “mongo.enabled” back to “true” and restart the standalone ORDS process, those two URLs are not found, and the “lsof” command shows port 27017 (for the simulated MongoDB connection) being listened on, but not also the 8080 port, which then I’m assuming is the reason the browser URL tests don’t work. Is there some configuration I’m missing that would allow both the MongoDB API and the standard REST and APEX calls to work together in the same ORDS standalone configuration? Hopefully, that makes sense.

    • Chris Boyd

      I tried clicking on the link you included below, and after being prompted and logging in with my MOS credentials, it brought me to a failed APEX login page.

      Either way, over the weekend, I noticed that when I had the “mongo.enabled” set to “true” that only port 27017 was being listened on, and when it was false *then* port 8080 showed up in the “lsof” list. Out of sheer frustration and just to throw something against the wall to see if it would stick, I explicitly configured the standalone HTTP port (even though its default value *is* 8080) with the following:
      ords –config ${ORDS_CONFIG} config set standalone.http.port 8080

      Having run that along with configuring the “mongo.enabled” allowed both ports to be listened on:
      [root@kub02 ~]# lsof -i -P -n | grep LISTEN

      java 109734 oracle 14u IPv6 1671931 0t0 TCP *:8080 (LISTEN)

      java 109734 oracle 15u IPv6 1671932 0t0 TCP *:27017 (LISTEN)

      Hence, the APEX login screen and ORDS URL test (http://:8080/ords//metadata-catalog) worked along with access to the Oracle JSON data store backend using a mongodb connection URL through a client tool such as Compass.

      Given that finding, do you know why I’d have to explicitly set that parameter/property to its default value, in order to get it to play nice with the MongoDB API having been enabled?

  7. Hi Jeff, for git based team work I would like to share the code formatting information (formatterOptions.xml, right?) between teammembers. Preferably outside of %APPDATA%\Roaming\SQL Developer\systemxx.x.x.\o.sqldeveloper\formatterOptions.xml in our git repo. So, can I change the location of the formatterOptions.xml somewhere? Or do I need to use symlinks to do this?

    Thank you, Florian.

    • Sorry Jeff, question was inaccurate. I am aware of the import/export function. My goal is to keep the custom format definition in git and automatically distribute it to developers whenever a change is made. My idea: each developer configures in SqlDev once the location of formatterOptions.xml in the git repo. Changes to formatterOptions.xml are then automatically rolled out to all developers when a git pull is made. Is this possible or do I have to point the filesystem location into the git using symlinks.

  8. Hi Jeff smith,

    Can you please guide on how to enable/Configure windows authentication for APEX deployed applications in an company Intranet environment?

    Current Environment:
    Platform : Windows
    Oracle : 19c
    Apex : 23..1.1
    Ords : 23.1 deployed on Tomcat9

    Thank you.

  9. Hi,

    I have some scripts that have a double hyphen as a comment, that work fine in sqlplus but not in sqlcl. How to overcome this issue without changing scripts?


    • Hi

      SQL> select ‘1’
      2* from dual;


      SQL> select ‘1’
      2 — COMMENT
      3 from dual ;
      SQL> /


    • Hi

      SQL> select ‘1’
      2 — COMMENT double hyphen
      3 from dual ;
      SQL> select ‘1’
      2* from dual;


      SQL> /



    • Please just send me the actual script, no prompts, no line numbers, i can’t tell if double hyphen is literal or a substitute.

    • select ‘1’
      — COMMENT double hyphen
      from dual ;

      select ‘1’
      from dual;

    • It works, but the comment is being added to the column header for the SELECT.

      Workaround: it’s working as expected in SQL Developer. I’ll log a bug for you.

    • Hi,

      Thank you, Jeff,
      If you have a query with multiple comments … it’s a mess.
      Please could put here the link to the bug.


  10. Hey jeff,
    How can I record the screen time of user in oracle apex application and store it to the database.

  11. Posting this question again since someone replied to my question and raised his own question. My question in the thread seems to have missed.

    HI Jeff,

    I am using SQL Developer 23.1.
    I am trying to extract the ddl for an object (e.g. a table) using the quick ddl option. I am getting all the dependent objects e.g. table ddl, index, constraint, grants etc. but unable to get the ddl of the synonyms pointing to the table in the extracted ddl. I dont see an option to including synonyms when exporting table ddl. Is there a way we can extract the synonyms in the same extracted ddl file for the table?


    • I spent 5 minutes on this yesterday and came up blank. I think the answer is yes, there’s a call to dbms_metadata.get_dependent_ddl that can be called for synonyms on a table, I THINK, that would do what you want, but I wasn’t able to get far with it.

      Short answer: file an ER with My Oracle Support, and we’ll take a look.

    • Thank You Jeff for the reply.
      I have raised SR 3-33245222811 with the SQL Developer product type. I hope raising a SR is the correct way to file an ER.

      Thank You.

  12. Hi Jeff,

    Just installed SQL D 23.1; explain plan button works fine; but when using ‘autotrace traceonly’, the trace works, but it should also provide a plan. Instead, get this message in the output:

    SQL_ID: null, child number: 0 cannot be found

    Can you help? Thanks.

    • We’re computing the SQLID for the query, and then getting the plan for that SQL. That’s not working for your SQL, that’s where the ‘null’ is coming from.

      Here’s the code we’re using to print the plan
      SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'05dnu8cqtmgc4',format=>'ALLSTATS LAST'))

      Here’s how it is working for me –

      set autotrace trace
      select * from hr.locations

      Autotrace TraceOnly
      Exhibits the performance statistics with silent query output

      23 rows selected.

      SQL_ID 05dnu8cqtmgc4, child number 0
      select * from hr.locations

      Plan hash value: 2989070059

      | Id | Operation | Name | E-Rows |
      | 0 | SELECT STATEMENT | | |

      - Warning: basic plan statistics not available. These are only collected when:
      * hint 'gather_plan_statistics' is used for the statement or
      * parameter 'statistics_level' is set to 'ALL', at session or system level

      2 CCursor + sql area evicted
      3 DB time

      To continue helping you I’ll need more information from your side. Like, does this happen for any/all queries? Is your account ‘privileged,’ and what version of the database are you connected to?

  13. While exporting rest API I am getting this error ORA-20850: Parameter p_module_id must not be null.

    • Weird. Well, there are like 6 ways you could be exporting your REST APIs.

      What versions of which tools are you using, and how exactly are you exporting your REST API?

  14. HI Jeff,

    I am using SQL Developer 23.1.
    I am trying to extract the ddl for an object (e.g. a table) using the quick ddl option. I am getting all the dependent objects e.g. table ddl, index, constraint, grants etc. but unable to get the ddl of the synonyms pointing to the table in the extracted ddl. I dont see an option to including synonyms when exporting table ddl. Is there a way we can extract the synonyms in the same extracted ddl file for the table?


    • Hi Jeff

      When editing PL/SQL, at the bottom of the screen there’s a super helpful one liner of how deeply nested you are in the code, and clicking on any element takes you to the start of that section (say a FOR loop, or an IF statement)

      Is it possible to click on the same element, but in a way that takes you to the END of that section (say END LOOP or END IF statement) ???

      I know I can collapse/expand, but a CTRL + click (or similar) on the elements in the one liner at the bottom would be very useful if it doesn’t already exist

      Thanks for your time
      Simon R

    • Hi Jeff,

      Any comments on my previous question please?


  15. Can you offer any guidance on resolving why I cant get apex export to work from sqlcl. I am looking to abandon using APEXExport.class. I’m seeing the following errors:

    workspace and instance exports fail…. but individual application does not

    SQL> version
    Oracle SQLDeveloper Command-Line (SQLcl) version: build:

    SQL> apex export -workspaceid 2150425194735257 -dir /backup/apex
    APEX: Unable to process request. Verify specified options:
    ORA-00942: table or view does not exist

    SQL> apex export -instance -dir /backup/apex
    APEX: Unable to process request. Verify specified options:
    ORA-00942: table or view does not exist

    SQL> SELECT * FROM apex_release;

    _____________ ____________________ ________________
    22.1.0 2022.04.12 APPLIED

    SQL> connect app_owner
    Password? (**********?) *******
    SQL> apex export -applicationid 500 -dir /backup/apex
    Exporting Application 500

  16. I’m trying to launch SQLcl through Azure Devops using a self-hosted agent. SQLcl launches fine using CMD when I login directly to the server, but when it comes through the pipeline agent it bombs with this error. I haven’t been able to find any reference to this specific error. Any thoughts? Thanks!

    Exception in thread “main” java.lang.ExceptionInInitializerError
    at oracle.dbtools.plusplus.connections.db.NamedConnections.loadConnections(
    at oracle.dbtools.plusplus.connections.db.NamedConnections.(
    at oracle.dbtools.plusplus.connections.db.NamedConnections$Holder.(
    at oracle.dbtools.plusplus.connections.db.NamedConnections.instance(
    at oracle.dbtools.plusplus.connections.db.SqlclConnectionSupport$ResolverHolder.(
    at oracle.dbtools.plusplus.connections.db.SqlclConnectionSupport.connectionResolver(
    at oracle.dbtools.db.ConnectionSupport.install(
    at oracle.dbtools.db.ConnectionSupport.setConnectionSupportImplementation(
    at oracle.dbtools.raptor.scriptrunner.cmdline.SQLCliHelper.getCliScriptRunnerContext(
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processOptions(
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.(
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(
    Caused by: java.lang.RuntimeException: TODO: Error creating connections dir
    … 13 more
    Caused by: TODO: Error creating connections dir
    … 13 more
    Caused by: java.nio.file.attribute.UserPrincipalNotFoundException
    at java.base/sun.nio.fs.WindowsUserPrincipals.lookup(
    at java.base/sun.nio.fs.WindowsFileSystem$LookupService$1.lookupPrincipalByName(
    … 15 more
    ##[error]Cmd.exe exited with code ‘1’.

  17. Hi Jeff

    Appreciate the help you provide to the community.

    How to use the SQLCL connection strings saved using “NET =:/;” for opening database connections. I dont seem to be able to find documentation on how and where the connection strings, saved using the NET command, could be used.

    please share the link for the documents if any.


    • Vijay Manthena

      Sorry Jeff.

      I meant connection strings saved using NET name=dbhost:port/service_name; The comments aren’t showing if I enclose the name in “” .

    • once you have saved a NET name, you can simply use it in a connection request

      SQL> net cdb=localhost:1521/free;
      SQL> net list
      SQL> connect sys@cdb as sysdba
      Password? (**********?) ******
      SQL> show connection
      type: ORACLE
      user: sys
      url: cdb
      role: sysdba
      SYS@jdbc:oracle:thin:@localhost:1521/free AS SYSDBA
      Oracle Database 23c Free, Release - Developer-Release

  18. — SELECT SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
    — SDO_ORDINATE_ARRAY(a, b)) AS cust_location, cust_name
    — FROM (
    — SELECT * FROM (
    — SELECT 77.376855 AS a, 28.617492 AS b, ‘plot2’ AS cust_name FROM dual
    — SELECT 77.376372 AS a, 28.617529 AS b, ‘plot2’ AS cust_name FROM dual
    — SELECT 77.376388 AS a, 28.617715 AS b, ‘plot2’ AS cust_name FROM dual
    — SELECT 77.376870 AS a, 28.617691 AS b, ‘plot2’ AS cust_name FROM dual
    — SELECT 77.376830 AS a, 28.617253 AS b, ‘plot1’ AS cust_name FROM dual
    — SELECT 77.376369 AS a, 28.617285 AS b, ‘plot1’ AS cust_name FROM dual
    — SELECT 77.376384 AS a, 28.617463 AS b, ‘plot1’ AS cust_name FROM dual
    — SELECT 77.376845 AS a, 28.617431 AS b, ‘plot1’ AS cust_name FROM dual
    — )
    — )
    I have this code to visualize two polygons on oracle apex maps, but i don’t get any geometry in the front end

  19. Is there a current How To document detailing the steps to configure SQL Developer to connect to a SQL Server database using Window Authentication? I found some older posts that referenced using a third party driver, (jTDS), and the following file placements within SQL Developer:

    – Copy the jtds-1.3.1.jar into: {$sqldevhome}\jlib Directory.
    – Copy the the ntlmauth.dll dll in the \x64\SSO subdir into: {$sqldevhome}\jdk\jre\bin\ntlmauth.dll

    Any help would be greatly appreciated.

  20. Hi Jeff,

    I have a procedure with table type as an out parameter (a table of object).
    When I try to execute it using SQL developer, it returns an error If I try to show the table out parameter in the output.
    Normal scalar parameters are successfully shown (if the table type is not shown).
    Is there some way to display such parameter type in the output upon execution?

    Here is an example:

    –In HR Schema, I created the following objects:

    F_EMP_ID NUMBER(12),
    F_JOB_ID VARCHAR2(50),


    Then I created the following procedure:




    Now I want to execute the procedure in SQL developer using “Run” option and show the output of the procedure of parameter “O_EMP_ARR”.

    Even in the auto-generated code, the line to show the output array is paused.
    Here is the auto-generated code:
    I_DEPT_ID := 50;

    /* Legacy output:
    –:O_EMP_ARR := O_EMP_ARR;

    My question is how to display the output of such types in SQL developer.
    Something rather than “DBMS_OUTPUT” of course.

    Thank you.

  21. Carsten Hansen Reply

    Hello Jeff,

    We use SQL Developer Data Modeler to model and document our Oracle databases. We have also used it to document some on-premise Microsoft SQL databases. We are however running into a problem connecting to the SQL databases we have in the Azure cloud.

    We get this error message:

    Status : Failure -Reason: Login failed due to client TLS version being less than minimal TLS version allowed by the server.

    I suspect that the issue lies with JDTS 1.3.1 not supporting TLS 1.2. Are you able to recommend another driver or a workaround to solve this connectivity issue?

    Thank you,


  22. Hey Jeff,
    i have ords 20.4 that running on tomcat 9

    im tring to see my instance metrics , so i add to the defaults.xml in my config folder this line:
    and restart.
    do i need to do anything else?
    and how can i see the metrics?


    • thank you ,
      i did it
      and i keep getting http 401…

      [user@]$ curl -v –user : http://:8080/ords/_/instance-api/stable/status
      * About to connect() to port 8080 (#0)
      * Trying …
      * Connected to () port 8080 (#0)
      * Server auth using Basic with user ”
      > GET /ords/_/instance-api/stable/status HTTP/1.1
      > Authorization: Basic #####################
      > User-Agent: curl/7.29.0
      > Host: :8080
      > Accept: */*
      < HTTP/1.1 401
      < Content-Type: application/problem+json
      < Content-Length: 183
      < Date: Mon, 22 May 2023 15:54:38 GMT

      "code": "Unauthorized",
      "message": "Unauthorized",
      "type": ",2020:error/Unauthorized",
      "instance": ",2020:ecid/##########################"
      * Connection #0 to host left intact

      what do i need to do?

  23. Are we able to send the queried report directly to email recipients by scheduling a job

    • Via DBMS_SCHEDULER, yes – but that’s a database feature/function, not a SQL Developer one.

      So you would have the database via job startup sqlcl, run your script, to get your report, and then have another item email that report.

  24. SQL Developer, great tool, snippets, great feature, i would love to be able to specify the location for the user snippets file (we are using a vm that doesn’t let us over write what was installed, so snippets we add disappear between sessions

  25. Daniel Looby Reply

    Back in late 2022 I believe you wrote and indicated in early 2023 that SQL Developer would be moving away from many of the Microsoft modules that it was using to open source modules. Since SQL Developer 23.1 documentation doesn’t reference such I wonder if there is an update on that change?

Write A Comment