Ask A Question

5,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!

6,930 Comments

  1. Avatar

    Jeff – is there a way to disable the bouncy social media icons in the lower right ? Your articles are valuable but those bouncing-as-you-scroll social icons are just just incredibly distracting. I think the intention was good but the practical effect is that I for one avoid your articles for this very bouncy reason because I mouse-scroll a lot.

    • thatjeffsmith

      You’re the 3rd to complain, so I’ll nuke it now.

      Thanks for taking the time to share this.

  2. Avatar

    Hi Jeff,
    A question about silent installation with TNS in ORDS 22.3
    When specifying
    –db-hostname
    –db-port
    –db-servicename
    installation completes, but omitting those and specifying instead
    –db-tns-alias
    –db-tns-dir
    i’m getting these errors:

    ERROR: Cannot have different connection types specified on the command-line.

    ERROR: Invalid option(s) specified on the command-line.

    Any idea?
    Pre-22.x we would need to specify in the parameterfile:
    db.connectionType=tns
    But there does not seem to be an equivalent in 22.3; at least it is not in the documentation and also none of the examples use tns.

  3. Avatar

    Hi Jeff,
    I’m in the process of assessment sqlcl – liquibase features for our use-case, and it looks promising
    But I have problems with queue tables, as I can’t get it to work with:

    — logged as appropriate user
    lb generate-object -obt AQ_QUEUE_TABLE -obn
    gives me: “Unable to retrieve object information from the database. Please check object name and object type”
    Generation of XML for queue itself runs fine.
    The version of sqlcl is latest at this time,
    Oracle SQLDeveloper Command-Line (SQLcl) version: 22.3.1.0 build: 22.3.1.285.1828

    I tried dbms_metadata directly and it works as expected:
    dbms_metadata.get_ddl(OBJECT_TYPE => ‘AQ_QUEUE_TABLE’, NAME => , SCHEMA => );

    Regards,
    Darko.

    • thatjeffsmith

      You forgot to say ‘table’ after -obt

      SQL> lb generate-object -object-type table -object-name QT
      --Starting Liquibase at 09:21:37 (version 4.17.0 #0 built at 2022-11-02 21:48+0000)

      Changelog created and written out to file qt_table1.xml

      Operation completed successfully.

    • Avatar

      Well, if I create a XML for type TABLE, then it will be a regular table and not the AQ table.
      The AQ table should be created with call to
      DBMS_AQADM.CREATE_QUEUE_TABLE(…)

      For example, with
      lb generate-object -obt AQ_QUEUE -obn queue_name
      I get a xml which will be executed as DBMS_AQADM.CREATE_QUEUE, and that’s the right call.

      But with just
      lb generate-object -obt TABLE -obn qt_name
      I’ll get XML for regular table, which will be executed as create table and not DBMS_AQADM.CREATE_QUEUE_TABLE

      For that reason I tried to use -obt AQ_QUEUE_TABLE, as documentation suggests

      Thanks,
      Darko.

    • thatjeffsmith

      In our latest internal build, it seems to be working

      SQL> lb generate-object -object-type AQ_QUEUE_TABLE -object-name QT
      --Starting Liquibase at 12:11:18 (version 4.17.0 #0 built at 2022-11-02 21:48+0000)

      Changelog created and written out to file qt_aq_queue_table.xml

      Operation completed successfully.

      Now connect to a fresh env and use updatesql with the changeLog and see what the DB will do with it –
      SQL> lb update-sql -changelog-file qt_aq_queue_table.xml
      --Starting Liquibase at 12:13:51 (version 4.17.0 #0 built at 2022-11-02 21:48+0000)

      -- Loaded 1 change(s)
      -- *********************************************************************
      -- Update Database Script
      -- *********************************************************************
      -- Change Log: qt_aq_queue_table.xml
      -- Ran at: 11/15/22, 12:13 PM
      -- Against: [email protected]:oracle:thin:@dbtools-dev:2213/DB213P
      -- Liquibase version: 4.17.0
      -- *********************************************************************
      ...
      -- Changeset qt_aq_queue_table.xml::841c3e8e912f2f2a5e25d744b6730b3e148549fc::(PDBDBA)-Generated
      BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(
      Queue_table => '"QT"',
      Queue_payload_type => '"EMBEDDED_VARRAY"',
      storage_clause => ' PCTFREE 10 PCTUSED 40 INITRANS 1
      TABLESPACE "USERS" ',
      Sort_list => 'ENQ_TIME',
      Multiple_consumers => FALSE,
      Compatible => '10.0.0',
      Secure => FALSE);
      END;
      ...

      So this should be working when we push our next release.

  4. Avatar

    Hi Jeff!
    Is there a posibility in SQL Developer to configure different paths depending on the context (see examples below) or one global standard path where to open files (on the local filesystem or on an network-share) when using the File => Open-menue path?

    In our currently used SQL Developer version (22.2.0.173 Build 173.2018, Java(TM)-Plattform 11.0.15.1, Windows 10 Enterprise Version 21H2 Build 19044.2130) we determined three (?!) different behaviours when using the File => Open-menue path:
    active but new / blank SQL worksheet tab with database connection => C:\Users\[username]\AppData\Local\Temp
    active SQL worksheet tab with an opened file (with or without database connection) => the files path
    active database object tab such as table => C:\Users\[username]\AppData\Roaming\SQL Developer\system22.2.0.173.2018\o.sqldeveloper\projects

    Thanks!
    Wolfgang

  5. Avatar

    Hi Jeff, I use the drag and drop functionality a lot to open pkb and pks files in sql developer.
    However. when I open a pkb file by dragging it on a sql window connected to a staging database, compile it, save and close sql developer, then when I open sqldeveloper again, connect to a different database and then drag the same pkb file from file explorer into the sql window connected to that new connection the pkd is opened but often with the connection still pointing to the staging database (the one used when I last opened it). This especially happens when dragging multiple files into a sql window.
    Is there a setting or preference to always only use the connection of the sqlwindow I’m dragging the files into ?

    • thatjeffsmith

      We work at the same company, you can just hit me up on Slack 🙂

      But yes, disable ‘Link Stored Procedures to Files’ preference under Code Editor sections.

  6. Avatar
    Arun Rajagopalan Reply

    select *
    from (select *
    from x
    where y between 1990 and 2000
    )
    match_recognize (
    order by y
    all rows per match
    pattern (i ln u{\?\})
    define
    i as winner like ‘I%’,
    ln as length(song) >= 18,
    u as host = ‘ISS’
    );

    even with escape character it does not work on sqlcl.

    Error at Command Line : 9 Column : 20
    Error report –
    SQL Error: ORA-00911: invalid character
    00911. 00000 – “invalid character”
    *Cause: The identifier name started with an ASCII character other than a
    letter or a number. After the first character of the identifier
    name, ASCII characters are allowed including “$”, “#” and “_”.
    Identifiers enclosed in double quotation marks may contain any
    character other than a double quotation. Alternate quotation
    marks (q’#…#’) cannot use spaces, tabs, or carriage returns as
    delimiters. For all other contexts, consult the SQL Language
    Reference Manual.
    *Action: Check the Oracle identifier naming convention. If you are
    attempting to provide a password in the IDENTIFIED BY clause of
    a CREATE USER or ALTER USER statement, then it is recommended to
    always enclose the password in double quotation marks because
    characters other than the double quotation are then allowed.
    SQL> show version
    Oracle SQLDeveloper Command-Line (SQLcl) version: 22.3.1.0 build: 22.3.1.285.1828

    • Avatar
      Arun Rajagopalan

      Yes, it is working in SQL Developer but not SQLCl

      pattern (i ln u{\?\})

    • thatjeffsmith

      Weird, they both share a JDBC driver and script execution engine, and parser. What version of both?

    • Avatar
      Arun Rajagopalan

      I have tried it on SQLcl: Release 22.3 and SQL Developer Version 22.2.1.234.

  7. Avatar
    Pavel Pospíšil Reply

    Hi Jeff,

    Thanks for your site..
    I’m using version 22.2.0.173..
    I use DBMS_DEBUG (a great option) – it works quite as expected, but during the step it is not able to open the source from TYPE BODY – it just opens a dialog asking where to take the source code – It is necessary to find a text source saved somewhere. Then it works.. (for the given debug session – I have to do it again the next time I debug). Packages/procedures/functions works as expected..
    Where am I making mistake ?

    (I tried debugging in a competing product as well, and there is no such problem – normally, debugging works through the TYPE BODY..)

    Thanks
    Pavel

  8. Avatar

    Hi Jeff!
    I have an standalone ORDS install version 18.4 pointing to an 11.2.0.4 database.
    Now I want to move the ORDS_PUBLIC_USER and metadata to a new database19c.
    Do you have a process for this? What I must do on the frontend(standalone side) and the backend database side?

    Regards

    /Ulf

    • thatjeffsmith

      Do a fresh install on your 19c database.

      Then, recreate your rest APIs, privs, roles, and OAuth2 clients.

      Hopefully you have these as SQL scripts in source control.

      You should be able to extract these from your 18.x environment as SQL scripts to be replayed on the box.

      Kudos on decommissioning your 11g instance and moving to 19c!

  9. Avatar

    Hi Jeff,
    Does the info or info+ command from SQLcl have a line number option similar to “set desc linenum ON” in SQL*Plus?
    If not, may I suggest that feature for a new SQLcl release? 🙂
    Thanks!
    -John

  10. Avatar

    Hi Jeff,
    Can you use the SQL*Plus “set” commands like “set rowlimit” or “set feedback on sql_id” in SQLcl?
    If not, will they eventually be ported to a new release in the near future?
    Thanks.
    -John

    • thatjeffsmith

      No plans for
      set rowlimit — you’re the first to ask

      SQL_ID, already there!

      SQL> set feedback on SQL_ID
      SQL> select 5 from dual;

      5
      ____
      5

      1 row selected.

      SQL_ID: 3b3s1w98d2g81

    • Avatar

      Thanks Jeff!
      Confirmed: “set feedback on SQL_ID” works.

      If I may make a suggestion, please tell Oracle to add “set rowlimit” to SQLcl (or let me know how to do it). 🙂

  11. Avatar

    Hi Jeff, love your content. With the new versions completion insight does not take into account the alias provided to limit the results to that table. This was very handy in the past to find column names in that table for the select statement and is no longer usefull because of this. Is this design or a bug?

    Thank you,

    Todd

  12. Avatar
    Attie Taljaard Reply

    Hi Jeff,

    I read the article : “Linking Excel to AUTO REST Enabled Tables…with the Oracle Visual Builder Plugin”.

    Do you know if there is a sample/tutorial (VBA Script/VBA Function) on how to call a REST API in ORDS for a lookup into Oracle ORDS from a VBA script?

    A sample of calling a basic lookup from Excel VBA (VBA Script/VBA Function) into and ORDS Rest Service to just do a basic lookup of a value?

    Thanks
    Attie

  13. Avatar

    Hi Jeff,
    I have a liquibase/sqlcl issue I was hoping you could give me some pointers on how to solve –
    I have 2 ATP DBs (Always Free v19c). I am just trying to create a POC where I can gen schema from one and update the other.
    SQLcl v22.3
    JDK 11
    JRE 18.9
    Liquibase v4.17

    I can connect to my DEV DB and run ‘lb ges -split’ which works and the relevant files are output as expected with changelogs and a controller.xml file.

    I then connect to my TEST_LOW DB and run ‘lb up -chf controller.xml’ (Pointing to the controller generated for DEV db)
    and I get ‘No such host is known (test_low)’

    Not sure if I’m missing any steps – any idea what could be wrong?

    Pretty new to lb so could be something I’m missing. any help would be really appreciated/

    Lee

Write A Comment