Mostly Making oracle easy, mostly:
Search for:

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!

7,859 Comments

  1. Rejean Reply

    Hi Jeff,

    I started using SQLcl and I really like it.
    I have a question regarding the ” set sqlformat ansiconsole -config=hightlight.json”

    Where should we create this file ?
    On a windows pc and on Linux Server

    Because I tried in the SQLcl directory and to other directories but I always get the error :
    Invalid File : highlight.json

    Take note I’ve got the info from your site
    here : https://www.thatjeffsmith.com/archive/2021/10/oracle-sqlcl-all-the-pretty-colors-for-your-console/
    And in Kris : https://krisrice.io/2019-04-18-SQLcl-SQLFormat/

    And yes, I notice the error in the help about the “endWith” in fact we nee to use “endsWith”
    Thanks

    here the highligh.json

    {“highlights”:[
    {“type”:”startWith”,”test”:”P”,”color”:”INTENSITY_BOLD,CYAN”},
    {“type”:”endsWith”,”test”:”TT”,”color”:”BLUE”},
    {“type”:”contains”,”test”:”RCAL”,”color”:”YELLOW”},
    {“type”:”exact”,”test”:”FORD”,”color”:”GREEN”},
    {“type”:”regex”,”test”:”[0-9]{2}”,”color”:”MAGENTA”}
    ]
    }

    • thatjeffsmith Post Author

      you can put the file anywhere you want, if the CWD isn’t where it’s at, you’ll need to either cd to the directory or provide the full file path

      Here’s my example

      Your JSON as shared is invalid, it has ‘smart quotes’ .. but not sure if that’s because of the way it was copied/pasted (from Word?)

  2. gk Reply

    Hi Jeff,

    I really like the SQL Developer extension for VS Code, it has improved my workflow and reduced the context switching.
    One thing I wanted to check is reg. the Query Result pane/grid.

    In the old SQL Developer, we are able to select and run multiple queries and see the results in separate Query Result tabs… Is this functionality available in VS Code extension? If not, is this something your team is looking to implement in future?

    Thanks.

    • thatjeffsmith Post Author

      YES!

      What you can do right now is open multiple sql worksheets and run a query in each, and each will see their query results.

      But having ability to see them side-by-side, is something we need AND plan to build this year – including for execution plans.

  3. Pak Reply

    Hi Jeff,

    I am new to ORDS and want to log each API calls automatically (I’m still trying to get my DBA to help setup prehook).

    In the meantime, I have defined my POST handler as below

    ORDS.DEFINE_HANDLER(
    p_module_name => ‘rest’,
    p_pattern => ‘item’,
    p_method => ‘POST’,
    p_source_type => ‘plsql/block’,
    p_items_per_page => 0,
    p_mimes_allowed => ‘application/json’,
    p_comments => NULL,
    p_source =>
    ‘DECLARE
    BEGIN
    insert into table1(:ITEM_ID, :ITEM_TYPE, :ITEM_QUANTITY);
    ins_api_log (:current_user, :content_type, :body_text, v_response);
    commit;
    END;’
    );

    I found if ins_api_log in the block, :ITEM_ID is null where as if I comment out ins_api_log line, :ITEM_ID is the bind data from json.

    The above plsql/block works without issues with GET, DELETE. Is there a limitation to POST?

    Note I checked ins_api_log is working as expected and inserting into the target table with required data.

    • thatjeffsmith Post Author

      I would need to see the json payload you’re including on that POST.

      Your webserver logs will have what you want, by the way, you’ll see all the incoming HTTP/HTTPS requests, including the ones for ORDS API calls. No need to use a prehook for this, unless you ALSO want to log this in the database instead of in your OS or logging/metrics stack.

  4. Steve Givens Reply

    I installed the VS Code extension for SQL Developer yesterday and completed importing my connections. But I’m having a weird thing happen when trying to connect to certain databases. We have 2 domains. I can connect to databases in the domain I’m currently logged in to with no issue. In order to connect to a database in the other domain, I have to open VPN to that domain. But here’s the strange thing – I don’t have to do that in SQL Developer, both domains work without VPN. Furthermore, I can open SQLcl in VS Code and connect to any database. It is only when testing a connection or trying to open SQL Worksheet that I get this error:

    A connection with the database could not be established. The database may be unavailable, the host or port values may be incorrect or the private endpoint configuration may be invalid. Ensure the database is operational. Check the hostname and port are correct. Ensure the correct protocol is being used. If using a private endpoint ensure it is configured correctly.

    Any ideas, I’m at a loss.

    Thanks,
    Steve

    • thatjeffsmith Post Author

      Not sure what you mean about domains, can you go into more details?

      Also, when you’re testing SQLcl, can you confirm you’re testing the SQLcl that ships with the VS Code extension? Right-click on connection, and select ‘Open SQLcl’ – that works?

    • Steve

      Yes, I am using SQLcl that ships with the VS Code extension and that does work.

      By domains I mean network domains. We have 2 that segregate servers geographically. While in the office I can connect to any database using native SQL Developer and SQLcl that is built into the extension. No VPN connection required. But for some reason, connections to the other network domain in the extension fail, both while testing the connection in VSCode or trying to open SQL Worksheet. The only way I can connect to databases in the other domain is if I first connect to the other network domain’s VPN.

      So, there must be some sort of difference in how network connections work between native SQL Developer (and SQLcl in the extension) and the VS Code extension.

      Hope that makes more sense.

      Thanks,
      Steve

    • thatjeffsmith Post Author

      On the surface this doesn’t sound possible, yet here you are 🙂

      In VS Code, if you bump the extension logging level up to TRACE (in extension settings), and try to make the connection, can you share the output panel’s contents?

    • Steve

      I set the trace level, but where are the trace files located?

    • thatjeffsmith Post Author

      it’s VS Code extension logging, it goes directly to the ‘Output’ panel in VS Code

    • Steve

      Unfortuanately, nothing shows up in the output window when trying to login to the database in the other network domain. Just a popup window in the bottom right-hand corner showing:
      A connection with the database could not be established. The database may be unavailable, the host or port values may be incorrect or the private endpoint configuration may be invalid. Ensure the database is operational. Check the hostname and port are correct. Ensure the correct protocol is being used. If using a private endpoint ensure it is configured correctly

      The settings for trace level includes a selection for enabling logging to a file. Any idea what file it may be creating?

    • Steve

      I found the output; it is rather large. Can I email it to you?

      Thanks,
      Steve

    • Steve

      This is probably the part of the trace you are interested in:

      Jan 17, 2025 9:58:13 AM oracle.net.ns.NSProtocolNIO handleConnectPacketResponse
      INFO: Got Resend, SessionTraceId = 2DB155F5
      Jan 17, 2025 9:58:13 AM oracle.net.ns.NSProtocolNIO handleAcceptPacket
      INFO: Connection established. Cleared conn strategy stack
      Jan 17, 2025 9:58:13 AM oracle.net.ns.NSProtocolNIO sendMarker
      INFO: Sending break marker, SessionTraceId = 2DB155F5
      Jan 17, 2025 9:58:13 AM oracle.jdbc.driver.PhysicalConnection connect
      INFO: throwing
      java.sql.SQLRecoverableException: ORA-17002: I/O error: Connection reset, Authentication lapse 0 ms.

    • Steve

      Problem solved! I had a discussion with our network guy, and it turns out that the firewall was blocking the connection because it was not only checking the port but was also checking for an Oracle signature from the software. This was removed and now I connect.

      I’ll leave it to you to decide whether this is something that can be updated in the VS Code extension.

      Thanks,
      Steve

  5. Pak Reply

    Hi Jeff,

    I am trying to setup ORDS api logging using prehook functionality. From all the pages I have come across, it is referring to https://www.jmjcloud.com/blog/using-the-ords-prehook-function which is returning 404 (even on the homepage).

    From what I gathered, prehook call a procedure with no arguments. In my case, I am trying to include the :current_user when inserting into my logging table. When I compile the procedure, I am getting
    PLS-00049: bad bind variable ‘CURRENT_USER’

    My sample proc is
    create or replace procedure pre_hook_ins_api_log as
    begin
    insert into api_log
    ( current_user
    , request_method
    , ords_path, query_string, headers
    , remote_host
    )
    values (
    :current_user
    , owa_util.get_cgi_env(‘REQUEST_METHOD’)
    , owa_util.get_cgi_env(‘SCRIPT_NAME’) || owa_util.get_cgi_env(‘PATH_INFO’), owa_util.get_cgi_env(‘QUERY_STRING’)
    , owa_util.get_cgi_env(‘X-MY-HEADER’)
    , owa_util.get_cgi_env(‘REMOTE_ADDR’)
    );
    return true;
    exception
    when others then
    return false;
    end;

    Can you provide some guidance how to setup my proc?

    • Pak

      Hi Jeff,

      Thanks for the quick reply.

      I changed it to a function but it is still getting PLS-00049: bad bind variable ‘CURRENT_USER’

      create or replace function pre_hook_ins_api_log
      return bool
      as
      begin
      insert into api_log
      ( current_user
      , request_method
      , ords_path, query_string, headers
      , remote_host
      )
      values (
      :current_user
      , owa_util.get_cgi_env(‘REQUEST_METHOD’)
      , owa_util.get_cgi_env(‘SCRIPT_NAME’) || owa_util.get_cgi_env(‘PATH_INFO’), owa_util.get_cgi_env(‘QUERY_STRING’)
      , owa_util.get_cgi_env(‘X-MY-HEADER’)
      , owa_util.get_cgi_env(‘REMOTE_ADDR’)
      );
      return true;
      exception
      when others then
      return false;
      end;

  6. Salvi Reply

    Hi Jeff
    I work for a finance company and we use SQL Developer. We are wondering if Oracle will continue to develop this tool in the future or if you will only push the VSCode plugin. Is there a binding date for the end-of-life of SQL Developer?
    Thank you very much for your feedback.
    Salvi

    • thatjeffsmith Post Author

      The Oracle SQL Developer tool will continue…but in the form of a VS Code extension. The delivery vehicle is changing from Java Swing to VS Code. The licensing remains the same. It just gets better.

      The current desktop tool will stick around until we have sufficiently moved over all the features. There is no binding EOL date. Each release gets an official 18mos support window – that’s binding.

Write A Comment