Ask A Question

4.5+ million 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,482 Comments

  1. Avatar
    Mark Hawker Reply

    Hi Jeff,

    I have a use case where I would like to suppress generation of an index object when generating DDL for a specific physical model of a relational model. Is this possible?

    Many thanks,
    Mark.

  2. Avatar
    Alil Adamov Reply

    Hello Jeff

    I was being almost fully satisfied user of SQL Developer for a long time. It is really great tool. Thank you.

    Recently I changed my work and now do more PL/SQL stuff.
    Some PL/SQL packages I work with are rather big. And I mentioned code completion insight completely not working at these packages.
    Not even trying to do single query to database;
    By some experiments figured that there is some hard coded limit based on lexical elements of script, some what 10000.
    If I comment some code it start working. Even being a little lower than 10000 it work fast so it is not performance issue (not timeout etc.). I use latest 21.2.1 checked also old 17.4 and seen same limit there.

    So my question is this completion insight limit based on lexical elements adjustable (may be some property for AddVMOption)?
    It would be great if so. Any way I think current limit is too low.

    PS. If needed I can post simple script generated package script where the limit is obvious.

    Regards

    • thatjeffsmith

      Tools > Preferences > Code Editor > Parser > Parser Threshold

      It’s based lexical tokens, is more intense, and is set to switch over to the powerful parsing algorithm (less features) at 15000.

    • Avatar
      Alil Adamov

      >Tools > Preferences > Code Editor > Parser > Parser Threshold

      Thank you for respond. Yes this parameter affects. If it is low other parser specific things like Code Outline also won’t work.
      I have increased it to 150000, and Code outline is correctly formed, even on huge scripts.
      But Code Complete Insight seems to be limited by other limit threshold as well. If Parser Threshold is greater than 10000 then real show stopper come from other place.

  3. Avatar
    Michael Johnston Reply

    Could you please recommend a way for me to find people who I could pay to provide me 1×1 coaching for TOAD for Microsoft SQL Server? (I know the basics of programming)

    • thatjeffsmith

      Sorry, I haven’t worked with Toad or SQL Server for 10 years.

      My advice would be to exhaust your free resources first. Follow the #sqlhelp hashtag on Twitter for some ideas.

  4. Avatar
    Mark Hawker Reply

    Hi Jeff,

    In the latest version of SQL Data Modeler (21.2 and a number of previous versions), I am unable to create or modify column “Comments in RDBMS”, “Comments” or “Notes” on any view definition in Relational Models. The input box is available for all three and not read only, but when the Apply or OK button is pressed, the comments are not saved.

    Is this expected behaviour or a bug? If the latter, please could this be fixed.

    Thanks in advance,
    Mark.

    • thatjeffsmith

      Definitely a bug…or at least, that should work.

      It’s working for me – do you see any ominous messages/warnings in the Log panel?

    • Avatar
      Mark Hawker

      Hi Jeff,

      Thanks for the quick reply.

      Unfortunately it isn’t working for me in version 21.2.0.183.1957 and it hasn’t for the last two versions I have tested. Steps to re-create:

      1) Create a new definition view in the relational model.
      2) Using the query functionality, get one column into the view from either another view or a table. Click apply.
      3) Open the view and navigate to columns.
      4) Highlight one of the columns. Note that if the table/view column referenced by this view already had comments, this will be copied over.
      5) Modify the “Comments in RDBMS” or “Comments” or “Notes”. Once changed, click the Apply button and the changes are lost.

    • Avatar
      Mark Hawker

      I have two error messages in the logging page. I have no idea whether these are related to the issue:

      sequence=166
      Elapsed =12
      Source = oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1
      Message = null at com.oracle.jdeveloper.nbwindowsystem.NbEditorContainer.getURL(NbEditorContainer.java:1019)

    • thatjeffsmith

      See View > External Log for the entire log, and share that back if you can, or open a thread on the Modeler Forums.

    • Avatar
      Mark Hawker

      Hi Jeff,

      There was nothing in the external log.
      I am happy to send screenshots of the issue to your e-mail address if that helps?

      Many thanks,
      Mark.

    • thatjeffsmith

      Not really. I would start troubleshooting, does this happen for any design, or just one particular one? What OS, Java, SQLDevDM versions are you using? Are other changes persisting? Are files corrupted/read only? That kind of stuff.

    • Avatar
      Mark Hawker

      Hi,

      SQL DM version: 21.2.0.183 Build 183.1957
      OS: Win 10 Enterprise Build 19041
      Java: 1.8.0_291

      All attributes in the View Properties except Columns -> Comments in RDBMS seem to be editable and get persisted as expected.

      I have checked all the XML files that make up the model and all are writeable. I even found the XML file for the view definition in question and modified the tag within the <viewElement tag outside of the modeling tool. After saving the file and reloading the model, the modified value from the XML file is reflected in the UI.

      However, any attempt to further modify this value through the UI results in the modification being lost as per my original explanation of the issue.

      I hope this helps to replicate.

      Many thanks
      Mark.

    • Avatar
      Mark Hawker

      Hi Jeff,

      Did you get chance to try my scenario?

      Many thanks,
      Mark.

    • thatjeffsmith

      Just now, and drumroll…BUG! I see this in my log –
      SEVERE 2033 0 oracle.dbtools.raptor.controls.grid.DefaultResultSetTableCellRenderer oracle.dbtools.raptor.utils.DataTypesUtil.stringValue(DataTypesUtil.java:406)

      I’ll log this for you, thanks for sticking with me on this one!

  5. Avatar
    Norbert Kiesel Reply

    Where do I find documentation on CopyFormatter API? Concrete question is: I like the “card.js” script to show individual rows from a query result, but I would like to include the column names for every value. Is there a way to do this?

  6. Avatar
    Stan Hartin Reply

    Can SQL Developer 20 graphically model a deep UDT hierarchy?

    create type grandparent as object;
    create type parent under grandparent;
    create type child under parent;

    What are the steps to get SQL Developer to show this and other UDTs graphically?

    Thanks!

  7. Avatar

    Jeff,

    I still have trouble to validate my ORDS which connects to a secured Oracle DB (19c).

    When you say “get the JDBC driver properly going OR you can use an OCI (thick)..”

    Do you have any links I can follow to have this setup?

    Once setup do I use the same command below or need extract parameter to pickup the required wallet?
    java -jar ords.war validate

    • thatjeffsmith

      It’s not a simple answer – you can either get ORDS to use a thick connection, and so it goes through the same path to the database as SQL*Plus does – so you setup your wallet there, ands ORDS to tags onto it.

      Or you go through the process of getting a custom JDBC URL going to pass everything over required for the secured database connection.

      [email protected]:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=something.com))(connect_data=(service_name=dbservicename))(security=(ssl_server_cert_dn=”CN=cert_info_here”))) UNKNOWN — you can look at the JDBC Oracle docs for more info on this.

    • Avatar

      In SQLCL, can you somehow reference UNIX variables like $ORACLE_HOME?

    • Avatar
      Ken Kline

      Oracle support says they do not support Tomcat, is this Tomcat error applicable to using ords of TCPS?
      Database is on Linux cloud VM, App Server/ORDS/Tomcat on Windows

      04-Sep-2021 23:18:22.318 INFO [main] org.apache.catalina.core.AprLifecycleListener.lifecycleEvent The Apache Tomcat Native library which allows using OpenSSL was not found on the java.library.path: [C:\Tomcat 9.0\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Program Files\Java\jre1.8.0_301\bin;C:\app\oracle\product\19.0.0\client_1\bin;C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\Windows\system32;C:\Windows;C:ge\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\OpenSSH\;C:\Windows\ServiceProfiles\LocalService\AppData\Local\Microsoft\WindowsApps;.]
      04-Sep-2021 23:18:23.430 INFO [main] org.apache.coyote.AbstractProtocol.init Initializing ProtocolHandler [“http-nio-8080”]

      here is my defaults.xml changes

      default
      customurl
      jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.0.0.146)(PORT = 2484))(ADDRESS = (PROTOCOL = TCPS)(HOST = 10.0.0.146)(PORT = 2484))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.bbb.xxx.yyy.com)) (SECURITY = (MY_WALLET_DIRECTORY =c:\app\oracle\wallet)))

  8. Avatar

    Hi Jeff
    How do you avoid SqlDeveoper to float all over the place when using Spaces on Mac ??
    regardless of what I do SqlDeveloper pops up on any Desktop that I have defined….
    Any way to avoid this ?

    • thatjeffsmith

      No idea, I only have one monitor for my Mac Mini – suggest pinging @krisrice on Twitter or on his blog krisrice.io

  9. Avatar

    Hi Jeff,
    We were Using sqldeveloper with Kerberos authentication. How can we enable it for sqldeveloper web?
    Thank you and have a nice day.

    • thatjeffsmith

      As of today, you cannot, it’s limited to database user authentication. There are plans in the works to extend availability by proxy users, Cloud Identity/AD (IDCS), and more.

  10. Avatar

    Hi Jeff,
    I have trouble to validate ORDS to a db requires TCPS connection. Running the ORDS is fine but when I am trying to validate it using the following methods got different errors:

    METHOD 1: (seems like ORDS don’t know where the wallet is, not sure how to provide the wallet???)
    C:\oracle\apexuser\ORDS>java -jar ords.war validate
    Enter the name of the database server [localhost]:
    Enter the database listen port [1521]:24841
    Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
    Enter the database SID [xe]:DCPAMPCE
    Requires to login with administrator privileges to verify Oracle REST Data Services schema.

    Enter the administrator username:teldbadmin
    Enter the database password for teldbadmin:
    Confirm password:
    Aug 25, 2021 10:11:29 AM oracle.dbtools.rt.config.setup.SchemaSetup verifyDBAUserConnection
    WARNING: Failed to connect to user teldbadmin jdbc:oracle:thin:@:24841:DCPAMPCE
    IO Error: Got minus one from a read call

    METHOD 2: ( already provide the db connection details in apex_pu.xml, apex.xml, apex_al.xml)
    C:\oracle\apexuser\ORDS>java -jar ords.war validate –database apex
    Requires to login with administrator privileges to verify Oracle REST Data Services schema.

    Enter the administrator username:teldbadmin
    Enter the database password for teldbadmin:
    Confirm password:
    Aug 25, 2021 11:02:45 AM oracle.dbtools.rt.config.setup.SchemaSetup verifyDBAUserConnection
    WARNING: Failed to connect to user teldbadmin jdbc:oracle:thin:@null:null:null
    IO Error: could not resolve the connect identifier “null:null:null”

    • thatjeffsmith

      So you’re wanting to get ORDS’ connection pools to the database to be encrypted a la secure sqlnet/SSL.

      You’ll need to get the JDBC driver properly going OR you can use an OCI (thick) connection, which will tie into an existing $ORACLE_HOME, where the wallets, etc are configured. It doesn’t sound like either of those things are happening here.

    • Avatar

      Jeff,

      “get the JDBC driver properly going OR you can use an OCI (thick)..”

      Do you have any links I can follow to have this setup?

      Once setup do I use the same command below or need extract parameter to pickup the required wallet?
      java -jar ords.war validate

  11. Avatar
    Gustavo Almeida Reply

    Hi Jeff,
    Is there a way to remove the record count from a query result in SQLcl?
    I know I can remove the columns header with SET HEADINGS OFF.

    Also, if one want to know what each of the 72 environment variables accepted by the SET command do, where is the best place to go?

    • thatjeffsmith

      [email protected]> set feedback off
      [email protected]> select * from locations
      2* fetch first 5 rows only;

      LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID
      ______________ __________________________ ______________ ____________ ___________________ _____________
      1000 1297 Via Cola di Rie 00989 Roma IT
      1100 93091 Calle della Testa 10934 Venice IT
      1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP
      1300 9450 Kamiya-cho 6823 Hiroshima JP
      1400 2014 Jabberwocky Rd 26192 Southlake Texas US
      [email protected]>

      There’s probably more than 72.

      show all or show all+, and then some/most of those should have help text avail, like

      [email protected]> help set sqlformat
      SET SQLFORMAT
      SET SQLFORMAT { default,csv,html,xml,json,fixed,insert,loader,delimited,ansiconsole}

      default : SQL*PLUS style formatting
      csv : comma separated and string enclosed with "
      html : html tabular format
      xml : xml format of /results/rows/column/*
      json : json format matching ORDS Collection Format
      json-formatted : json format matching ORDS Collection Format and pretty printed
      fixed : fixed width
      insert : generates insert statements from sql results
      Example
      Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);

      loader : pipe (|) delimited enclosed with "
      Example:
      7369|"SMITH"|"CLERK"|7902|"1980-12-17 00:00:00"|800||20|5555555555554444|

      delimited : CSV format with optional separator , left, and right enclosure
      set sqlformat delimited [separator] [left enclosure] [right enclosure]
      Example:
      set sqlformat delimited , < >
      7369,,,7902,17-DEC-80,800,,20,5555555555554444

      ansiconsole : advanced formatting based on data and terminal size
      set sqlformat ansiconsole : base format
      set sqlformat ansiconsole default : number formatting to ###,###.###
      set sqlformat ansiconsole : Mask following Java DecimalFormat

      https://docs.oracle.com/javase/8/docs/api/java/text/DecimalFormat.html

      set sqlformat ansiconsole -config=highlight.json : highlight matches in results

      highlight options :
      Example :
      {"highlights":[
      {"type":"startWith","test":"W","color":"INTENSITY_BOLD,CYAN"},
      {"type":"endWith","test":"MAN","color":"BLUE"},
      {"type":"contains","test":"MIT","color":"YELLOW"},
      {"type":"exact","test":"FORD","color":"GREEN"},
      {"type":"regex","test":"[0-9]{2}","color":"MAGENTA"}
      ]
      }

      [email protected]>

  12. Avatar
    Daniel Looby Reply

    In the Real Time SQL Monitor at times the SQL comes back ‘incomplete’, with ‘…’ as the last three characters. What is the easiest way to get the complete SQL statement?

    • Avatar

      Yes, when I hover over the line on the page and it comes up in the black window.

    • Avatar

      No, there is query text. But the query is long and ends with ‘…’ at the bottom of the black box.

    • Avatar

      I do right click and copy the statement. And then I paste it into a worksheet. And the statement ends with:

      and email.goremal_status_ind = ‘A’
      and …

      Dan

    • thatjeffsmith

      how many characters are you getting until the Ellipsis (…) come into play?

      Maybe I should log a bug, but not sure you want 1,000,000 characters put onto your clipboard when you could/should just query v#sql for the SQL_ID text.

  13. Avatar
    Mark Casazza Reply

    Hi Jeff,

    I’m probably going to feel foolish when you provide the answer, but…. I’ve got a simple query that runs fine in SQLD (v.21.2) but when I transfer it to a User Defined Report it fails with a ORA-00933 error. What am I missing? The query:

    SELECT
    ‘1’ srt,
    ‘irdb_dw’ owner,
    MAX(ir_run_date) run_date
    FROM
    [email protected]

    UNION

    SELECT
    ‘2’ srt,
    ‘fnd_irdb_dw’ owner,
    MAX(ir_run_date) run_date
    FROM
    fnd_irdb_dw.wc_ira_term_enrlmt_ytd_ps

    UNION

    SELECT
    ‘3’ srt,
    ‘stg_ir’ owner,
    MAX(ir_run_date) run_date
    FROM
    [email protected]

    order by
    srt
    ;

    Thanks,
    Mark

    • thatjeffsmith

      Hmmm, I can do a TABLE style report with this code, with or without a semicolon (;) at the end…now, I don’t have your code or DB_LINKS to play with, so not sure if they come into play or not.

      SELECT
      '1' srt,
      user owner
      FROM
      dual
      UNION
      SELECT
      '2' srt,
      user owner
      FROM
      dual
      UNION
      SELECT
      '3' srt,
      user owner
      FROM
      dual
      ORDER BY
      srt

    • Avatar
      Mark Casazza

      Hi Jeff,

      That code runs fine so I’m assuming there is something about the dblinks it doesn’t like. If you think of something else I should test, or if you think of another way to do this, I’d love to know.

      Thanks,
      Mark

  14. Avatar

    Hi Jeff,

    In MySQL Workbench, there’s a feature that shows the SQL commands that will be executed every time a user change something (Update a row in the datagrid, change a column’s datatype). I want to ask if SQL developer has a similar feature. Ultimately, I just want to be careful when creating any changes.

    Thank you in advance

    • thatjeffsmith

      Data grid changes, no.. Although you can see them afterwards in the Log.

      Table structure changes yes, we preview that DDL for your.

  15. Avatar

    Hi So I have this fairly large model comprising of 500+ tables in SQL Data Modeler – with a whole bunch of subviews thrown in. The question is – is it possible to export / save each subview as a separate model – for ease of distribution, deployment etc.?

    The use-case here is that we would like to manage the data-model centrally across a suite of apps – with each subview representative of a specific app within this suite. When an app is deployed, the corresponding subview alone would need to get deployed.

  16. Avatar
    Florian Schulze Reply

    Hi Jeff,
    is it possible to export the ORDS_METADATA schema (with our pl/sql handlers) via expdp and import the schema in different database ?
    background:
    Usually we export all relevant application schemas from production database to our testing environment. We implemented pl/sql handlers for our application and we want to keep the ords-pl/sql code compatible to correspondig application schemas in testing env.

    • thatjeffsmith

      For sure.

      I would normally recommend you source control your rest APIs, oauth2 clients, etc via scripts…then you could just run those as needed in various environments.

      But data pump should work just fine.

  17. Avatar
    Keith Bines Reply

    Hi Jeff

    Another question on scaling with a 4k monitor. Followed your suggestions re jdk11 and that works great. However the Real Time SQL Monito remains tiny. Any advice on how to increase the font size of the monitor

    Thanks

    Keith

    • Avatar
      Keith Bines

      Hi Jeff,

      tried following:
      Windows 10
      Right click on sqldeveloper.exe -> properties -> compatibility -> Change high DPI settings -> Override high DPI scaling behavior. Scaling Performed by -> select “System” form drop down.

      Doesn’t affect the Real Time monitor – still very small

      Keith

    • Avatar
      Keith Bines

      Ah – has to be done from a beach. Lying on a towel or a beach chair 🙂

      Cheers – enjoy

      K

  18. Avatar

    Thanks for the quick response Jeff..Appreciate it.

    1 – yes, simply swap out the files–

    Is this supported officialy and what files should we be swapping out..swapping just “sql” will do?

    2-I tried set history filter for alter it works..but create doesnot..

    SQL> set history FILTER create
    Unknown Command create
    Unknown command in history filter

    • thatjeffsmith

      1 – altering the home, probably not officially supported, so just download sqlcl to /opt or wherever, update your path and run it from there
      2 – yup, i see that, logging a bug now – thanks for the report!

  19. Avatar

    Questions on sqlcl

    1)Is it possible to upgrade sqlcl which is shipped with client/DB software?If Yes, how do we do it?
    2)Is it possible to exclude create user/alter user from history. Currently the passwords could get exposed if sqlcl installation is shared.

    • thatjeffsmith

      1 – yes, simply swap out the files
      2 – yes, see the HISTORY command setings, you would just add CREATE and ALTER

      SQL> help set history
      SET HISTORY
      -----------

      set history [FAILS [LIMIT [ n | DEFAULT ] ] | NOFAILS
      |FILTER [DEFAULT ?|? | NONE]
      |LIMIT [n|DEFAULT]]

      FAILS sets history command to show failed statements.
      use limit to limit number of failues
      NOFAILS sets history command to not show failed statements
      FILTER sets a list of sqlcl commands that can be excluded from the history
      LIMIT sets a new max size for the history

      NB. History will not save failed commands at end of session.
      SQL> show history
      HISTORY
      enabled
      filter: show, history, connect, clear, dbccred, dbcc
      Do not show failed statements
      SQL>

  20. Avatar

    Load Table using sqlcl

    I have been trying to use load table in sqlcl..For number data type i noticed sqlcl is rounding off big numbers…Is there anyway to avoid it?

    • Avatar

      Column is defined as number(28)..example numbers are:

      592135377776198004078,
      592135377776635704299,
      592135377776852704089,
      592135377776773404009

  21. Avatar

    Hello. I’ve installed the newest SQL Developer 21.2 on MacBook Pro OSx11.5 Java 11.0.9
    The problem is that in a code editing window (package or procedure) the run/debug icons don’t appear.
    Is there a solution?
    Thanks in advance.

  22. Avatar
    Jens Helbig-Jachens Reply

    Hello Jeff,

    Since SQl Developer Version 19. a connection to the database via VPN is no longer possible. (ERROR 17002).
    When I work in the company, SQLD works fine. Only version 18.4.0.376 is still able to establish a connection via VPN. We also have a VPN in the company, and the same phenomenon can be observed there. Every employee of our company or of customers have the same problem. What is the cause and can we fix it ourselves?

    regards from germany
    Jens

    • thatjeffsmith

      You’ve tried this with 21.2?

      It’s been happening for 2 years, have you opened a SR with MOS? Give me that SR# and I can take a look.

      Your problem description doesn’t match any known issues…we use VPN here all day, every day as you can probably imagine.

Write A Comment

RSS
Follow by Email
LinkedIn
Share