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.

Go!

8,013 Comments

  1. Hi Jeff,

    i’m currently evaluating the usage of the vscode plugin in my team. We have a ISO-8859-1 encoding in our databases. In the “old” sql-developer i was able to change the encoding, but i cannot find the setting in the vs-code plugin.

    That leads to the problem, that german umlauts are not correctly compiled to the package.
    I can enter an umlaut, but on compilation it seems like its converted to utf-8 and is broken. Any way to change NLS_CHAR?

    best regards,
    David

    • That encoding setting is working with files.

      Were unicode and always have been.

      I believe there’s a vs code setting to define the default file encoding m for your editors.

    • Yes, this setting exists (files.encoding) – and the pks file is opened with this encoding i’ve configured, Nevertheless, it does not seem to be recognized at save (compilation) as the umlaut is saved in utf-8, even if i explicity say that the file should be saved as iso-8859-1.

    • Lets say i have following procedure:

      create or replace PROCEDURE PRINTINVALIDPACKAGES
      as
      object_name varchar2( 1000);
      CURSOR c1 IS SELECT object_name from user_objects where status=’INVALID’ and object_type like ‘PACKAGE%’;
      begin

      OPEN c1;
      loop

      fetch c1 into object_name;
      exit when c1%NOTFOUND;

      dbms_output.put_line( ” || object_name);

      — Ä Ü Ö ä

      end loop;
      close c1;

      END PRINTINVALIDPACKAGES;

      Opened from the database, i included the comment with the german umlauts (hope it displays right here…)
      Now, i save this via STRG+S.
      Procedure is compiled – and content of the procedure is changed:

      create or replace PROCEDURE PRINTINVALIDPACKAGES as
      object_name varchar2(1000);
      CURSOR c1 IS
      SELECT object_name
      from user_objects
      where status = ‘INVALID’
      and object_type like ‘PACKAGE%’;
      begin
      OPEN c1;
      loop
      fetch c1 into object_name;
      exit when c1%NOTFOUND;
      dbms_output.put_line(” || object_name);

      — Ä Ü Ö ä

      end loop;
      close c1;
      END PRINTINVALIDPACKAGES;

      (see the comment)

  2. Hey Jeff,
    If we’ll implement a tomcat within a container and ORDS just like a VM will it still be supported from oracle?
    Also how can we create ORDS for K8S when we got external ORACLE DB (outside the cluster) with an operator?

    Thanks,
    Dolev

    • Thanks for your answer,
      about the second question will it actually work if the ORDS is on the cluster and the DB is outside the cluster?

    • I think if you want to use ords as the k8s operator on the databases, like to create or clone a pdb…then it needs to be on the same cluser as the db.

      Sorry if I have misread your question. If that’s the case, i need more details. I only spell K8s, I don’t work with it.

  3. Hi Jeff,

    I have just started to use the VS code version of SQLDEV and it is really good. Keep up the good work 🙂

    I have noticed that some of my PL/SQL source files have been flagged in VS code as having a syntax error, but when I compile it, it succeeds without error or warning. Is this a bug in VS code or the extension?

    Also are you planning to enhance the source code formatting for PL/SQL?

    Many thanks,
    Mark.

    • Were using the same parser here and sql developer… do you have any examples you can share?

    • Hi Jeff,

      Thanks for the reply.

      The following snippet of code is within one of my PL/SQL package specification that compiles without error or warning but reported by SQLDEV and VScode as a syntax error.


      c_max_json_key_length CONSTANT SYS.STANDARD.PLS_INTEGER := 128;
      SUBTYPE json_key_st IS
      SYS.STANDARD.VARCHAR2(c_max_json_key_length BYTE);

      It is the VARCHAR2 portion of the SYS.STANDARD.VARCHAR2 clause in the subtype definition that is reported as a syntax error – “Syntax error. Partially recognized rules (railroad diagrams):”.

      Also on the subject of parsing, do Oracle publish a complete specification of the PL/SQL grammar used in 23c? I could create such a grammar file myself using the many railroad diagrams in the documentation, but using a vendor supplied and supported grammar would be my choice if possible.

      Many thanks in advance,
      Mark.

    • The same parser issue we see in ALL of our tools…and yes, our parser uses the syntax diagrams published in the docs to build our grammar.

      set serveroutput on

      declare
      x SYS.STANDARD.VARCHAR2(250);

      begin
      x := 'weird';
      dbms_output.put_line(x);
      end;
      /

      Just curious, do you do that with ALL of your data types? That’s some intense security precautions.

    • Hi Jeff, did you get chance to see my latest reply?

      Thanks,

  4. Hey Jeff,

    My team is getting a LLM/AI code chat bot that will be hosted on our internal network.

    It has been trained on a variety of programming language including Java and basic SQL but not PL/SQL.

    The goal is to maximize the AI so we can code more efficiently. Would you happen to have any resources/tip/reference on how we can train this AI on PL/SQL??

    • Mmmmm, not really. I could show you how to augment your LLM via RAG, or how to hook up your LLM directly to our database via MCP Server, however.

  5. Hello Jeff,
    we are currently trying to move our ORDS to openshift,
    Is there any good operator for this from oracle or the only way is to perform a helm installation?

    I’ll appreciate your comment.
    Idan

    • Openshift doesn’t support oracle java, so ords isn’t supported on that platform

      Doesn’t mean it won’t work…

  6. Vivek Gupta Reply

    Hello Jeff,
    Thank you for this great ORDS product and all the help/documentations. Recently i upgraded Apex 20.2 to 24.2, ORDS 21.4 to 25.2 , OHS 12c to 14c. Post upgrade i am able to run ORDS API’s however apex is not launching with error “Failure of Web Server bridge:” Internal Server Failure. Cannot Continue.

    It seems this is a bug 38150252 as per MOS note 3080572.1 as i see similar messages in OHS log. Not sure if this bug is with ORDS or weblogic and whats the ETA as i am stuck. Also the workaround mentioned in the note doesn’t help.

    Will be creating SR as well

    Thanks,
    Vivek

    • It’s a bug between WLS and OHS. If you straight up run just ORDS (standalone), it’s fine.

      If you just run ORDS in WLS, with no OHS, it works.

    • Thanks for the revert Jeff. Yes direct access to ORDS working fine and after i reverted back to 12c OHS everything works. Clearly something related to OHS14c and WLS. Created SR with OHS team and will follow up. Thanks again

  7. Hi Jeff
    I’m facing some issue with Oracle APEX 24.2 to get proper RESFul API based on a package for three tables.
    In fact I could generate the table packages and then created the AutoREST objects, but the final endpoints creted does not seem what I expected.
    I followed the steps :
    1o) Open SQL Workshopand acess Utilities ==> Method on tables

    2o) Put package name : PKG_Customer

    3o) Select the three tables to crete the package : tcustomer, tcity, tsalesperson

    4o) Created the package PKG_Customer

    5o) Then I went to SQL Workshop ==> RESTFul Services == Create AutoREST objects, it generated the endpoints on full URL : http://localhost:8080/ords/wsgcash/RESTpkg_customer/

    6o) Open SQL Developer Web to check the REST Objects ==> REST ==> AutoREST

    7o) Check the PKG_Customer API code PKG_Customer ==> View OpenAPI
    a) Two of the some endpoints created for the tcity table were /GET_TCITY/1 and /GET_TCITY/2
    b) Although they were named as GET_TCITY they were defined as POST verbs and required a parameter in the request body
    c) This is the endpoint url : http://localhost:8080/ords/wsgcash/RESTpkg_customer/GET_TCITY/1
    d) this is the cURL call
    curl -X ‘POST’ \
    ‘http://localhost:8080/ords/wsgcash/RESTpkg_customer/GET_TCITY/1’ \
    -H ‘accept: application/json’ \
    -H ‘Content-Type: application/json’ \
    -d ‘{
    “p_cit_id”: 1
    }’
    e) it does ont run and showed the error :
    404 Error: Not Found
    Response body
    {
    “code”: “NotFound”,
    “message”: “Not Found”,
    “type”: “tag:oracle.com,2020:error/NotFound”,
    “instance”: “tag:oracle.com,2020:ecid/aDbnmbom6-qQqzCqNT_tuQ”
    }
    f) I also run this requst in Postman but I had the same 404 error

    I also opened this request on stackoverflow and provided screenshots of each step.
    https://stackoverflow.com/questions/79675561/oracle-apex-24-restful-services-url-endpoints-verbs-does-not-work

    Why the endpoints and verbs created by using “SQL Workshop ==> RESTFul Services ==> Create AutoREST Objects” based on package are generating only POS|T verbs and those are not working as expected?

    Did I miss some step or am I using the SQL Web Developer in the wrong way?

    I appreciate your comments on this.

    Thanks.

  8. Vitaliy Kildeyev Reply

    Hi Jeff,
    I am trying “PROJECT” feature in SQLcl. Is there a config option to set liquibase schema?

    • No, that’s not supported with Projects command, directly.

      However, if you inspect the install.sql script, you can see the liquibase commands…and you could add the -liquibase-schema-name parameter to the update changeSet command. And if you do that, it’s on you to do it, correctly, going forward as you would risk breaking your installer, as we won’t know what you’re doing.

  9. Robert Boucher Reply

    Hi Jeff, I was wondering if you knew if there is an (official) effort at Oracle to create a model context protocol (MCP) server to allow Gen-AI enabled tools like VS Code to be context aware of Oracle databases? Thanks!

    • Robert Boucher

      Thanks Jeff. I’ll keep an eye out for the MCP announcement.

  10. Peter Drahos Reply

    I appreciate you for taking the time to answer the question..and thank you for the tip.

  11. Peter Drahos Reply

    Hi Jeff,
    I started using the extension 25.1.1 recently and connected it to EBS 12.2.14, while I can use sql worksheet and run queries I am not having any success populating the object tree, should this be working or is there a trick to it. I am wondering if the reason is the amount of data that needs to be pulled in for browsing through the objects. Every presentation on line that I saw is using a simple DB, just not with number of items EBS database contains.
    Thank you
    Peter

    • We don’t care how ‘big’ your database is.

      We’re querying the dictionary for objects, and it tells us what is there.

      You can see the queries we’re running in the Output panel, with category switched to SQL Developer Log. In Extension preferences, set the extension logging to ‘TRACE.’

      We’re basically querying the contents of the ALL_ views like ALL_OBJECTS, ALL_TABLES, etc.

    • Peter Drahos

      never mind sorry about that, I figured it out after going into Other Users and then applying filter, it works as would expect. I need little more time discovering the tool.
      Thank you

Write A Comment