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

  1. Ted L in FL Reply

    Hey Jeff,

    SQLcl is great. In the Statusbar, is there a way to show if we are spooling?

    Thank you!
    Ted L.

  2. Amin Reply

    Using Oracle FREE 23.3
    SQLCL v 25.1

    SQL> datapump export
    Initiating DATA PUMP
    Database Time Zone: VERSION:43 CON_ID:0
    Log Location: DATA_PUMP_DIR:ESQL_6684.LOG
    Additional Information: ORA-04036: PGA memory used by the instance or PDB exceeds PGA_AGGREGATE_LIMIT.
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3545
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4168
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4757
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 6245
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 7674
    ORA-06512: at line 48

    Am I toast?

    • Amin

      Can we change the settings in FREE23ai?
      I tried SGA 1448 and PGA 600. Does not accept.

    • thatjeffsmith Post Author

      Do you need help finding the database docs?

      I know that SGA for Free is limited to a total of 2GB for both SGA and PGA – so adjust accordingly.

  3. DAVE Reply

    I’ve been using VS Code / SQL Developer for several months now and love it! However, there are a few things missing that make me open up the old SQL Developer occasionally. I figured I would post them here for future enhancements. or if already available, someone can clue me in.

    1. Copying rows, columns or group of cells in the results grid.
    2. Right click menu option to start an explain plan.
    3. When using CTRL-END to get to the bottom of a result set, it buffers a 100 rows and then you have to repeatedly hit CTRL-END to eventually get to the bottom.

    Thanks for all the work you guys have put into this. I’ll keep using and look forward to future updates and enhancements!

    • thatjeffsmith Post Author

      You can copy rows today
      1. ctrl/cmd click the row number to the far left of the grid to select 1 or more rows
      2. it’s mapped to a keyboard shortcut and has a toolbar button – if you want it on a right click, access via the Command Palette
      3. you want to … force all the rows back, for…what reason?

      It would be super helpful if you could leave a review on the Marketplace, Dave! That helps us attract other users so they can love it, too 🙂

    • DAVE

      Great, I didn’t realize you could copy rows that way. Having that ability on the columns would be “nice to have”.
      For the cntr-end thing, it’s really just a lazy way to get row counts for small returns. Not a biggy, just a habit I got into after years of using SQL Developer.

      I’ll certainly post a review.

      Thanks!

    • thatjeffsmith Post Author

      Column/region copy/paste is being worked on.

      Right-click ‘count rows’ will usually be faster than fetching down the entire resultset – and will definitely incur fewer resources client and db server side.

  4. Wayne Adams Reply

    Jeff, got the javascript working after installing graalvm 17 and it seems to work, but I was trying to test error handling and I’m not seeing it report errors?

    I created a script with a select statement with invalid table name. And it runs as if there are no errors? Not sure what I’m doing wrong and can’t find any clear documentation on it (I pulled my error checks below from various blog sources).

    SQL> script
    2 var binds = {};
    3
    4 var objects2 = util.executeReturnList(
    5 “SELECT ‘x’ col1, ‘y’ col2 from dBADual”
    6 ,binds);
    7
    8 if(objects2) {
    9 ctx.write(“successful\n”);
    10 } else {
    11 ctx.write(“Error :(\n”);
    12 var err = util.getLastException();
    13 ctx.write(“\nERROR:” + err + “\n”);
    14 }
    15 if (ctx.getProperty(“sqlcl.error”)) {
    16 throw new Error(ctx.getProperty(“sqlcl.last.err.message”));
    17 }
    18 for (i = 0; i < objects2.length; i++) {
    19 ctx.write("cols: " + objects2[i].COL1 + "\n");
    20 }
    21 ctx.write("invalid stmt executed\n\n");
    22* /
    successful
    invalid stmt executed

  5. Liz Reply

    Can we use a more recent version of Tomcat than v9 to deploy yet?
    Tomcat 11 now out.

    • thatjeffsmith Post Author

      You can but you’ll need to sort Jakarta issues, and we don’t technically support it.

      Tomcat 9 is still under support.

  6. Wayne Adams Reply

    Jeff, do you have any instructions for getting sqlcl to work with Graalvm 21 (jdk 21?) with Javascript support? All the existing documentation I can find talks about using “gu” to install the js support for a graalvm installation, but “gu” is no longer shipped with graalvm 21. And i can’t make sense of the instructions on the graalvm js github site for adding it and can’t seem to find any post where others have documented the process.

    • thatjeffsmith Post Author

      External/Just in Time plsql debugging where you start a local listener and attach to another session, not YET implemented in our VS Code extension.

  8. Jayson Garrett Reply

    Hi Jeff,

    I’ve been scouring the web for a definitive way to do this, and have found varying things. I was hoping you could clarify how to create a tab-delimited file using SQLcl. I’ve seen mentions of using COLSEP CHR(9), an old post where you mentioned JS (but what JS?), etc. Is there a current way to do this with SET SQLFORMAT (or any other means) in SQLcl? Currently, I’m using 22.4, as the machine I’m using it on is required to have Oracle Client 19c only. Thanks for any help you can give!

    • thatjeffsmith Post Author

      You need to upgrade to 25.1

      then you can do this –


      --wrap this with a spool file.tsv and spool off
      SQL> set sqlformat delimited \t ' '
      SQL> select * from countries fetch first 5 rows only;
      'COUNTRY_ID' 'COUNTRY_NAME' 'REGION_ID'
      'AR' 'Argentina' 20
      'AU' 'Australia' 40
      'BE' 'Belgium' 10
      'BR' 'Brazil' 20
      'CA' 'Canada' 20

  9. Jack H Reply

    Hey Jeff – do you know if there are any plans to add functionality in the new VS Code extension to give me a notification when I log into one of my connections that my password is going to expire soon? It used to happen in the old SQL Developer Java version which was useful so I knew to change it before it expired, since it wasn’t possible (as far as i know) to change the password after it expires through the SQL Developer app and I had to go to through the SQLPlus (now SQLcl CLI tool) to change the password.

    Many thanks,
    Jack

    • thatjeffsmith Post Author

      If we’re not surfacing that message, it’s a bug. I’ll look into it.

      OK, I looked into it, it’s a bug! Will file and sort this for you.

      Thanks for your report!

  10. John Thomas Reply

    We have our own tailored data dictionary viewer, implemented using node.JS.

    Thinking it might be a nice feature if we could add a button to call out to an API that generates a model diagram, as SQL Developer does.

    • thatjeffsmith Post Author

      Theoretically yes, it’s there for SQL Developer Web –
      but
      1 – requires ORDS
      2 – assumes dictionary is in an oracle database

  11. John Thomas Reply

    Jeff, is there any prospect of an API to create models the way SQL Developer does when you click on a table and it returns the immediate neighbours?

    • thatjeffsmith Post Author

      An API to create a model? How exactly would you use this API, or what would the expected output look like?

Write A Comment