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!

5,038 Comments

  1. Hey Jeff,

    We use IDM password for our Oracle databases authentication. They expire every 3 months and we change them in the IDM tool.

    Is there a way I can make an update to my connections.json file to reflect this new password? (I know it isn’t the most secure method). I am on Sql Developer 19.1 version.

    When my password is incorrect, I get an alert saying “invalid username/password”. I can then go to properties and update the password. Is there a way I can get a popup/ prompt to put in a new password along or after the error popup?

    Thanks!

  2. I’m trying to figure out how to use the following setup that I use in SQL Developer export a result set, in sqlcl. Here’re the setting I use in the GUI Export Wizard:

    Format: delimited
    Header: x
    Delimited: tab
    Line Terminator: environment default
    Left Enclosure: none
    Right Enclosure: none
    Save As: Single File
    Encoding: UTF-8

    This exports in a format that’s accepted by an upload process with a customer. What I’m trying to do is schedule a sqlcl process to create the same file. Does the sqlcl support setting the above export settings?

    Thanks,

    Dennis…

  3. Jeff,
    Really enjoy SQL Developer (ok, enough flattery). At times I export a lot of data from production and import into development. That way users can see/work with real data. When importing (using a script) the Script Output screen displays whats happening. As data is inserted you see 1 row inserted. Is there a way to display what line number in the script is running? I can open the sql file and see the total lines that need to run and then when the script is running I can see how far along it is. Maybe as a setting to turn on/off. I doubt I’d want it on short files. Just wondering.
    Thank you in advance for all assistance.

  4. Hello, I followed blog ( https://developer.oracle.com/databases/building-jet-crud-apps-odb12c ) to invoke rest web service but am getting CORS error ( refer below). Have installed ORDS within sql developer ( version 18.1.0.095) and rest service works and returns json output. I tried setting “Origins Allowed” at module level but still it gives gives the same error.

    Can someone please guide as how to setup the access control across domains in ORDS.

    Thanks in advance.

    Error :

    Access to XMLHttpRequest at ‘http://localhost:8080/ords/apps/hr/department/555’ from origin ‘http://localhost:8383’ has been blocked by CORS policy: Response to preflight request doesn’t pass access control check: No ‘Access-Control-Allow-Origin’ header is present on the requested resource.

  5. I’m using SQL Developer version 18.4. When I’m logged in as SYS and do a Database Export and specify objects for a particular user/schema, it generates all of SYS’s objects as well. I would have thought that specifying particular objects would limit the export to those only. Is there any way to change this behavior?

    Thanks

  6. Hello Jeff,

    I have two questions about reporting features in SQL Developer (i’m using latest version 18.4).

    First, it’s about the refresh time. When I generate a report and open it, I can ask SQL Developer to refresh it at regular basis. There is a list with every 5sec, 10, 15.. until every 120 seconds. But, I was wondering if we could put a different value in there? Like every 10 minutes? Or every 15 minutes?

    Also, I was wondering if we could have automatic conditional formatting? Meaning, whenever a value in a table report is let’s say less than 0, display it in RED?

    Regards,

    • Hello Jeff and thank you for your answer.

      I supose you mean something like
      SELECT
      CASE val1 < 0 THEN '’ || val1 || ” END,
      CASE val2 < 0 THEN '’ || val2 || ” END

      FROM dual

      I was hoping there was a more global solution. But it’s fine.
      Thank you for your answer.

  7. I have two tables. One is for Task

    ID TASK
    1 Abc
    2 Def
    3 Ghi
    4 Jkl
    5 Mno
    6 Pqr

    The second one is for getting dependent tasks
    ID DEPENDENT_ON
    2 1
    3 1
    4 2
    4 6
    5 2
    6 5

    Is it possible to write a sql query to get a list of all the tasks (recursive) which are dependent on a particular task.
    Example
    I want to check what all task are dependent on ID=1
    expected output=
    2.Def,3.Ghi (Which is 2 and 3)
    Furthermore query should also give output of these two dependent tasks and so on
    Final output should be
    2.Def, 3.Ghi ( This is level one)
    4.Jkl, 5.Mno ( Dependent on task 2)
    6.Pqr (Dependent on task 5)

    • thatjeffsmith

      Sorry, I just don’t have the time to help people with their SQL here. But you can try on StackOverflow or AskTom.

  8. I’m trying to configure ORDS to handle a database outage.
    I’m not seeing much on that subject on internet or Oracle support. Just your one comment on https://community.oracle.com/thread/4185844.
    Is setting db.invalidPoolTimeout in de default.xml the ‘supported’ way to go with this?
    Is there a resource where all parameters for these xml configuration files are described?

    • thatjeffsmith

      What’s your HA solution? RAC? Data Guard? For DG you just have your TNS setup to handle the failover appropriately and build that in your connection pool properties using a custom TNS/JDBC URL.

    • Thanks,
      We use just Data Guard.
      It’s just that this database (primary site) still gets bounced every night (don’t ask).
      We just need to make sure the databaseconnection gets restored after the bounce without any help (parameter setting or bouncing Tomcat for instance).

  9. Hi,
    I am using the SQL Developer 18.2 version and cant seem to find the option to Execute current statement or auto select current statement in tools – preferences. I need to execute the current query when I place the cursor on the query and hit ctrl + enter. This was available in version 4 – not sure if its been taken in this newer version. Thanks.

    • thatjeffsmith

      there’s no option, that’s just a feature. But check your keyboard shortcuts in preferences and make sure ctrl-enter didn’t get mapped to something else

  10. Juan Rojas Orellana Reply

    Hello, I want to example of sentences bulk collect with for all for apply sentences DML (Delete, Insert and Update). Thanks you very much.!

  11. Kerstin Volkenand Reply

    Hello Team,

    I’m using SQL Developer Version 17.4.0.355 under Windows client 10. I use the editor to execute insert and update statements und outputs under skript-output: is (messae, then 2 empty rows)
    1 line updated

    1 line updated

    show all
    shows Message
    wrap: Zeilen werden umgebrochen

    I would like to know is there an set to generate the output as:
    1 line updated
    1 line updated
    till commit?
    Under windows client 8 there was no problem.

  12. Hey Jeff,

    I’m using SQL Developer 18.4. I changed my Look and Feel to Windows. Now, when I open a new worksheet (shared or unshared), I get the Query Builder tab next to my Worksheet by default.

    Is there a way I can disable the Query Builder to show by default?

    Thanks!

    • thatjeffsmith

      the query builder is always next to the worksheet, it’s actually part of the worksheet

      are you saying it has focus, or is open, as opposed to the worksheet, by default?

      did you try Window > Reset Windows to Factory Settings ?

    • Hey Jeff,

      Thanks for replying. The Query Builder doesn’t have focus or is open by default. It is just an extra tab that I see next to the Worksheet that I don’t plan to use.

      I was trying to maximize my Editor area. I have disabled the Main Toolbar and have the “Full Screen” enabled.

      I use keyboard shortcuts most of the time.

      So, I wanted to see if I could

      1) Disable the Query builder tab
      2) Disable the toolbar that has the “Run”, “Explain Plan”, “Commit” buttons. This toolbar also has the drop-down for switching connections but I mostly use Unshared Worksheets and I switch windows using Alt+Tab.

      Thanks again.

    • thatjeffsmith

      Sorry, there’s no way to disable the Query Builder…unless you see it as a Feature on the Tools menu.

  13. I just downloaded the newest SQL Developer version 18.4.0.376 and try to change the Preferences for “Change case as you type” to “Upper Identifiers, lower keywords” but it looks like that option is no longer available or they took away. I tried to change it in the “Code Editor > Format” but it does not seems to work right in the worksheet. Do you have any idea if there is another way to change that option.

    • thatjeffsmith

      The simple formatter options allow you set identifiers/keyword case, and there’s an option for the formatter to ONLY make these changes. Hit Ctrl+F7 to invoke the formatter on demand.

  14. Stuart Higgins Reply

    Hi Jeff, Great site and tweets! I need to connect to Oracle 8.1.7. Which is the latest version of SQL Developer that will work with it ? I would love to use the latest version if there is a way to do that. Thank you.

    • thatjeffsmith

      Not much we can do for 8i. 11g is about as far back as we support today.

      You can try with version 1.5 maybe, but even that might not work.

  15. Hi Jeff,

    I often have the challange to identify all appearances of a database link. When I do a search, I get no results for synonyms. Should I be able to find database links used in synonyms, or is that not possible with the search?

    BR
    Stefan

    • thatjeffsmith

      appearances of a db link – why aren’t you searching the SOURCE area for occurrences of a ‘@’ ?

    • I tried, but it does not work db-links used in synonym definitions (DDL). It searches the code pretty well, but there are only the names of the synonyms, and I don’t know what DB-Link the developers hided inside. 🙂 So I guess, I will have to find another way.

      BR
      Stefan

    • thatjeffsmith

      What I would do..

      write some sql using regex to key in on the @ in your source text column, and isolate the name of the synonym. Then join on the synonyms views to get what you’re looking for.

    • Hi Jeff,

      thanks for your fast reply. I found out that

      select OWNER,SYNONYM_NAME from dba_synonyms where upper(db_link) like ‘LINKNAME’;

      does the trick!

      Have a nice day!

  16. Hello, I followed blog ( https://developer.oracle.com/databases/building-jet-crud-apps-odb12c ) to invoke rest web service but am getting CORS error ( refer below). Have installed ORDS within sql developer ( version 18.1.0.095) and rest service works and returns json output. I tried setting “Origins Allowed” at module level but still it gives gives the same error.

    Can you please guide as how to setup the access control across domains in ORDS.

    Thanks in advance.

    Error :

    Access to XMLHttpRequest at ‘http://localhost:8080/ords/apps/hr/department/555’ from origin ‘http://localhost:8383’ has been blocked by CORS policy: Response to preflight request doesn’t pass access control check: No ‘Access-Control-Allow-Origin’ header is present on the requested resource.

  17. I am new about SQL Dv Data Modeling; version 18.4 of Dmodeler needs “msvcr120.dll” under bin directory.
    JDK is already installed and its version 11.2. Is there anything that mismatches?

    • thatjeffsmith

      wow, i haven’t seen that error in a LOOOOONG time

      i think the easiest fix is to just search your pc for that dll file, and copy it to your sqldeveloper\bin directory

  18. Hi Jeff,
    I use many connections in Oracle SQL developer (100+, various databases, schemas). I named the connections to have them sorted and well organized to quickly find out right one.
    But it is useless when I try to open a new SQL worksheet by pressing Alt-F10 (or change a connection in current worksheet in right-upper dropdown list), becouse connections in ‘Select connection’ window are sorted by last used timestamp.
    Is it a way how to change the behaviour?

    And one more proposal – would be good to have option to control text and background color in the dropdown list of connections (or check box, apply color setting for frame only, not for connection dropdown list). Color frame around worksheet is nice, but some combinations of colors are not readable in dropdown list.

    Thanks a lot
    Martin

    • thatjeffsmith

      No sorry there’s no way to make that change today.

      Since you have your organization already configured the way you want connection wise, why not open a worksheet from your connections panel instead?

  19. Hi Jeff!

    I’m a keyboard shortcuts enthusiast!
    I try to use as much as I can Oracle SQL Developer with the keyboard.

    I know how to open a new window with a new connection (Alt + F10).

    But there is an action that I did not find the shortcut. How do I change the connection of my current window?

  20. Cléber Macieski Reply

    Hi mr. Jeff, I searched the web and didn’t find so: is there a way to customize a default tab in the ‘open declaration/describe’ at SQLDeveloper 18.4 for Windows 10?
    I would not like to to create an xml extension Constraint2 tab with my layout, so I will be happy to know like to override the default layout.
    Thank you.

    • thatjeffsmith

      I don’t believe so…the best you could do is add a new page to an existing object viewer, then open that object, and tab to your custom page.

  21. Hi Jeff

    All SQLcl 18.4 features seem to be working pretty well on cygwin except one : TAB Completion
    I’ve tried with both login shell (bash and ksh) and still doesn’t work

    Similar thread has already been raised : https://community.oracle.com/thread/4085611 but with no relevant answers asof now.
    I think this is related to cygwin env setting knowing MobaXterm which is technically very close to Cygwin+Putty.

    Under my shell Tab competion works perfectly fine (whether using bash or korn shell) so this is rather odd

    Here is my configuration ( with bash) :
    # sqlcl

    Launching /Appli/sqlcl_18.4/bin/sql SYS/********@C1N00002 as SYSDBA …

    SQLcl : version 18.4 Production sur jeu. mars 21 11:29:50 2019

    Copyright (c) 1982, 2019, Oracle. Tous droits réservés.

    Connecté à :
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Advanced Analytics and Real Application Testing options

    SQL> show version
    show version
    Version d’Oracle SQLDeveloper Command-Line (SQLcl) : 18.4.0.0
    SQL>
    # uname -a
    CYGWIN_NT-6.1 S0415796 2.10.0(0.325/5/3) 2018-05-18 20:52 i686 GNU/Linux
    # bash –version | egrep ^GNU
    GNU bash, version 4.1.17(0)-release (i686-pc-cygwin)
    # env | egrep -i term
    TERM=xterm-256color
    ORACLE_TERMINAL=/Appli/oracle/product/12.2.0/client_1/guicommon2/tk21/admin/terminal/US
    WINPATH=C:\Program Files\Common Files\Oracle\Java\javapath;C:\Appli\oracle\product\12.2.0\client_1\bin;C:\Appli\MobaXtermRoot\bin;C:\oracle\ORA112\bin;C:\oracle\ORA102\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\1E\NomadBranch\;C:\Program Files\GTK2-Runtime\bin
    HOME=/home/mobaxterm
    LOG=/home/mobaxterm/LOG
    TERMNUM=3
    HISTFILE=/home/mobaxterm/.sh_E0254079
    #set -o
    Current option settings
    allexport off keyword off notify off trackall off
    bgnice off login off nounset off utf8-mode off
    braceexpand on markdirs off physical off verbose off
    emacs off monitor on pipefail off vi on
    errexit off noclobber off posix off vi-esccomplete off
    gmacs off noexec off privileged off vi-tabcomplete on
    ignoreeof off noglob off restricted off viraw off
    inherit-xtrace on nohup on sh off xtrace off
    interactive on nolog off stdin on

    Thanks by advance !

    • thatjeffsmith

      I don’t use cygwin or MobaXterm…my buddy Tim (aka oraclebase) says he’s running MobaXterm and tab completion in sqlcl 18.4 is working as expected there.

  22. hi Jeff, I downloaded and installed SQLDeveloper 18.4, but I can’t connect using my Oracle wallet. I have a local wallet (not Cloud Wallet).

    Currently I’m using SQL Developer 3.x and the connection I use for my DBs is: jdbc:oracle:oci:/@dbname.

    It connects flawlessly. But I can’t get SQLDeveloper 18.4 to do the same. I get this error:

    “An error was encountered performing the requested operation:no ocijdbc18 in java.library.path”

    I downloaded the ODBC drivers from Oracle but no matter where I place them, I keep getting the same error.
    I only have one client folder.

    I tried enabling Oracle OCI on it, but was not able to (I have the classic client, not instant client).

    So I’m puzzled on how SQL Developer 3.x can connect real quick using the wallet, but SQLDeveloper 18.4 can’t. By the way, SQL Developer 3.x does not have the Use OCI/Thick Driver checkbox checked in the Database Advanced settings. I tried both with SQLDeveloper 18.4 and none of it has worked.

    Let me know if you have any advice.

    thanks!

    • thatjeffsmith

      the error is saying we can’t find a compatible oracle client for the 18c jdbc driver we’re using

      you need to configure the oracle home to point to a valid 18c oracle client home or instant client

      19.1 MIGHT allow you to setup a jdbc-THIN connection using a wallet too – so stay tuned

  23. In SQL Developer when I click on File – Open, in the Location field I want to change what is defaulting in that box. Where in the setup can that default be changed?

  24. In SQL Developer when I click on File – Open in the Location box it defaults a directory. I would like to change that default. Where is that set?

    • thatjeffsmith

      Based on your OS user temp directory the first time you do this. But the 2nd time you do it, it’ll start where you left off last time, the MRU directory.

      And then down the left hand side, you’ll have a historical listing of previously used directories as quick short cuts.

Write A Comment