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,644 Comments

  1. Miguel Reply

    Hey Jeff,

    Does plsql have a package or utility that can create a function/package/procedure dependency graph? We always use lucidchart for diagrams. Before we develop a feature we create a package diagram, but with development time it becomes obsolete and we never go back to it. We would like to see the relationship between functions in a simple text/diagram way.

    • thatjeffsmith Post Author

      We have all the parts/pieces to do it…it just hasn’t gotten off the drawing board into a product, yet.

  2. Nabil MESSARA Reply

    Hello Jeff,
    We use SQLDEVELOPER through Citrix. Users encounter issues when relaunching the application after exiting using the cross button. The progress bar freezes at around 10%. Is there a way to disable/hide this cross in order to force users to use ALT+F4 to close SQLDeveloper?
    Thank you very much for your support.
    Sincerely,
    Nabil

    • thatjeffsmith Post Author

      Mmmmm, I can’t think of a way, when you’re building a java swing app and it’s Windows, it just paints the windows buttons like minimize/maximize/exit.

      I would figure out why launching the app is hanging, and fix that.

      One possibility is the dtchache.xml file is being corrupted – this happens frequently when one user launches multiple instances of the app. If you delete that file at startup, i’m betting your problem goes away.

    • Rajesh Bhogadi

      Hi Jeff,

      SSH tunneling on the current version of SQL Developer doesnt work and a search on support site presents this note.
      I believe this is a pretty handy feature and appreciate if you can throw some light on this issue, whether this get fixed in future releases.

      Bug 36130399 : SSH CONNECTION FAILS JAVA.LANG.NOCLASSDEFFOUNDERROR: ORG/APACHE/SSHD/COMMON/FUTURE/CANCELOPTION

      Regards,
      Rajesh

    • thatjeffsmith Post Author

      Fall back a version or open a tunnel using putty, or we’ll have version 24.2 ready in a few weeks with this fixed.

  3. Tim Reply

    Hi Jeff

    I’m looking at ORDS 24.2 to replace ORDS 21.4 with multiple databases with ORDS. I really like the “new” ords syntax, separating out the config, etc.

    Sadly, I still have some old databases 11.2.0.4 and 12.1.0.2 remaining as well as supported 19c.
    All the ORDS documentation says that you need a supported database (as it likely always has) and I get that. But, if I look on the Certification tab in My Oracie Support and look at the certifications for “Oracle REST Data Services” (version 24.1 is the latest they have), it shows as “Certified” with Oracle Database 11.2.0.4 (for example).

    I appreciate that you are NOT support, and I have logged with support…. who say they can’t even look at whether the information being provided to me on the certifications tab is wrong because I’m asking about a database product that is out of support (i.e. 11.2.0.4).

    If it can work, I’d like it to, as the ORDS upgrade cycle is shorter and simpler while we also need to tackle database upgrade. if it cannot work, I’d like that to be clear from My Oracle Support.
    It would be even better if the upgrade attempt bombed out with a message saying that it isn’t going to work.

    If I install or upgrade ORDS in an 11.2.0.4 database (ords install)… no errors and it appears to have worked.
    After ords serve… I get errors in the log

    WARNING Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: Failed to create initialPoolSize connections while starting a pool: oracle.ucp.UniversalConnectionPoolException: Failed to create initialPoolSize connections while starting a pool: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12504, TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
    (CONNECTION_ID=Xj+pNKFjSdmaCzakgpsYZg==)

    The tnslsnr log on the “target” database of the database pooi shows some ok connections then some malformed ones (I’ve maybe garbled it worse by chaning the service name to and ip address to

    15-JUL-2024 12:31:42 * (CONNECT_DATA=(CID=(PROGRAM=Oracle REST Data Services)(HOST=__jdbc__)(USER=oradev))(SERVICE_NAME=)(CONNECTION_ID=qmMT5fvdSd6yDLlM8ks/uQ==)) * (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=30254)) * establish * * 0
    15-JUL-2024 12:31:42 * (CONNECT_DATA=(CID=(PROGRAM=Oracle REST Data Services)(HOST=__jdbc__)(USER=oradev))(SERVICE_NAME=)(CONNECTION_ID=VihQCks8SuytpQfy+NUofg==)) * establish * 12504
    TNS-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

    ORDS 24.2 seems to work with database 12.1.0.2.

    I’d appreciate if you do read this if you can confirm that there is a technical reason why this cannot work with 11.2.0.4 and what that is, and also if there is any way to get Oracle Support to have correct certification information.

    Thanks
    Tim

    • thatjeffsmith Post Author

      We still support 11gR2, unfortunately. It should work. My internal 11gR2 testing environment is working on latest ORDS.

  4. Paul Reply

    Hi Jeff,
    Quick Dev question. Spent the day trying to get to grips with Java Stored Procedures on an ATP instance in OCI. Worked my way through the official docs and got a number of examples working successfully which was good.
    1. What is the recommended (and easiest setup) IDE for developing Java on an ATP DB. Connecting to OCI seems tricky with a cloud configuration file. Is JDeveloper the way to go or is that old now? Had a look at the database actions in the OCI ATP dash, but couldn’t see anything. I have been loading java files from sql dev, but that assumes they are bug free before uploading. I have found various folks on forums all giving conflicting advice: what do you think would be my most painless option?
    2. Do you know of any good walk through tutorials? I had a look through your archives (and Tm on Oracle Base) but couldn’t spot any.
    Thanks as always!

    • thatjeffsmith Post Author

      I don’t know of anyone actually using java stored procedures, in or out of Autonomous DB, but I will try to get you some advice.

  6. Jan Reply

    Hi Jeff,

    I have searched the internet after how to debug in vscode with the SQL Developer extension without any result.

    Is it possible to debug PL/SQL code in vscode with the SQL Developer extension?

    Wonders,
    Jan

  7. Scott M Reply

    love sql dev over toad, but with the introduction of windows clipboard history i find i cannot use the key board (arrow up/down, nor enter) after i activate the history (win-v brings up the windows clipboard history display, but the up/down arrows and enter keys don’t work, i have to use the mouse)

    any idea why this is, or if aware is there any work around or fix coming

    thank you

    • thatjeffsmith Post Author

      Tell me more about this

      but with the introduction of windows clipboard history

      What do you mean by this statement.

    • scott

      gonna have to be quick, the page refreshed last time and i lost what i had written

      since win 10 there is a clipboard history that you can turn on, when on windows saves your last 20 or so ctl-c or ctl-x entries, you use win-v to see the list, arrow keys to scroll through the list, enter to select one to paste

      when running sql dev, when the history is displayed the arrow keys and enter don’t work (so i have to use the mouse)

      sql dev isn’t the only program that shows this action, but it is the one i use the most

    • thatjeffsmith Post Author

      Best guess you need to remap some keyboard shortcuts.

      I’ll be back Monday to have a look.

    • thatjeffsmith Post Author

      No, those built-in features of a SQL based REST API all go away when you move into the ‘code it yourself with plsql’

      But, you COULD add them back in, as in, you parse the query parameters in your plsql and update your dynamic plsql to accommodate those requests.

      So it’s possible, but it’s not automatic.

      Also, try asking your question on the page related to the topic if possible, it’s helps others follow along.

  9. Steeve T Reply

    Greetings!
    I started using SQLcl recently thanks for the videos, really helpful.
    Instinctively I tried the following sequence of commands, the “help edit” seems to infer that I could set _EDITOR=”inline” to be able to edit the sqlbuffer with the inline editor. however it doesn’t work how I thought i would.
    It there a reason why I can’t edit the buffer using he inline editing when I bring back a SQL query with the ‘history 10′ command?
    It does bring the query back into the buffer but I can’ edit it using inline editor.
    VI and notepad can be used yes. but then I have to use ‘/’ to execute the query, which doesn’t put the sqlbuffer in the history, only the ‘/’…
    I would love to see this in upcoming versions of SQLcl

    • thatjeffsmith Post Author

      I think that’s just a deficiency in the History command.

      I think we put it in the buffer so you could quickly run it via ‘/’ vs using ctrl+R…but if you DO need to edit something it becomes tedious as you’ve explained.

      I’ll file an ER for you, thanks for the feedback!

  10. Miguel Escamilla Reply

    Hi Jeff,

    Do you know when 23ai or any of 23 versions will be available in OCI? 19c is the only version available for paid instances. I really need to use javascript in the backend… I think!

    • Miguel Escamilla

      Autonomous database instances. Forgot to add that part. Thanks in advance!

    • thatjeffsmith Post Author

      I know exactly when, but I’m not allowed to tell you. You won’t be waiting much longer.

  11. Dave Reply

    I watched a few videos on YouTube about Github Copilot for database and I’m very interested to know whether anyone has tried using Copilot and SQL Developer for VS Code and can you write a blog post on that about the experience with PLSQL syntax?

    • thatjeffsmith Post Author

      No because talking about AI and someone else’s AI is not easy right now.

      But you can expect more in this area, stay tuned.

  12. Dave Reply

    Is there a target timeline / release version YY.1 when SQLDEV for VS Code – > will be feature complete?

    • thatjeffsmith Post Author

      It took us 18 years to build SQL Developer. We’re hoping to have an Extension that’s suitable to replace the old SQLDev in 1.5 years, and we’re 5 months in.

      Probably gonna be close to 2-2.5 years, esp when you factor in Data Modeler.

      Is there a particular feature or set of features you’re keen to see sooner vs later?

    • Dave

      I plan to stick with old SQLDEV until the debugger is available.

  13. Paul Reply

    Trying to get to grips with ORDS: confused over something after reading every Post I can find over the last couple of days. I want to use ORDS to build a RESTful api layer consumed by multiple clients: mobile clients, SaaS clients etc
    I have a read all of your posts I can find on ORDS and got most of them working in my environment (ATP OCI). I was reading some of the posts by Tim Hall on Oracle Base. Has has examples of procedures using APEX_JSON.write to return content. If I use this: I see the returning content in the ORDS Admin panel when i log into OCI, but nothing in the browser when I run the end point. If i put an explicit htp.p in the procedure, I see the content in the browser, but errors in the admin panel. I see examples from Steve Muench using in out parameters and printing the response in the handler like this:
    p_source => q'[
    declare
    l_response json_object_t;
    begin
    generic_service(p_request => json_object_t(:body),
    p_response => l_response);
    owa_util.mime_header (‘application/json’, true);
    — Show example of modifying the response from the invoked
    — json-in-json-out method before returning.
    l_response.put(‘method-used-was’,’post’);
    htp.p(l_response.stringify);
    END;
    ]’,
    (not sure what the q is for at the beginning p_source parameter)
    My current preference is to use PL/SQL JSON datatypes to create and move the json around with, a bit like the example above.
    Any tips on the best way of doing this. Procedures/Functions : APEX_JSON.write/ htp.p : in-out parameters : simple/complex handlers. Are any methods more optimised than others at scale?
    Why do some methods show in the browser when running the endpoints and some do not? Is there an implicit conversion for APEX which does not work when called from the browser using the APEX apis?
    Final point, in an enterprise setting, would you still use the api gateway in OCI, or just expose the ORDS end points straight to mobile and SaaS clients.
    Many Thanks

  14. Ryan Reply

    Hey Jeff!

    Bear with me as I’m inexperienced with Java… I have a connection to an Oracle DB in SQL Developer which uses a custom JDBC driver. The driver is a JAR file I imported using the “Third Party JDBC drivers” screen. Is it possible to use this driver with the VS Code extension? (I see that “Custom JDBC” is an option for Connection Type, but it wants a URL and despite searching I have no idea if I can use this field to somehow refer to a file on my local machine…)

    • Ryan

      Whoops, that’s a typo… it’s a NON-oracle DB

    • Ryan

      More specifically… it’s an IBM Db2 DB, and so my JDBC drivers are the standard db2jcc4.jar & db2jcc_license_cisuz.jar

    • Ryan

      Alright, I’m learning… I found IBM documentation for DB2, which says the URL should be of the format jdbc:db2://server:port/dbname. However when I try that in Oracle Developer extension (using “Custom JDPC URL”) I get the error “No connection provider found”

      I AM able to connect to this DB programmatically (in Python, not Java) using the above syntax for connection URL–when I do that, I also provide “com.ibm.db2.jcc.DB2Driver” as the value of “Java class name” and don’t need to provide the JDBC driver JAR files.
      Maybe I need to specify some detail(s) under the Advanced tab of Create Connection?

      Sorry if this is a simple question; I am new to all of this and have been searching the web for as much info as I can find.

    • Ryan

      Well that just makes too much sense. Guess I got tunnel vision on migrating all DB connections I had in OSD to the VS Code extension. Thank you Jeff!

  15. Jaruwan Reply

    Hello Jeff,

    I hope you’re doing well. For the last few months, I have been trying to authenticate database connection via SQL Developer to Azure AD using latest SQL Developer 23.1.1 and many combinations of JDBCs version. However, I haven’t been lucky enough when the similar approach works on SQLPLUS. Do you have any pointers or suggestions to help resolve this problem?

    Thank you.
    Jaruwan N.

  16. Nate Reply

    Hello Smith,

    I hope you’re doing well.

    Lately, my APEX_PUBLIC_USER account has been getting locked every morning when I arrive at work. Initially, I thought this behavior was due to the FAILED_LOGIN_ATTEMPTS setting in the profile. To address this, I created a new profile for APEX_PUBLIC_USER and ORDS_PUBLIC_USER with unlimited attempts and an unlimited PASSWORD_LOCK_TIME.

    However, the issue persists.

    Do you have any pointers or suggestions to help resolve this problem?

    Thank you.
    Nate

    • thatjeffsmith Post Author

      That’s a really bad idea, you’ve just made it easier to compromise your database, especially if someone has a weak password.

      Go change your profiles back.

    • Nate

      Hello Jeff,
      I have changed the profile. Now, back to my main issue.
      Do you have any suggestion for me?
      Thank you.

    • thatjeffsmith Post Author

      There are several things that could cause an account to go inactive/locked. You’ll need to investigate a few scenarios. Passwords expired, account locked due to bad login attempts, someone manually editing the user…

  17. MJS Reply

    Hi Jeff,

    If I recall correctly, you mentioned something about 2FA for SQLDev Web (Database Actions) in the past. Is that something which is available already?

    Thanks!

  18. Miguel Reply

    Hey Jeff,

    Can I pass a custom sorting/ordering function to the APEX Interactive Grid’s control break ? The default ASC and DESC options are not what I need. I have a list of items. Each item belongs to a category. The control break works great to group them and show the header as the first row of each group. However, it orders the categories/groups/control breaks in desc or asc order only. But i need it to be a custom order. Each item has an index. I dont know how to get the control break to follow the order that I want.

    Thanks in advance,

    • thatjeffsmith Post Author

      That’s a great question, but it’s not in my wheelhouse. You can find the answer here, or someone that will know the answer if you ask a new question assuming Search comes up empty.

Write A Comment