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.

Go!

5,517 Comments

  1. Hey, Jeff,
    I am running ORDS in standalone, with configuration mapped to replace our mod_plsql setup.
    Running it localhost is ok, but when I put it on an actual server with a load balancer in front of it and a DNS name with URL that of course is different the actual host name ORDS is running on I get the Cross Origin error (with Chrome and FF; IE has no issue).
    “The request cannot be processed because this resource does not support Cross Origin Sharing requests, or the request Origin is not authorized to access this resource. If ords is being reverse proxied ensure the front end server is propagating the host name, for mod_proxy ensure ProxyPreserveHost is set to On”

    I am not finding much info about issue online or in the docs. Where/How is the best possible place/way to avoid this error. I don’t understand if this 403 error comes from Jetty or from ORDS itself.
    I guess the safest would be to enumerate the allowed Origins somewhere in the ORDS configuration. But where and how?

    • I had several problems with returned “CORS” Error. I finaly found, that I had to escape some special charakters like the “curly bracket” in my URL to the service. Windows Tomcat was ok, Linux Tomcat didn’t work without escaping.

    • I have no braces in the URL and furthermore, there is no Tomcat of any sort involved I am running ORDS in standalone mode. I have found your question here (I searched for this before posting) and it does not apply to my case.

  2. Hi Jeff,

    Sorry but another one of those SQL Developer formatter questions.

    I know that later versions of the formatter allows users to paste their own code to “show” SQL developer how they would like their source code formatted. Unfortunately when I do it, it doesn’t honour most of my settings which is unfortunate.

    The main reason for this is I want to tie the formatting engine of SQL developer into our source control pre-commit “hook” that automatically reformats the entire source prior to check-in. This way we can maintain code that conforms to visual code standards whilst developers concentrate on the actual code. There may also be a requirement to convert multiple source code files in batch from time to time.

    I have seen a previous post on this site that goes into incredible detail about fiddling with the rules at each step of the parse, but I did find it a little hard to follow. Anyway, below is a snippet of PL/SQL code with my desired formatting (please convert into monospace font to see the indentation). I would appreciate some help and/or advice to get something like this automagically.

    Kind regards,
    Mark.

    ============

    DECLARE
    SUBTYPE identifier_st IS
    VARCHAR2(128 BYTE);

    FUNCTION camel_to_snake
    (
    ident IN identifier_st
    )
    RETURN
    identifier_st
    IS
    v_retval identifier_st DEFAULT NULL;

    BEGIN
    v_retval := SYS.STANDARD.REGEXP_REPLACE
    (
    srcstr => SYS.STANDARD_REGEXP_REPLACE
    (
    srcstr =>
    ,pattern =>
    ,replacestr =>

    )
    ,pattern =>
    ,replacestr =>
    ,position =>
    ,occurance =>
    ,modifier =>
    );

    RETURN(v_retval);

    END camel_to_snake;

    BEGIN
    SYS.DBMS_OUTPUT.PUT_LINE
    (
    a => camel_to_snake
    (
    ident => ‘convertToSnake’
    )
    );
    END;

    • Oh dear… my source code has lost the formatting on post. 🙁

  3. Hi Jeff,

    In SQL you can create different types (array, object, table), how can we see in one overview to which category(array, object,table) a type belongs?
    Thanks in advance.

  4. Hi Jeff, first of all thank you for the great job on liquibase.
    I have two problems using the lb update:
    1.
    The command
    lb update tablename_table.xml false
    leads to a tablespace-error: 00959. 00000 – “tablespace ‘%s’ does not exist”
    Is it possible to ignore the tablespace name when I genobject the table?
    2.
    The command
    lb update controller.xml false
    brings the following error:
    Liquibase konnte die Anforderung derzeit nicht verarbeiten (allgemeiner Fehler).

    liquibase.exception.SetupException: Error Reading Migration File: Invalid byte 2 of 3-byte UTF-8 sequence.

    Can you or anyone please help me with these problems?
    Best regards René

    • thatjeffsmith

      For the first one, use the set ddl command to disable storage, tablespace, partitioning, etc as needed…

      Then when you generate your changelog, that info won’t go into the XML

    • Thenks Jeff, that worked for the single lb genobject.
      In case of lb genschmema, the set ddl doesn’t work. You know how to deal with that?
      Kind regards, René

    • thatjeffsmith

      Yup, it’s a bug.

      Good news though, developer found the issue and fixed it for v20.1

      Thanks for the report!!

  5. Hi Jeff,
    I download sqlcl-19.4.0.354.0937. I’m try connect to oracle database 19.3.6 with Kerberos authentication and I have following error:
    ———————
    USER =
    URL = jdbc:oracle:oci8:@(description=(address=(community=tcp.world)(protocol=tcp)(host=xxxxxxxx)(port=1521))(connect_data=(sid=xxxxx)))
    Error Message = no ocijdbc19 in java.library.path
    USER =
    URL = jdbc:oracle:thin:@(description=(address=(community=tcp.world)(protocol=tcp)(host=xxxxxxxx)(port=1521))(connect_data=(sid=xxxx)))
    Error Message = ORA-01017: invalid username/password; logon denied
    —————————–
    Can you help?

    Thanks Mary

    • thatjeffsmith

      I’m not the best Kerberos person, but others have blogged this, also should be the same process as sqldeveloper

  6. Hi.

    Migrating to Apex 19.x on 18c. On prototype box ORDS restful services OK.

    But on migration target servers,while APEX via ORDS is fine, even oracle.example.hr restful services, instantiated with the register schema with ORDS click, get 404 page with reason blank. So far, I’m unable to detect a difference in configuration.

    Can you please illustrate the probable diagnostic steps, highlighting those where SYSDBA privileges are required?

  7. Amin Adatia Reply

    SQL Data Modeler v19.4.0
    Database v19.3

    I was trying to create the Repository for doing Reports. There is a bug in the script to create the Type (I added the / at the end and compile worked. The Package has an item, Objects_List showing as invalid. Perhaps database 19.3 is looking for something else

  8. Hi, I’m trying to make an ORDS module of source type pl/sql to call a package-procedure that returns a out sys_refcursor from a query. I added a parameter of type RESULTSET, as ORDS documentation says that the result will be automatically formatted in JSON in the response. And that is what we want. We have hundreds of get-procedures we want to expose as a ORDS service. But: it doesn’t work: when I post a request to the ORDS service from Postman I get a 500 status. This is the example I rebuilt: BEGIN
    ORDS.define_module(
    p_module_name => ‘testmodule6b’,
    p_base_path => ‘testmodule6b/’,
    p_items_per_page => 0);

    ORDS.define_template(
    p_module_name => ‘testmodule6b’,
    p_pattern => ‘get-employee-details’);

    ORDS.define_handler(
    p_module_name => ‘testmodule6b’,
    p_pattern => ‘get-employee-details’,
    p_method => ‘POST’,
    p_source_type => ORDS.source_type_plsql,
    p_source => ‘BEGIN
    get_employee_details(
    p_empno => :empno,
    p_employee => :employee);
    END;’,
    p_items_per_page => 0);

    ORDS.define_parameter(
    p_module_name => ‘testmodule6b’,
    p_pattern => ‘get-employee-details’,
    p_method => ‘POST’,
    p_name => ’employee’,
    p_bind_variable_name => ’employee’,
    p_source_type => ‘RESPONSE’,
    p_param_type => ‘RESULTSET’,
    p_access_method => ‘OUT’
    );

    COMMIT;
    END;
    /

    Should this way of returning a sys_refcursor as a resultset work? I really hope so!

  9. How about a new feature: Saving current working area regularly and without need to save every single worksheet separately.

    I often run into situation to be forced for reboot my PC, but have opened many sessions and worksheets.
    If I have the time, I check all important and not already saved worksheets and save them.

    I know, that I can save all worksheets automatically to a generic filename with increased numbered suffix, but this causes over time many many different files and even more, I would have to delete them manually on OS-level, because I cannot delete them directly out of SQL-Developer.

    Even more, this option doesn’t protect me from surviving crashes, like blue-screen or closed by any privileged installation with automatic killing processes during my absense.

    I would appreciate a kind of workspace backup including all current and even unsaved worksheets, similar what browsers do (restore closed tabs), so that I don’t loose my last developed queries. This should save just for each running instance separately. I’m fine with “last saved wins”. Would be sufficient to have this optional (preferences) and to configure save intervals. I think, 5 mins is sufficient, but only if any changes were made.

  10. Hello,

    Do you think it might be possible to have permalinks for SQLcl as it is the case today for instant client?

    Cheers,
    Jean Remacle

  11. Hi Jeff,

    How to audit only the sessions that is coming from sql develpoer?

    I tried to define a audit policy but it is not monitoring the sessions coming from sql developer?

    alter audit policy audit_revpro_bala condition ‘sys_context (”userenv”,”client_program_name”) in (”SQL DEVELOPER”)’ evaluate per session;

    • thatjeffsmith

      …in(‘SQL Developer’, ‘Raptor’)…

      Also, beware of people changing their client_program_name for their session, to avoid auditing?

  12. Amin Adatia Reply

    In SQL Data Modeler, on an Entity what does the the triangle on the Top Right corner indicate?

    • Amin Adatia

      Figured out the reason for the triangle showing up in the top right corner of an Entity is when some attributes are hidden

  13. Hello Jeff,

    I am doing one POST operation that should insert only 1 record at the time in a table where a text file is inserted into the blob_document column. ( see table DDL here below. )
    But instead it is creating 2 records.
    One will have the blob column populated correctly with the text file and a second record where the blob_document column is empty.

    I have here below the DDL of the sequence and table (with trigger) and the POST command sql statment.


    CREATE TABLE “AL”.”DOCS_JSON”
    ( “SEQ_ID” NUMBER(10,0) NOT NULL ENABLE,
    “LOADING_DATE” DATE DEFAULT sysdate,
    “BLOB_DOCUMENT” BLOB
    ) ;

    CREATE UNIQUE INDEX “AL”.”DJ_PK” ON “PAL”.”DOCS_JSON” (“SEQ_ID”);

    CREATE OR REPLACE TRIGGER “AL”.”BIUD_DJ_TRG”
    before insert or update or delete on docs_json
    for each row
    begin
    if deleting then
    null;
    elsif inserting then
    select dj_seq_id.nextval into :new.seq_id from sys.dual;
    elsif updating then
    :new.seq_id := :old.seq_id;
    end if;
    end;


    — POST —
    declare
    l_blob blob := :body;
    l_seqId number := 0;
    begin
    — nextval —
    insert into DOCS_JSON( LOADING_DATE, BLOB_DOCUMENT ) values ( sysdate, l_blob ) returning seq_id into l_seqId;

    :response := l_seqId;
    :status := 200;

    exception when others then
    :response := ‘Status NOK.’;
    :status := 400;
    end;

    Can you check if you can see what’s the problem?

    Regards,

    Anibal

    • Hi Jeff,

      I am just in a testing phase.
      For this i am using POSTMAN and only click once on the SEND button!

      Could i be a problem of the ORDS version?

      Regards,
      Anibal

    • Hi Jeff,

      I have put an condition that the l_blob (:body) (in this version of ORDS) is not null then it is only inserting one record.
      It is like ORDS re-evaluate the the :body tag twice!

      Could this be true?

      Regards,
      Anibal

    • Hi Jeff,

      I changed the code a little bit and putthe condition if the blob is not empty.

      declare
      l_blob blob := :body;
      begin

      if l_blob is not null
      then
      insert into DOCS_JSON( BLOB_DOCUMENT ) values ( l_blob);
      end if;

      commit;

      :response := ‘Status is OK’;
      :status := 200;

      exception when others then
      :response := ‘Status is NOK’;
      :status := 400;
      end;

      It is like a re-evaluation of the :body is taken place.

      Regards,
      Anibal

  14. Hi, Jeff, I know you like questions and you do reposnd timely. For mine, I could not think of anyone more relevant to ask, but you.
    We have a big application in PL/SQL with some frontend in AngularJS. We currently run this on Webtier 12.1.3 with mod_plsql.
    Anywas, I have this task of migrating from mod_plsql to ORDS. I have two options here – one is to use Apache and Tomcat and deploy ORDS on Tomcat. The other one, and preferred by me, is to run ORDS in standalone mode. However there are concerns if ORDS standalone is prodution ready, is an adequate solution and whether it can cope with all the traffic (thousands of customers) in production.
    What would you say?

    • thatjeffsmith

      ORDS standalone would be just fine.

      The real question is would you have any particular implementation and management requirements for your system that Tomcat would provide out of the box?

      ORDS standalone is a very specific implementation of Jetty. We run it in production here at Oracle, and it can handle the load. You may want to consider, whichever way you go, front-loading 2-3 ords/tomcats with a loadbalancer though, esp for HA benefits.

    • Branimir

      Yes, we definitely have a Load Balancer in front of equally configured webtiers with mod_plsql. So we will keep the same structure. As for Tomcat, the only reason to use it was for us to run ORDS on it. No other requirement. I am following the migrating-mod_plsql-ords.html doc.
      Thank you for your answer. I now have a good direction to follow.

    • thatjeffsmith

      You don’t need to add Tomcat, but you always can later if you’d like. It’s very popular with our customers – it’s free, lightweight, and has a ton of features available for ‘extras.’

    • Branimir

      Hey, Jeff, I had long and sleepless nights fighting with ORDS. I have been through the docs inside and out and all over google. I cannot find help for my situation.
      Basically, the problem is I must have context path “/”, not /ords and not /pls. Not a single example about this. The only way to have this is if I run java -Xms512m -jar ords.war standalone –context-path “/”
      Through a lot of experimenting I found a few bugs when context path is root:
      1) Even if standalone.properties file contains “standalone.context.path=/” I still have to start ORDS with –context-path “/” every time, or ORDS starts again looking inside \ords\config.dir\…
      2) ORDS disregards config.dir and creates/looks for configuration files next to it, not below it. Now that is fine for me. It might only interest you as a bug. But the third one is a real show stopper:
      3) document root does not work. ORDS says “The document root is serving static resources located in: c:\ords\www” but when I browse them, I just get 404.

    • thatjeffsmith

      I just edited the standalone.properties file to point to my static content folder, started ORDS, and had index.html served at the base URI with and without a slash.

      Document root definitely does work – we’d have thousands of APEX customer raising Cain otherwise.

    • Branimir

      Yes, document root works. But you missed the important detail at the beginning – it does not work when context path is empty: /. Not /ords, not /apex, not /pls, etc. I must have it empty /. In that case, document root files are not served.

    • thatjeffsmith

      No I didn’t, see my screenshot, I pulled up both
      localhost:8080 and
      localhost:8080/

      both serve up the index.html in my doc root

    • Branimir

      Right, my bad. I looked more carefully. Yet I am still getting 404 “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” when I browse localhost:8080.
      I also have a db-name called “bg” in the pool and localhost:8080/bg/ does work properly, however it is made to load static resources, like images, which fail to load.
      I also tried to simulate your case with …/standalone/doc_root/index.html but I still get this 404.
      Is ORDS mistaken, by trying too look in the pool for a db connection? Could the reason be in an opton in defaults.xml? I don’t know. I am desperate 🙁

    • thatjeffsmith

      We can do a call on Monday, I’ll talk you through it.

      However you said earlier you mapped / a routing pattern? That’s not necessary for what you’re wanting.

  15. Peter Melis Reply

    Jeff, how can we tell that a column is virtualy defined in a certain table without looking into the DDL? Is there a symbol for it?

    Also we want to see what is partitioned without looking into the DDL of the table, is there a way to do this?
    Thanks!

    • thatjeffsmith

      DBA_TAB_COLS probably indicates that

      For partitions you might be able to guess based on the high/low value of the partition, otherwise, yes you need to to look at the SQL page. I have a standing ER to make the partitioned ‘key’ more obvious in the Partitions tab.

  16. Hi.
    I just would like to ask what can I do to solve this problem . My run statement button has just stops working. The “Run” menu options are disabled and I have no idea why. I think that I mistakenly pressed ctrl + Shift+i or ctrl + Shift+r because I wanted to relpace a word, but now I can n ot run my query. I am so worried about it. If someone can help me, I really appreciate it.

    • thatjeffsmith

      Those will only be available during a debugger session as it’s running – except for the Toggle Breakpoint item.

      Can you start and run the debugger?

  17. Hi,

    I want to connect with SQLDEV to kerebos;
    I’ve configured
    kerebos Thin Config
    krb5.conf
    credential cache File
    However whenever I set Authentication Type Kerebos
    it keeps asking me for username / password.

  18. Michael Moser Reply

    Hi Jeff – with your examples I got my utility working perfectly! The type=”SCRIPT” attribute for the sql tag was the magic incantation!

    There is one last thing though: My script produces its output using dbms.put_line(…)-statements and the output in the Messages Log-window gets wrapped.
    Since the utility is generating sql statements these MUST NOT be arbitrarily wrapped (esp. not in the midst of a string!) because then the result will be non-parseable and my tool gets useless.
    Is it possible to prevent the log-window from wrapping lines? Or is there a different output channel or tab or windows where I could direct the output to?

    • Michael Moser

      Never mind. I found the answer elsewhere on your website.
      Thanks for providing this! It’s a real treasure trove!

      Regards and cheers,
      Michael

  19. Hi Jeff,
    Is it possible to open SQL developer web from my custom web-app in other bowser/iframe/newtab. And does SQL developer web provides some web hooks through which another web can communicate with it ,i.e send/receive queries and perform other operations?
    Thanks

    • thatjeffsmith

      First question, yes…it’s just a html5 web app, so I don’t see why not.

      Second question, maybe. It’s a REST API too. What are you thinking about having it do?

    • Zeeshan Noor

      We want to have some kind of functionality to open the text of an existing query or pl\sql script from our web application inside your tool. Since we want to have auto-completion support for database objects (like tables, views, columns, …), we need to somehow also provide you the connection (credentials) to the db. Additionally, we want to syncronize the changed query text back to our tool.
      Unfortunately, we were not able to find a proper API for your tool to do so. Could you send us the REST API, you were talking about so we can checkout if it suits our needs?

  20. Michael Moser Reply

    >> I’ll take a look, but have you seen our GitHub repo with extension examples?

    I found the repo but I was a bit overwhelmed by its contents. Is there some overview or table of contents?

  21. Thank you very much for the immediate response Jeff! Truly appreciate it. I just downloaded 19.4 version, that loads faster than 19.2 and as you mentioned, right clicked on the connection node (After using the suite for almost 4 months everyday!) for the first time to realize that you have a schema browser already available.
    I do understand the JAVA platform implications, however, please let me to request you to add a feature which will allow us to pin frequently used folders to the folder browser left hand side panel, that randomly attaches folders. Thank you once again!

    • thatjeffsmith

      Should be the last 5 or so directories you’ve open or saved files to.. Not a random list.

  22. Hi Jeff
    Do you have plans to add a dedicated “Schema Browser” like in Quest Toad? We are switching from Toad for commercial reasons & after using Toad for last 12+ years, my developers are finding it bit difficult to deal with the current schema browsing approach SQL Developer has.
    We would appreciate, if you can associate the Windows folder browser standards with folder browser as well. Thank you! you guys are doing a wonderful job

    regards,

    rajesh

    • thatjeffsmith

      We have one! Right click on your connection to open and use it.

      We don’t have access to the windows features like that as a Java application, alas…

  23. I’ve changed the NLS to DD-MON-RRRR without the time component, so that when I run the import, it should just import my dates… right? Yet when I hit the Verify button, my date fields get an error, so I hit the Back button and change the Format field to DD-MON-RRRR for each of my 6 date fields, and then the import works. Changing the format for each date field is not very automated. Why won’t changing the NLS parameter work for date importing? I can’t find posts out there specific to this issue. Thanks!

    • thatjeffsmith

      You’re describing the date format of the data in the file you’re importing…it could be different for each column.

      Also, what are the odds your session parameters would match up exactly your import scenario?

  24. Duane Dieterich Reply

    Data Modeler 19.4.0.350, Build 350.1424 won’t restart after disabling 2 features: “Oracle NoSQL Database 19.3.12” and “Oracle SQL DEveloper – Cloud Support”. I was able to fix by removing the “oracle.sqldeveloper.onsd” and “oracle.sqldeveloper.cloud” string tags from the “disabledExtensions” list in “%APPDATA%\Oracle SQL Developer Data Modeler\system19.4.0.350.1424\ide-extension-prefs.xml” file.

Write A Comment