Ask A Question

4.5+ million Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!

If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!

Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.

Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.

  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. Ulf Jonsson Reply

    I wonder if anyone has an example where I can use GET and return the data in CSV format with table column headings included. I also want to make this an automatic routine, that can be scheduled on time and place the csv file in a dedicated directory .

    Thx and regards!


  2. There is not specific problem. There are two parameters i do not know:

    I am preparing a presentation about standalone mode and would like to know which parameters are available for standalone mode. From my point of view standalone mode can be used in many APEX and/or REST projects.

  3. Hi Jeff

    is there a complete reference about all available properties in config file
    I cannot find a complete reference in ORDS Doc 19.2.

    kind regards Perry

  4. I want to prompt for these values in the same order as shown below:
    WHERE A.Aud_Yr_Nbr=:Audit_Year
    And A.Week_Nbr>=:From_Week
    And A.Week_Nbr<=:Thru_Week

    When I execute the script, it prompts for the Thru_week first, then the Audit Year and finally the From_Week which is very confusing for the user. Why does it change the order (TOAD prompts in the exact order) and is there any way to change this behavior? Oddly enough, if I use the & rather than the :, it prompts in the order that I want, but isn't it more efficient to use the : notation?

    Thank You!

    • thatjeffsmith

      Not sure why it works this way….

      Ampersand (client side text replacement) vs Colon (binds) – that’s a big conversation, and not just about efficiency, more around security. The DB is smart enough to auto-bind your query plans even if you use Ampersands in your query most likely.

  5. If I’m connected to SQL Developer as SYS and attempt to export a schema, often SYS and SYSTEM objects are generated as well. I ran across a post here ( with someone having the same issue. Unfortunately no date or version is given, so I have no frame of reference for their issue. In the post, someone supposedly from the SQL Developer team says “You have selected types which you have no objects for in the “other user” schema. The rule for export is that if you do not select any objects for a type that you select, we will export all of the objects for that type from the schema that is owned by the connection.”

    That doesn’t sound like desired behavior. Just wanted to get your 2 cents worth. I am using SQL Developer and Database.


    • thatjeffsmith

      That quote sounds like something I said.

      If you login as SYS, and you say, export all VIEWS, then you leave the filter empty for VIEWS, we’ll export all the VIEWS for SYS.

      What are you trying to do, and why are you logged in as SYS?

    • It happens when I’m logged in as SYS or when I’m logged in as myself with DBA privilege. So I connect as myself, then try to export schema XYZ using the Database Export tool. I select schema XYZ to export and select all objects within that schema. The export occurs, but it contains objects from other schemas.

    • thatjeffsmith

      If you check all object types on the first screen, make sure you have at least one item of each type from schema XYZ selected in the filter dialog.

      If you only select TABLES to be exported, and you bring over all the tables from XYZ in the filter screen, then when you do the export, you’ll only see XYZ tables, nothing from your login schema (SYS or whatever).

    • OK – thanks for the input. It just seemed to me like if there were no objects in schema XYZ of the type I selected it just wouldn’t return any of those object types, rather than getting them from another schema. I appreciate your time and explanation.

  6. Dave Campbell Reply

    Hi Jeff

    Hoping I can call on your REST skills (pun intended) 🙂

    Can you advise if there is any way to programmatically (in PL/SQL) identify the currently invoked RESTful service that is executing the PL/SQL code ?

    For example, I have a RESTful service that calls PL/SQL code and would like to use the calling service name for conditional logic etc. From within that PL/SQL code, is there any way to identify what parent RESTful service called it, similar to the way you can access APEX variables inside database PL/SQL code ?

    Much like we can use owa_util.get_cgi_env(param_name => ‘X-APEX-METHOD’) to get the method, it would be great if there was something similar to easily get the module or URI template – APEX can identify these when you set up a service, and I was wondering if these were available through PL/SQL. Perhaps undocumented? My Google ninja skills failed me on that search.

    I can get to them via regexp on the apex path, but hoping there is a cleaner way.



  7. Hi Jeff,

    Can you point m to any documentation which will help in using OAM authentication with ORDS?

    We are just getting started with ORDS and have some limitations due to our environment.
    ORDS 19.2 has been deployed on a Weblogic server as part of an APEX upgrade.
    During the deployment, ords.war was renamed to apex.war, and the context root is /apex
    I don’t think any “sql developer” user was created during install/deployment (our FMW admin did the deployment).

    We have /apex protected by OAM.
    I have 2 simply modules, demo and test, both doing pretty much the same thing (selecting from EMP).
    I have used SQL Developer to create a privilege and include the “test” module.
    At this point, I have not created any role, so according the the “Quick Start” doc I am looking at, that should result in /test requiring that the user be authenticated.

    So my question is not really asking you to solve this particular problem, but rather whether you can point me to documentation which will help in using OAM authentication with ORDS.

    Thank you

    For either the demo or test module, accessing the REST URL gets me redirected to OAM.
    Once I have logged in, I get redirected to “/demo” fine (not protected by the privilege).
    For “/test” however, I get either 404 or 401 (have not yet been able to discern what sequence prompts which error response0.

    • thatjeffsmith

      We’re in the process of adding this to the Docs

      Integrating Oracle REST Data Services and Oracle WebLogic

      Oracle REST Data Services (ORDS) recommends that for complex/enterprise user identity integrations, customers leverage the capabilities of Oracle WebLogic. Oracle WebLogic has a rich and diverse set of capabilities to integrate with existing enterprise identity solutions. When Oracle REST Data Services is deployed on WebLogic it can leverage those capabilities to secure access to ORDS based RESTful Services.

      When properly configured ORDS can ask Oracle WebLogic to provide the authenticated user’s identity and roles, and ORDS will authorize access to protected ORDS based RESTful Services based on the user’s role memberships.

      To configure ORDS to work in this manner with Oracle WebLogic, the following command must be executed to prepare the ords.war to be integrated with Oracle WebLogic.

      java -jar ords.war oam-config

      To view help on this command do:

      java -jar ords.war help oam-config

      What Does the oam-config command do?

      The oam-config command reconfigures the web.xml deployment descriptor in ords.war to tell WebLogic to pass on any established user identity to ORDS.

      After the above command has been run, ords.war must be re-deployed to WebLogic.

      How does ORDS determine the user’s identity and roles?

      ORDS uses APIs provided by Oracle WebLogic [1] to retrieve the WLSUser [2] and WLSGroup [3] for the established user identity.


      ORDS treats the WLSGroup as being equivalent to the role that the user possesses. In other words is a user belongs to a WLSGroup named ‘Sales Assistant’ then ORDS considers that user to have a role named ‘Sales Assistant’.

      How does WebLogic establish the authenticated user?

      Since there are many enterprise identity solutions and within those solutions many possible ways to define an identity architecture, this is a hard question to answer definitively. Please consult the Oracle WebLogic documentation on how to configure integration with your preferred identity solution. In general the user visits a single sign on login form and obtains a cookie or an access token that asserts their identity and roles. That cookie or token is passed to WebLogic. WebLogic is configured to know how to validate the cookie or token and map that to a specific user and determine what roles the user possesses. This is all done before WebLogic passes the request to ORDS. Once ORDS receives the request it calls the APIs mentioned above to retrieve this information about the user identity and roles from WebLogic.

    • Thanks very much for the quick response Jeff.
      The info you provided seems to be from a KB doc (2554551.1) I had found earlier on Support.
      I have asked our FMW admin, who does all our installs, to have a look at that doc and see how that info fits with our situation.
      For now I will just follow up with him and that doc.
      For another question, I have an SR open to hopefully get an answer.
      If I get an answer to that other question, I will reply here with the question and answer.
      Thank you again!

  8. Daniel Looby Reply

    In SQL*Developer ( Build 094.2042) i created a connection to Oracle 18 CDB using the service definition. My connection tested as successful. I connected and got my worksheet. I know (more on that in a moment) I should be able to do a ‘desc ps_job’ and get the table description. But instead I get:
    ORA-04043: object ps_job does not exist

    How do I know that should work? On a Linux VM I connected to the container via:

    sqlplus username/[email protected]:port/service

    and then did:

    13:10:00 SQL> select * from user_synonyms where synonym_name = ‘PS_JOB’;



    13:29:36 SQL> desc ps_job
    Name Null? Type
    —————————————– ——– —————————-

    So why can I describe the entity in SQL*Developer?

    • Daniel Looby

      One more note to add to the ‘weirdness’. In the Worksheet window I can do a ‘select * from ps_job where rownum < 10'… and it works.

    • thatjeffsmith

      Our DESC code is looking for the object and can’t find it.

      It does sound like a bug. If you pull up the View – Log – Statements panel, you can see the SQL we run when you do the DESC. Does anything look off there?

    • Sabir Mukadam


      I am trying to use Oracle Pivot functionality but came across limitation using it in IN clause where subquery doesnt work except you use with XML .If Oracle provide the functionality then it will be very useful and powerful tool to use.

      Sabir Mukadam

    • Daniel Looby

      Eight statements fire, in order:

      1. begin DBMS_UTILITY.NAME_RESOLVE ( name => :NAME, context => :CTX, schema => :SCHEMA, part1 => :PART1, part2 => :PART2, dblink => :DBLINK, part1_type => :PART1_TYPE, object_number => :NUM);end;
      Parameters: 1=”ps_job”, 2=0

      2. select count(*) from all_objects where object_name = :Name and owner = :OWNER
      Parameters: 1=”STG_OUR_PS_JOB_TBL”, 2=”EDWSTG”

      3. select distinct object_type from sys.all_objects where UPPER(object_name) =UPPER(‘ps_job’) and UPPER(owner)= UPPER(sys_context(‘USERENV’, ‘CURRENT_USER’)) and object_type != ‘INDEX’ and object_type != ‘TABLE PARTITION’ and object_type != ‘TABLE SUBPARTITION’

      4. select table_owner ,table_name, db_link from all_synonyms where synonym_name = UPPER(‘ps_job’) and (UPPER(owner)= UPPER(sys_context(‘USERENV’, ‘CURRENT_USER’)) )

      5. select username from sys.all_users where username=UPPER(‘EDWSTG’)

      6. select distinct object_type from sys.all_objects where object_name =’STG_OUC_PS_JOB_TBL’ and UPPER(owner)= UPPER(‘EDWSTG’) and object_type != ‘INDEX’ and object_type != ‘TABLE PARTITION’ and object_type != ‘TABLE SUBPARTITION’

      7. select table_owner ,table_name, db_link from all_synonyms where synonym_name = ‘STG_OUC_PS_JOB_TBL’ and owner =UPPER(‘EDWSTG’)

      8. select table_owner ,table_name, db_link from all_synonyms where synonym_name = ‘STG_OUC_PS_JOB_TBL’ and ( owner = ‘PUBLIC’)

    • thatjeffsmith

      Right, I know what code is ran.

      What I don’t know is what happens when that code runs on YOUR database.

    • Daniel Looby

      Right! I was going to give you the results of the SQL statements. Went to run the first statement (anonymous PL/SQL block) and got a wonderful:

      *** glibc detected *** sqlplus: double free or corruption (!prev): 0x0000000000baf320 ***

      followed by a very long backtrace. DBA has taken the instance down to determine the cause. Thankfully, this is a test instance.

  9. Hello Jeff,

    I can select a view in the Object Navigator to display tabs for Columns, Data, Grants … in a worksheet.
    The sourcecode under the SQL tab looks looks like:

    FROM emp
    where deptno = 10;

    How can I make automatically look like (without copying to a new worksheet and “Format” it:
    ) AS
    deptno = 10;

    The last style is better for finding errors.


    • thatjeffsmith

      That page is using dbms_metadata to generate the DDL (SQL), and it has it’s own ‘pretty print’ option for formatting the code. So what you’re seeing is the database ddl for the object as the database generates it.

      Having an option to auto-format it with SQL Developer’s formatter seems like a nice idea to me.

  10. Hi Jeff,
    I just downloaded and installed Sql Developer. Seems to work fine except one thing. I don’t see “Code Editor” in the Preferences list. I put a picture of the Preferences list on google. It can be viewed here:
    Clicking on the picture will make it larger. Do you know why there is no Code Editor in the list?

    Thanks for your help,

    • thatjeffsmith

      Weird, I’ve never seen this before.

      Try deleting your SQLDev folder and re-extract it from the download Zip

      Also, I’m assuming you didn’t disable any features, and you’re using a supported Oracle JDK.

    • Thanks for the quick reply Jeff! Your suggestion to re-extract worked perfectly. I’m sure the JDK is ok because it came with the Windows Oracle 18c XE I recently downloaded. My best guess as to what went wrong is that I unchecked ALL of the license stuff. I’m all set now. Again, Thanks for the help,

  11. Larry Tanner Reply

    Would it make more sense to name the SQL Developer version numbers based on when it is actually released ? 19.2 was released in Sept, so it would be more meaningful it was named 19.3 ?

    • thatjeffsmith

      Maybe. But then you’re assuming we know exactly when it’s going to hit the WWW in advance.

  12. Michael Moser Reply

    Hi – I just encountered yet another bug in SQLDeveloper. Where can I report those?

    • thatjeffsmith

      The official place is My Oracle Support.

      There are many unofficial places, including the place you are at right now.

    • Michael Moser

      [email protected] Re. .. including the place you are at right now:
      OK – so I report it here: when I export my connection settings (in my case from v19.2.1) I am asked to provide a password (that’s mandatory if one wants to export saved passwords and I want to migrate these as well).
      When I then import these settings into another version (in my case v19.1.0 since I have to downgrade to circumvent another bug) then the import reproducibly fails claiming that the provided password is incorrect. I am 100% positive I provided the same password – after all I inserted it into both dialogs using copy-paste. 🙁

  13. Miroslav Narodetskiy Reply


    I still hope that the profile clause will appear in the description, editing, and creation of the user.

    I also would like to clarify the info about the tabs “Dependencies” and “References”.
    If I understand correctly, in the “Dependencies” tab, I can find the list of the objects on which the current object referred to. In the human language, it is a result of:
    select referenced_name from user_dependencies where name=’My_Object’

    In the “References” tab, I can find a list of objects that reference the current object or in the readable form, it is a result of:
    select name from user_dependencies where referenced_name=’My_Object’.

    If I’m still right, I would like both tabs to exists for all objects, not just stored procedures/functions/packages. And also that the “Dependencies” tabs showed something for the user objects.

    I am my tools dependant.

  14. Hello,

    I held my questions hence I have a couple, I am not sure this is the right place and I would be happy to know if there is a better process for this.

    1. About sdcli, documentation is scarse, I would like if it is possible somehow to run a “Database Diff” from the command line. I don’t want to apply delta straight away but rather apply it later in a controlled flow.
    2. In the connection creation screen, when using ldap, all the entries are shown and it is not filtered as I type, hence I have to browse down until I reach the proper entry. Would it be possible to add this as a feature request. It’s not rocket science, but would really make life easier for our end users.

    I am using SQL Developer and SQLcl more and more, it is amazing what could be delivered as a free addition to the database.

    Best regards and thanks for your time,
    Jean Remacle

    • thatjeffsmith

      db compares aren’t available via the CLI…YET

      What version are you using of SQL Developer? We changed the LDAP logic to page the entries so you could actually filter/type…but you need version 19.1 or 19.2 to see that change.

  15. how can we group 5 pm yesterday to 5 pm today records to todays date

    oracle table

    id timestamp status
    1 2019-10-20 12:34:56.000 approved
    1 2019-10-22 12:34:56.000 approved
    2 2019-10-20 17:34:56.000 approved
    2 2019-10-21 12:34:56.000 approved
    3 2019-10-23 18:10:10.000 mod_in_ip
    3 2019-10-24 11:10:10.000 approved
    3 2019-10-24 12:10:10.000 approved
    4 2019-10-25 12:10:10.000 approved
    4 2019-10-25 18:10:10.000 approved
    I want to label id’s as new or edited. The problem is from yesterday’s 5 pm to today’s 5 pm is a working period. For example: an id record with yesterday 6:00PM record and today 11AM record should be labeled new if you look at the table and expected outcome you can get the idea

    but the expected result/outcome should be

    1 edited
    2 new
    3 new
    4 edited
    initally I tried this but it fails to address the above point

    select id,
    when count(id)<=1 then 'New'
    else 'Edited' End AS prefix
    from(select id,status ,trunc(timestamp) from table
    status='approved' and id in (1,2,3,4)
    group by id,status,trunc(timestamp))
    group by id
    the result is

    1 Edited
    2 Edited
    3 new
    4 new
    but the expected result is

    1 edited
    2 new
    3 new
    4 edited
    I am looking for solutions such as the innerquery groups records from 5pm to 5pm such that outer query can work fine or an entirely different solution is also feasible

  16. Heads up there’s a typo in SQL Developer after importing APEX app. DB > Application Express > Deploy Application > blah blah > finish – pop up = “Sucess” and “Application Imported Sucessfully”. Success is misspelled both places. Yes, I’m real fun at parties. 🙂

  17. Hello, I’ve purchased your SQL in one day book. Do you have the table data so I can copy and paste? Thank you

  18. I installed ORDS 3.0.11.over linux 7 server with Tomcat and
    I created a new workspace called OPERACIONES
    All seems to work fine from apex but when i try to access with Restfull Services, no mather if i use a hr example or a simple test i created, i get 404, when i setup debug i finally get:
    Actually i went over hundreds of pages, and all seems to be right… can you give me a direction to search?
    Sampe: hr sample.
    When i clic on Test goes to https://[myServerName]:8443/ords/operaciones/hr/version/ and I GET 404

    URLMappingNotFoundException [statusCode=404, reasons=[The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured]]
    at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(
    at oracle.dbtools.http.filters.HttpFilter.doFilter(
    at oracle.dbtools.http.filters.FilterChainImpl.doFilter(
    at oracle.dbtools.http.cors.CORSResponseFilter.doFilter(
    at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(
    at oracle.dbtools.http.filters.FilterChainImpl.doFilter(
    at oracle.dbtools.http.errors.ErrorPageFilter.doFilter(
    at oracle.dbtools.http.filters.HttpFilter.doFilter(
    at oracle.dbtools.http.filters.FilterChainImpl.doFilter(
    at oracle.dbtools.http.auth.ForceAuthFilter.doFilter(
    at oracle.dbtools.http.filters.HttpFilter.doFilter(
    at oracle.dbtools.http.filters.FilterChainImpl.doFilter(
    at oracle.dbtools.http.filters.Filters.filter(
    at oracle.dbtools.http.entrypoint.EntryPoint.service(
    at oracle.dbtools.http.entrypoint.EntryPointServlet.service(
    at javax.servlet.http.HttpServlet.service(
    at oracle.dbtools.rt.web.HttpEndpointBase.dispatchableServices(
    at oracle.dbtools.rt.web.HttpEndpointBase.service(
    at javax.servlet.http.HttpServlet.service(
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(
    at org.apache.catalina.core.StandardWrapperValve.invoke(
    at org.apache.catalina.core.StandardContextValve.invoke(
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(
    at org.apache.catalina.core.StandardHostValve.invoke(
    at org.apache.catalina.valves.ErrorReportValve.invoke(
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(
    at org.apache.catalina.core.StandardEngineValve.invoke(
    at org.apache.catalina.connector.CoyoteAdapter.service(
    at org.apache.coyote.http11.Http11Processor.service(
    at org.apache.coyote.AbstractProcessorLight.process(
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(
    at java.util.concurrent.ThreadPoolExecutor.runWorker(
    at java.util.concurrent.ThreadPoolExecutor$
    at org.apache.tomcat.util.threads.TaskThread$

    Thank you.

  19. Hello,

    We are using a lot of types in my work, some having a lot (30 more attributes), and I was wondering if some kind of “completion insight” existed for when you create an object of said type using a constructor method, using sqldeveloper. Because it’s very long and fastidious to have to write it all every time. I have tried to search everywhere on the web but it doesn’t look like anybody is looking for that.
    I was wondering, first, if that was a feature that existed and that I just did not know how to use, or if it doesn’t exist, there’s a way to create a PL/SQL function on my own, printing the constructor function in the output so then I can copy/paste and replace the fields.
    To basically have this generated after I type the ( :

    Object1:= “Type1″(
    attrib1, –NUMBER(10)
    attrib2, –VARCHAR2(10)
    attrib3, –VARCHAR2(255)

    Thank you for all you post here it already helped a lot in other topics.

  20. How to Print, store and process non English Characters like Tamil Language Alphabets in Oracle SQL developer?
    I hope this helps for all Languages is general.

    • thatjeffsmith

      The database needs a character set that supports your characters – Unicode (UTF-8 probably), AND SQL Developer needs to be setup to use a display font that supports those characters.

  21. Are there specific settings to prevent the SQL logic from being exported into the results of a query when using the “Run Script” option and spooling files to a directory on the harddrive?

  22. Hello,

    Are there more settings for Completion Insight? I recently updated from an older version to 19.2.1 and insight just doesn’t seem to be working like it used to. Specifically, I no longer get the join column help.

    For example, if I start typing a query like this:
    select t1.table_id, t2. user_name
    from table1 t1
    left join table2 t2 on [hit control-space]

    Previously, insight would pop-up with possible join options, but now, nothing happens.

    Are there additional options somewhere else? I do have the feature enabled and I searched the site but didn’t find anyone else having this sort of issue.


  23. Hi, Question about migrating mod_plsql gateway to ORDS – the install of ORDS has a step “If you are migrating from Oracle mod_plsql, then specify the PL/SQL gateway database username and database password” – what does this actually do to the specified schema? In particular, what if I have several schemas (as listed in the dads.conf) that need to be converted from mod_plsql to ORDS?

  24. Hi Jeff,

    It doesn’t appear that Data Modeler has ever been integrated into the Database Client software distribution (which includes SQL Developer – 17.3.2 for 12.2). Can you confirm? Thanks,


    • thatjeffsmith

      As a standalone, no.

      But Data Modeler is a feature of SQL Developer, so it is there at the end of the day.

    • Jeff – When I bring up the SQL Developer (17.3.2) that’s provided with the Database Client 12.2 software distribution, I can’t find a way to get to Data Modeler through it. Thanks,


    • thatjeffsmith

      If you don’t see ‘Data Modeler’ entries under File, View, or Tools menus, you’ll need to download it.

  25. Which Oracle SQL Developer version will support Windows 2016 – 64 bit OS

    • thatjeffsmith

      You’re not asking the right question.

      The right question is, what versions of Oracle JDK are supported on Windows 2016 – 64 bit OS?

    • Surendran

      What JDK version supports Windows 2016 – 64 bit OS

    • thatjeffsmith

      If you check the release notes for the different versions of Java, you can see it there. We support latest Java 8 Oracle JDK and Java 11 JDK – so see if your OS is listed on either of those. If so, you can run SQL Developer version 19.2.1.

Write A Comment