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

  1. Avatar

    Hi Jeff

    I sometimes run a bad query that runs for ages (I’m sure we all do).
    Using the Cancel Query button does not seem to kill the query in most cases.
    Is there a good way to get your session back without sys access?

    • thatjeffsmith

      when you select the word and right-click to open the object, you can alternatively ctrl-mouse hover-click…might be faster. thanks for sharing!

  2. Avatar

    Hi Jeff,
    have upgraded to sqldeveloper 4.1.1.19 a couple of months ago and have one issue. My previous login.sql script doesn’t appear to work as it did with the previous version. Basically i dont’t see any output from dbms_output. I have tried issuing selects in the login.sql script, but don’t see the results of these either. Here’s a snippet of my login.sql

    — start

    set serveroutput on
    set termout on

    declare
    v_mode varchar2(100) := sys_context(‘userenv’,’database_role’);
    begin
    dbms_output.put_line(‘–‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ –‘);
    end;

    — end

    I know the login.sql script is running as i have put inserts in there and then viewed the data in the relevant tables after i have connected.

    Any ideas ??

  3. Avatar
    Tim Chaffin Reply

    Jeff,
    As part of minimizing risk, I am looking for a way to password protect the SQL Developer application. The scenario we are envisioning is that by some means, an intruder has gained access to one of our DBA’s stations. By simply opening the SQL Developer app, that intruder could use the application and perform privileged actions by opening a connection through SQL developer.
    Does SQL developer have a method to password protect the application as a whole? As a means to add a roadblock in the way of any intruder? If not, are there any plug-ins that could help?

    • thatjeffsmith

      you need to secure the machine

      the passwords are encrypted and can’t be read outside the machine and UI

      you can disable the ability to save passwords, but that won’t help you if the user is already connected and the walk away

      ideally the machines lock themselves down after a few minutes of inactivity

      what do you do if a hacker gets access to a terminal and can login to a database as SYS using a simple, sqlplus / as sysdba?

  4. Avatar

    Hi, I’m a big fan of the Find/Highlight feature in SQLDeveloper. I find that persisted highlights do not persist very long though and disappear sporadically when changing search paremeters in a report. Is there a way to lock them in?

    • thatjeffsmith

      I don’t think it’s sporadic, I think it’s performing the search immediately on you entering/changing text in the ‘find’ toolbar. I can’t find a way to ‘lock in’ the highlights.

  5. Avatar

    Hi Jeff, I use SQL Dev 4.1.2 and I frequently browse large tables. SQL Dev appears to hang, & I get a ‘waiting to initialize’ message and then nothing. I have to kill SQL Dev and try again. Is this a memory thing? I have increased my memory available to SQL Dev, by adding Add64VMOption -Xmx1024m – which is up from the default 800m as you can see. This seems to have fixed it. Would this be the correct thing to do? Sorry I forgot to mention I’m using Windows 7 64bit

    Thanks,

    Vic

    • thatjeffsmith

      You could also try lowering the fetch array size in the preferences to retrieve fewer rows at a time, but adding memory is nice if you have it available.

  6. Avatar
    Jim Porter Reply

    Hi Jeff,

    We are using SQL Developer 4.1.0.19 – Build MAIN-19.07. We are using Oracle 11g Enterprise.

    Operating System is Windows 7.

    We are having an issue when compiling Packages.

    There are times when the compilation process causes SQL Developer to hang.

    It seems that the only way to get back to a state where we can compile the Package is to shut down the SQL Developer instance and restart it. We normally do this by going to the Task Manager and ending the task.

    Is this possibly an issue with SQL Developer? If so, do you have any suggestions for a workaround?

    Thanks.

    • thatjeffsmith

      did you check for wait events/locks for your session in the database?

      what’s happening in the database when you do the compile?

    • Avatar
      Jim Porter

      I talked with our DBA. He has asked if there have been any reported issues between SQL Developer and the Oracle Database Firewall.

      Would you have any information on this?

      Thanks again.

    • thatjeffsmith

      That’s a pretty vague question. I don’t know of any, but I haven’t searched the bug DB to know for sure. Tell your DBA he can open an SR with MOS for SQL Developer if he needs to.

    • Avatar
      Jim Porter

      Sounds good. I will relay the information.

      Thanks again.

  7. Avatar

    Hi Jeff,

    in terms of customer support and development i use quite a number of SQSDev installations on various machines (all windows).
    on every installation i have to make the same changes in the configuration for having SQLDev look and work the same. no matter on which machine i’m logged in.

    is there a possibility to (auto) synch the configuration over different systems?
    as an example the tortoise client offers such a feature: you can define a synch directory in which the config is stored; outside the regular path. if you place this synch on lets say your drop box directory you have an easy way to transfer changes in the setting over a great number of installations.

    if not existing, does this sound like a cool feature?

  8. Avatar

    Hi Jeff.

    We are planning for migrating our data models from CA ERwin to SDDM. One requirement is collaborative development and I have a great hope that we can use the Team functionality. With SD 4.0 or SD 4.1, what version of Subversion are supported/required (… we have an existing Subversion 1.6.11 installation)?

    Thanks!
    Jo

  9. Avatar

    I’m using SD 4.1.1 and 4.1.2 – coloring the connections is great. But even if I have read (and seen in some of your pictures) that the folders could be colored too I don’t find how it works? Any hint would be great … THX

  10. Avatar

    Hi, my requirement is to inject details like Connection Name, Username, Password, Hostname, Port and SID from the command line (or using any API if exist) into Oracle SQL Developer for Oracle database, so that all these details are prefilled for the user and user just need to click on the connect button to establish a database connection. I am doing this to broker the database connection.
    Any guidance on this please?

    • thatjeffsmith

      we don’t support this (sending connect string to SQLDev from CLI) today..but you could create the connections.xml file for them that has their connections already defined and ready to go

    • Avatar

      Is it possible to create/update it programmatically. And upon modification, will the Oracle SQL developer pre-filled with the fields like Hostname, username, password, etc?
      Where I can find this file? Any tutorial you can suggest me on this?

    • thatjeffsmith

      it’s an xml file, search for connections.xml

      you could create it programmatically as long as you follow to the xml structure expected

    • Avatar

      Hello jeff, thanks for your reply. I am sorry but I searched and found no file with the name of connections.xml. I searched in my all drives.
      I installed Oracle SQL developer and it is installed under “\Program Files (x86)\Microsoft Application Virtualization Client” folder. I do have file like sfterr.xml under it but no able to trace connections.xml anywhere in my system? Is something I missed during installation? I have installed SQL Developer 4.0.3 version and I am able to connect it with Oracle database system using this client.

  11. Avatar

    hi,
    im using SQL developer after i switched from toad,
    the data grid/query result in toad used to show up as a table when we ran the query,
    the query result is plain in sql developer by default,
    i want my result to be in table format,
    i tried changing a lot of things in colours in preferences but can’t find this one.
    please let me know

    • Avatar

      doesn’t work 🙁 i fire a query always by ctrl+enter,
      and it is a table, i want the cells separated by a line.
      that is not happening 🙁

      can u suggest something?

  12. Avatar

    Hallo,
    The sqlcl is amazing. But…
    as i run the dbms_job procedure with SQLcl, then always failed. F. e.:
    SQL> exec sys.dbms_job.broken(90,true);

    Fehler beim Start in Zeile : 1 in Befehl –
    exec sys.dbms_job.broken(90,true)
    Fehlerbericht –
    ORA-06550: Zeile 1, Spalte 7:
    PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von ‘BROKEN’
    ORA-06550: Zeile 1, Spalte 7:
    PL/SQL: Statement ignored
    06550. 00000 – “line %s, column %s:\n%s”
    *Cause: Usually a PL/SQL compilation error.
    *Action:
    Sorry im not knowing how to change the output language.
    Could you please give me some advices?
    thank you
    Jipeng

  13. Avatar

    Hi,

    When I go here:
    View > Show Toolbar > Customize Main Toolbar > Initializing…
    It say this

    How do I solve this?

    Thanks.

    • Avatar

      ok to solve this you need to update sql developer (even though when i went to view updates within the app it said there were none)
      hmmm

    • thatjeffsmith

      Who said you needed an update? I haven’t taken a look at your original question yet, on vacation until Monday.

      There’s not too much customization available for the main toolbar as it is, what are you trying to do in the first place?

    • Avatar

      I checked the version of a colleague who doesn’t have this problem and thought an update may help,
      I was on 4.1.1, now i’m on 4.1.2 and all is well

  14. Avatar

    As an admin/teacher when I have my class do a project involving writing SQL statements is there a way I can see if anyone has looked at someone else’s Saved SQL statement that they have written? Or can I only see if they ran someone’s Saved SQL statement?

    • Avatar

      Jeff,

      I hope you see this question and reply back. I was worried when I saw some of these questions unanswered. I would like to know if I can or not see what I asked. I was thinking about using APEX for teaching.

    • Avatar
      Jeffrey Kemp

      Your question is about Apex, not SQL Developer. I was going to say “Go Ask Tom” as per the post above, but then, you are Tom already…

    • thatjeffsmith

      Give each student their own OS user, that should segregate the histories. Or, create a batch/shell script to launch sqldev that starts with deleting the history files

  15. Avatar

    SQL DEVELOPER: Create Unit Test Wizard too slow, why?

    Hi Jeff,

    Why does it take such along time (it can take between 40-60 secs or more) to display the package list during ‘SELECT OPERATION’ step in the ‘Create Unit Test Wizard’?

    Is there a way to speed it up?

    Thanks.

  16. Avatar

    Hello Jeff

    I need a row limit for a query to testing purposes for our crm.

    So i got from another section an query which i have to use with an row limit ( for example the first 10 rows based on Field FIRMANR).
    But i came across with the location where the row colum command had to stand in the query. I thought before all correlations command like order an so on .

    Here is the query:

    SELECT DISTINCT RELES.AKTJAHR,
    RELES.VORJAHR,
    RELES.QUARTALE_1,
    RELFIRMA.NAME,
    RELFIRMA.KTXT,
    RELES.VORVORJAHR,
    RELFIRMA.FIRMANR,
    RELANSCH.STRASSE,
    RELANSCH.ORT,
    RELANSCH.LAND,
    RELANSCH.STAAT,
    RELANSCH.PLZORT,
    RELANSCH.PLZPOSTFACH,
    RELANSCH.POSTFACH,
    RELACP.VERTRETER2,
    RELACP.VERTRETER1,
    RELANSCH.LANDKNG
    RELES.AKTJAHR,
    RELES.VORVORJAHR,
    FROM (((INFOR.RELFIRMA RELFIRMA
    INNER JOIN INFOR.RELADRESSE RELADRESSE
    ON (RELFIRMA.FIRMANR = RELADRESSE.FIRMANR))
    INNER JOIN INFOR.RELANSCH RELANSCH
    ON (RELADRESSE.ANSCHRIFTNR = RELANSCH.ANSCHRIFTNR))
    INNER JOIN INFOR.RELES RELES
    ON (RELES.DATENFELDER_1 = RELFIRMA.FIRMANR))
    LEFT OUTER JOIN INFOR.RELACP RELACP ON (RELFIRMA.FIRMANR = RELACP.MNR)
    WHERE ( RELANSCH.VERWENDUNG1 = 1
    AND ( (RELES.SATZART = ‘UK’ AND RELFIRMA.VERWENDUNG1 = 1)
    AND (RELES.AKTJAHR > 10000 OR RELES.VORJAHR > 10000)

    From my understand it has to be in the near of thw WHERE clause or ?

    thanks for help

    best regards john

  17. Avatar

    Hi Jeff,

    I have only recently started using SQL Developer and find it very useful and flexible. I have a couple of questions re. SQL Developer functionality (please let me know if you would like me to post them as two separate comments):
    1. How do I save my windows layout in SQL Developer? For my work, I like to have six separate document tab groups. I configure it when I start SQL Developer first time. But when I exit and re-open, it doesn’t have those document tab groups. Is there a way for me to save the look-and-feel, so I don’t have to reconfigure it every time I restart SQL Developer?
    2. How do I see parent-child data in SQL Developer? This is similar to the master detail browser in TOAD? Is there a way for me to view parent and child information simultaneously such that if i change the parent value, the child value will automatically modify accordingly?

    Thanks a lot in advance,

    Niraj

    • Avatar

      Jeff,

      Quick update…

      I was able to figure out how to create master-detail report. But I would really like to hear from you response to my other question.

      Thanks,

      Niraj

  18. Avatar

    I like what you’ve done here! Thanks for being so available to customers.

    I have multiple Oracle clients, can I specify which Oracle client that SDDM 4.0.3.853 is using? Trying to get past this error when connecting to a DB.

    Status : Failure -Test failed: Incompatible version of libocijdbc[Jdbc:112020, Jdbc-OCI:112030

    • Avatar

      Did you ever find a resolution to this issue? I’m running into the same problem

    • thatjeffsmith

      Use a thin jdbc connection, unless you really need a thick connection. What version of SQL Developer Data Modeler are you using?

    • Avatar

      Specifying the JDBC connection string solved it. Data Modeler v17.3.0.261

      Steps to reproduce
      1. File > Import > Data Dictionary
      2. Connection Properties
      3. Connection Type: Oracle – TNS
      4. Cry

      I have tried specifying the Oracle Client and TNSnames directory in the Preferences > Data Modeler menu without luck.

      Seems like specifying a connection by TNS should work out of the box.

    • thatjeffsmith

      That’s the idea. There’s a spot in the preferences to define your tnsnames location, and another to force ‘thick’ connections. I’ll give the modeler a once-over tomorrow morning and make sure there’s nothing crazy wrong on our side. Sorry for the tears.

  19. Avatar

    Hi,

    Every time I open SQL Developer 4.0.1.14 it displays things differently. I want the main toolbar hidden (more on that in a mo) connections visible and everything else closed.

    Sometimes I get connections sometimes i don’t, sometimes I get this crazy long toolbar sometimes I get a useful compact toolbar.

    Is there anyway just to save your setting so you don’t have to set it up and change it every time you open it?

    Thanks, Dave

  20. Avatar
    Christophe Paquet Reply

    Hi Jeff,
    Can you tell me if it exists a way to display the parameters needed for a function or a procedure while coding.

    Thanks,

    • Avatar
      Christophe Paquet

      Thanks for your quick response, but it is not working for me.

      Are there any special conditions or special configuration of the application to display these parameters?

      Or should the database must be in 12c?

      Thanks,

  21. Avatar

    Hi Jeff,

    This may have already been asked, but I searched for it and could not find it.
    Within SQL Developer, is it possible to prompt a user to choose between 2 choices. I have them entering data into a prompt via :hierarchy_name but would like to offer them to choose between the 2 sets of text, they can choose from.

    Basically each time a query is run they have a choice of entering into the field either “internal hierarchy name” or “external hierarchy name” and I thought it would be good if there was either an option to choose or at worst both of these showed in the field and they could delete one, but I cannot work out if this is possible?

    Hope that makes sense!

    Deryk

  22. Avatar
    Laurie Baublitz Reply

    Hi Jeff,

    Is there a way in SQL Developer to “save your place” so your worksheets and query results and everything open up again in a new session? My colleague’s SQL Developer does this for him every day, and I’d love to have this ability as well. When he opens SQL Developer tomorrow, it will have all his open worksheets and query result windows right there for him. To run anything, he just has to select the proper connection. But, he can’t figure out how he got it to be that way!

    Thanks!
    Laurie

    • thatjeffsmith

      Sorry, no. If you close SQL Developer with a document open, on restart, it should re-open that file for you. But that’s not what you’re really asking for…

  23. Avatar

    Hi Jeff
    Is it possible for a sql worksheet to “view” the window of another connection in the same grid.
    It would be a cool feature to have, if i can fire one query say “select rate_value from job_table” and i get to see the status of the rates in DEV, QA, PROD in one grid.
    Not needed for any write transactions, a view only data(ie SELECT can support). 🙂
    Thanks George

  24. Avatar

    Jeff, does SQL Developer allow specifying the edition to connect to when using Edition-Based Redefinition?

Write A Comment