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.



  1. Avatar

    Is there somewhere I can set up or some other key to auto select the first suggestion from code completion or a code editor template?

    For example if I have id=saf and template=select * from can I set it so I type saf and then can start with my table-name without having to use the mouse or down arrow to select the first option.


    • Avatar

      tab or some other key – I put TAB in brackets and it was removed.

      I’d like to type safTAB

    • Avatar

      Thanks I knew of that method.

      I was just curious if I could alter it to a single keystroke (like tab) without having to move over and hit the down arrow then enter.

      Super lazy I know, but actually would be a lot quicker 🙂

  2. Avatar
    Rick Wheeler Reply

    When I import excel data and use the Insert script option SQL Developer adds decimals to the input excel “data”. This occurs when setting the data type to general or number in excel. For example values of 1 become 1.0 in the insert script. Why does this happen ? It creates issues when trying to insert values for Foreign Keys when the values are 1 and not 1.0.


    • thatjeffsmith

      Rick, my first guess/question – what’s the column definition in the Oracle table – is it a number or integer?

    • Avatar
      Rick Wheeler

      Thanks for you timely reply. The columns in Oracle are defined as Number(4,0) I have also tried Number(4) and Integer.

      I have upgraded to but the insert scripts created still have 1.0 instead of 1.


    • thatjeffsmith

      I’ve got numbers formatted as ‘General’ in Excel. They show as 1,2,3,4…

      When I launch the wizard, the data preview also shows the data as 1,2,3,4

      The columns are defined as Number (4,0)

      I see in the scripts where we bring that in as 1.0, 2.0, 3.0, 4.0…but when you actually insert the data, it’s not significant and it’s stored as 1,2,3,4.

      So, is this actually a problem, or does it just look ‘weird’ in your script?

    • Avatar
      Rick Wheeler

      It does look “weird” but I agree it seems to work in sense that the storage and insert works most times. I do however from time to time get an error implying that the foreign key value of 1.0 does not exist when in fact 1 does.

      Next time I can reproduce the error I will provide more…

      Thanks for your time.

  3. Avatar

    Hi Jeff,
    Good stuff on your website.

    A Solution:
    sudo cp /Library/Java/JavaVirtualMachines/jdk1.7.0_51.jdk/Contents/Home/jre/lib/libosxapp.dylib /Library/Java/JavaVirtualMachines/jdk1.7.0_51.jdk/Contents/Home/jre/lib/lwawt/

    For me, sqldeveloper used to work fine until yesterday. Now it fails to startup and throws below exception. (though it works if sqldeveloper-Darwin.conf is set to jdk1.6. per your website).

    OS X 10.9.2 (13C64)
    sqldeveloper ver 4.0.1 Build MAIN-14.48
    java -version
    java version “1.7.0_51”
    Java(TM) SE Runtime Environment (build 1.7.0_51-b13)
    Java HotSpot(TM) 64-Bit Server VM (build 24.51-b03, mixed mode

    I did not change any of above since sqldeveloper last worked fine. May be some autoupdate caused a change. (sqldeveloper/sdk)

    Exception in thread “main” java.lang.UnsatisfiedLinkError:
    /Library/Java/JavaVirtualMachines/jdk1.7.0_51.jdk/Contents/Home/jre/lib/lwawt/liblwawt.dylib: dlopen(

    1): Library not loaded: @rpath/libosxapp.dylib

    Referenced from: /Library/Java/JavaVirtualMachines/jdk1.7.0_51.jdk/Contents/Home/jre/lib/lwawt/liblwawt.dylib
    Reason: image not found
    at java.lang.ClassLoader$NativeLibrary.load(Native Method)
    at java.lang.ClassLoader.loadLibrary1(
    at java.lang.ClassLoader.loadLibrary0(
    at java.lang.ClassLoader.loadLibrary(
    at java.lang.Runtime.load0(
    at java.lang.System.load(
    at java.lang.ClassLoader$NativeLibrary.load(Native Method)
    at java.lang.ClassLoader.loadLibrary1(
    at java.lang.ClassLoader.loadLibrary0(
    at java.lang.ClassLoader.loadLibrary(
    at java.lang.Runtime.loadLibrary0(
    at java.lang.System.loadLibrary(
    at Method)
    at java.awt.Toolkit.loadLibraries(
    at java.awt.Toolkit.(
    at java.awt.Component.(
    at oracle.ide.osgi.boot.api.SplashScreen.createInstance(
    at oracle.ide.osgi.boot.OracleIdeLauncher.showSplashScreen(
    at oracle.ide.osgi.boot.OracleIdeLauncher.main(

    Before finding the solution, it would work fine if sqldeveloper-Darwin.conf is set to jdk1.6 as below:

    AddVMOption -Dapple.laf.useScreenMenuBar=true
    AddVMOption -Xdock:name=”Oracle SQL Developer”
    AddVMOption -Xdock:icon=SQLDeveloperIcons.icns

    SetSkipJ2SDKCheck true
    SetJavaHome /System/Library/java/JavaVirtualMachines/1.6.0.jdk/Contents/home
    AddVMOption -Xbootclasspath/p:../../rdbms/jlib/ojdi.jar


    • thatjeffsmith

      I’m glad you found the fix, not sure what Apple auto-updated to break it. You’re much better off running v4 with a 1.7 JDK.

    • thatjeffsmith

      that’s what I have and i don’t see any issues. can you take some screenshots and send them to me? I’m not sure exactly what you’re trying to describe.

  4. Avatar


    when I run this:
    Select * from my_table –here is my comment!

    Then in my script jus looks like this:
    Select * from my_table

    my comments are missing


    • thatjeffsmith


      If I have SET ECHO ON and execute with F5, I see the comments in the script output.

      I don’t see the comment disappearing from the worksheet.

      What version of SQL Developer are you running?

  5. Avatar

    Sure Jeff, here is…

    – NOMBRE : MICROS_Ventas_Detalle_items_v3.sql
    – VERSION : v3.0
    – FECHA : 13/02/2013
    – AUTOR : MSE
    – PROPOSITO : Consultar los detalles de las ventas de MICROS del tipo ‘COMPS’ para el bunsiness_date en curso en el
    – DESCRIPCION : Genera una sub-consulta para extraer los detalles de los ítems posteados en los checks. Consulta la
    tabla CHECKS_DETAIL para obtener las líneas de detalles del tipo ‘1’ (posteo de items) y los registros coincidentes en
    las relaciones JOINS que se indican para el business_date ingresado. El result set lo agrupa en una tabla temporal
    -‘TBL_TMP’- y luego busca las formas de pago asociadas a cada registro. Finalmente filtra por las formas de pago del
    – OBSERVACIONES : Para que el filtro funcione, se debe verificar que los ‘tenders media’ de MICROS, esten
    parametrizados en ‘PREAMBLE’ como ‘Comps’ -ojo, es case sensitive-
    – CAMBIOS VERSION: 13/02/2014 se aplica filtro por las formas de pago del tipo ‘COMPS’ -TENDER_MEDIA.PREAMBLE-,
    linea 136
    – VALIDACION : Fecha 12/02/2014 2628 y 2456 -linea 143-
    –TENDER_MEDIA.PREAMBLE , — Necesario para validar identificación del detalle de pago asociado a la
    — transacción
    –TENDER_MEDIA.OBJECTNUMBER , — Necesario para validar identificación del detalle de pago asociado a la
    — transacción
    (SELECT — Inicio Sub Consulta para extraer detalles de ítems posteados en las transacciones.
    (SELECT — Extrae el business date de la FECHA ingresada en la consulta.
    bla, bla, bla, bla…

    Whe I run this query 3 o 4 times, then all comments disappear… the lines are simply deleted.



  6. Avatar

    Hi, I usually put comments to document and explain some relations of my queries, but when run three or four times, all commented lines just disappear from my structure … timely comments I put on a line with ‘-‘ and Longer comments go with ‘/ *’ and ‘* /’ respectively.

    Is there any way to prevent my comments being deleted?.

    Thank you very much.

    PS: I am not an Oracle expert, but I offer my support to document or supplement the site in Spanish language, as for those not fluent in English, we could really have a high quality site like this in Spanish.

    Best Regards,



    Hola, suelo poner comentarios para documentar y explicar algunas relaciones de mis queries, pero al ejecutarlas por tres o cuatro veces, todas las lineas de comentarios simplemente desaparecen de mi estructura… comentarios puntuales en una línea los pongo con ‘–‘ y comentarios más largos van con ‘/*’ y ‘*/’ respectivamente.

    Hay alguna manera de impedir que se borren mis comentarios?.

    Muchas gracias.

    PD: No soy un experto en Oracle, pero les ofrezco mi apoyo para documentar o complementar el sitio en español, pues para quienes no dominamos el inglés, nos vendría muy bien tener un sitio de alta calidad como este en español.

    Saludos cordiales,


    • thatjeffsmith

      Can you give an example?

      If your comments are going before and after DDL statements – those aren’t stored in the database.

      I’m just guessing though…

  7. Avatar

    Hi Jeff

    I have downloaded, When I try to run it on XP-32 bit machine,
    I am getting below message.

    sqldeveloper.exe – Bad Image
    The application or DLL D:\sqldeveloper-\sqldeveloper\jdk\bin\msvcr100.dll is not a valid Windows image. Please check this against your installation diskette.

  8. Avatar

    Hi Jeff,

    I frequently export data from query results in SQL Developer to Excel and I’m wondering if there’s any way I can get Excel to recognize dates? It doesn’t seem to matter how I format the date before exporting, Excel treats it like a text value and just selecting Date format in Excel doesn’t change the way it treats the cells.

    If I double click on an individual cell with a date in it and hit enter Excel starts recognizing that the value is a date, but I’m hoping there’s an easier or more global way.

    I’m using SQL Developer and Excel 2010.

    Thanks for any advice.

    • thatjeffsmith

      Yes, you’ll have to format the date in Excel…I have an outstanding request to have dates exported as dates in the Excel file from SQL Developer, so stay tuned.

    • Avatar

      Any update of the date format for Excel Exports from SQL Developer?

  9. Avatar

    Hi Jeff,

    After a whole days work, SQL Developer starts bringing up the Logging Page tab even though I continually close the tab. In it, there a number of errors logged I suppose. I’m not really concerned with them as they don’t stop me from working. Just wondering if there’s a way for the Logging Page tab to stop coming up and bothering me. 🙂

    • Avatar

      Hi Jeff,

      Is there a substantial solution to this? There must be a setting somewhere?

      Thank you,

  10. Avatar
    Ken Burwood Reply

    Hi Jeff,

    I’ve just upgraded SQL Developer to the newest 4.0.1 version and am seeing strange behavior with multi-line snippets that contain multiple queries. In all previous versions, when I dragged such snippets into the worksheet, they would be pasted exactly as saved (new lines and all), and the focus would be put to the end of the snippet in the worksheet.

    However, when using my existing snippet library in 4.0.1, this works for many of the existing snippets, but there are other snippets that when I drag them, SQL Developer replaces all new lines with commas instead, and also doesn’t give focus to the worksheet. There’s nothing unusual about the snippets that this happens to.

    I also tried completely removing my snippet library and re-building it from scratch with SQL Developer 4.0.1, but in that case, it always replaces the new lines with commas.

    Is this a defect? Or, if this is supposed to happen now (I can’t that it should though) is there any way to turn it off?

    Here’s an example snippet that you can create to see the effect, put all of the queries below, as typed into the same snippet:

    select something from nothing where everything = null order by entropy desc;
    select everything from some_other_thing where why = not;

    select another_line from my_snippet where why_do_new_lines_become_commas = “defect”;

    select asking_this_question from oracle_devs;


    • thatjeffsmith

      Totally a defect, and it’s way-high on my list of bugs to fix with another patch. Which will be..soon’ish.

    • Avatar
      Ken Burwood

      Great! I’ll keep an eye out for updates then. Thanks for verifying that I wasn’t going crazy 😉

  11. Avatar

    We just upgraded to SQL Developer We’ve been doing describes on tables for years to get the column names but it looks like that’s not working any more. In fact we get this strange error that the object doesn’t exist.

    Is there any way to get desc working again in our current version?



    • thatjeffsmith

      I don’t know, the DESC feature does work for me. We did have a patch though, v4.0.1 is available.

  12. Avatar

    I just installed SQL Developer 4.0.1 primarily to migrate data from a SQL Server database to Oracle. I found – and have tried to follow – a couple of tutorials on the subject, the tutorials want me to use the ‘Migration’ tab – which, along with the Versioning tab, do not appear when I open the package. I must be doing something wrong – can you give me a hint WHAT I’m doing wrong?

    • thatjeffsmith

      You want to create a migration project – Tools > Migration > Migrate…

      You’ll need to create a repository, then connect to your SQL Server, then run the wizard.

    • Avatar

      Can you please explain the process of doing this? as there are several tutorials on it referring the placement of ntlmauth.dll in path variable.

  13. Avatar

    Ys Jeff I’ve seen tat.

    But I’m facing the diff prob evn aftr saving the dmd in svn while saving it is not working.. I’ve been wrkin in saving the dmd file fr almst 6mnths and I was successful in saving dmd in server

    bt this issues is not being resolved..

    I have performed the below steps.

    1.created a folder in svn
    2. checked out that empty folder in ‘D:\’ location
    3. Open the backup dmd which I shld put in svn
    4. do save as in ‘D:\’ location
    5. while performing tat I’m gettin a pop up “ADD Version control”
    (Generally wen in this step I giv Ys it ask fr loc n I’ll giv tat checkout location ‘D:\’ ) But this time if I give Ys it’s not giving me any loc to save.. I guess this is the problem.

    Once it is saved in svn. And as multiple person’s are checking out to wrk on it wen they do their changes and save it, it is not saving were as it is giving SAVE AS.

    That is the issue I’m facing.

    • thatjeffsmith

      I don’t have a quick answer, and I think I need backup. Can you post your question to our modeling forum? The developers moderate that fairly well and they’ll have a better idea than me. Here’s the link

      Just be sure to give a step-by-step description of your scenario. Also tell them what version of SDDM you are using, and if you are using a SVN server you setup yourself or one that you setup with SQL Developer.

  14. Avatar

    Hi Jeff,

    I’m facing problem in Data Modeler while saving the the DMD file to svn.

    I have created a folder to save my dmd in svn. I’ve checked the location in a path. After tat I opened the backup dmd which I should save. and clicked save as.

    I should get a add version control pop up were I should giv the path. I’m not getting tat option so I’m not able to save the backup dmd in svn.

  15. Avatar


    When i export an SQL query output (right click => export => format ‘text’), it produces two double quotes.

    In the query result pane in the lower part of SQL Developer worksheet, i see for example – “Jeff Smith”, but when i export into text format using the export wizard, I see it as “”Jeff Smith”” when i open in notepad (Notice the double quotes occur two times).

    Any ideas how to prevent it ?

    Good blog !


    • thatjeffsmith

      I don’t see this behavior…unless the data itself is stored as quoted. You can tell SQLDev not to quote the strings though. In the EXPORT dialog, set the LEFT ENCLOSURE and RIGHT ENCLOSURE values to ‘none’

  16. Avatar
    An issue related to SQL Plus and the command SET HTML MARKUP came up in the above thread.
    Doesn’t SQL Developer use SQL Plus to interpret the scripts ?
    Perhaps the 11g version of SQL Plus has a bug related to this command ?
    It appears the 12c version works fine.

    • thatjeffsmith

      Kinda, not really. SQL Developer has it’s own SQL*Plus library. We build it to emulate the actual SQL*Plus behavior.

  17. Avatar
    Subbu Mukkavilli Reply

    Hi Jeff,

    Excellent website and thanks for the information. I am wondering how to add a new MIME type in External Editor in SQLDeveloper. I am trying to view a .txt file which is stored as a BLOB object in the DB. I am unable to open it as this file type is not listed as one of the MIME types in the External Editor.


  18. Avatar

    Hi Jeff,
    Love the site. I’ve learned a lot and saved a ton of time by consulting this site.

    I have a question about data modeling of relationships using Oracle Data Modeler (Logical modeling).
    Best explained by example: I have three entities that are related through two 1-to-many relationships
    StudentEnrollment (PK is made up of two attributes – StudentId and SchoolYear)
    CourseOffering (PK is made up of two attributes – SubjectId and SchoolYear)
    StudentSchedule (PK is madeup of three attributes – StudentId, SubjectId and SchoolYear)
    There is an identifying relationship from StudentEnrollment to StudentSchedule (StudentEnrollment is parent entity)
    There is an identifying relationship from CourseOffering to StudentSchedule (CourseOffering is parent entity)

    When I model this scenario in Oracle Data modeler’s Logical model, I get four attributes in the primary key of StudentSchedule (StudentId, SchoolYear, SubjectId and SchoolYear1) instead of three.

    I’ve tried to use the properties editor for the relationships to modify the FK Attributes entries but they are locked.
    Is there a way to eliminate (or prevent creation of) the extra SchoolYear attribute in the child entity?


  19. Avatar

    Hi Jeff,
    I have a question on unit testing.
    We are planning to use SQL Developer’s unit test facility on our company project (~20 PL/SQL developers – unit and non-regression testing).
    If, for example, each developer produces test suites on his private UT repository, will we be able to export the suites and then import them into a common repository ?
    In the XML export files I see lots of ID fields, and am wondering if we could experience ID “collisions” between test suites exported from different source repositories.
    Any “best practices” suggestions would be appreciated !

    • thatjeffsmith

      If, for example, each developer produces test suites on his private UT repository, will we be able to export the suites and then import them into a common repository ?

      Can I ask why you are doing this? The repository was designed to be a central holding place for tests from multiple developers on ANY database. Why would a developer want to create a test in a local repository – I would think if you spent the time to build the test, it would always go to the central repo.

      But to answer your question, you could probably setup the sequences such that there wouldn’t be collisions. However, I’d try to use it as designed for now – for the maximum savings of work to get this going.

    • Avatar
      Guy Lecore

      Thanks Jeff, I hadn’t thought it through. Our idea was to store exported test suites with the PL/SQL code in our Subversion source code system. Regression testing would then involve compile -> import test suite -> execute test suite -> report results. The test suites would be in sync with the code version and the db.
      Sounds like we just need 2 repositories. One for all active development, and a second for regression testing, where overwriting tests (on import) is no problem.

  20. Avatar
    Ketil Smaaskjaer Reply

    I wonder if there are any plans on supporting XMI export from Oracle Data Modeler?
    I have a customer asking me to use Sparx Enterprise Architect for a modelling tool. I would rather like to be able to continue to use Oracle Data Modeler and export the models to Sparx EA.

    • thatjeffsmith

      Probably not. How good is their Data Dictionary import? You could continue to use SDDM to do the design, push it to their database, and then import from the database using their $$$ tool.

  21. Avatar

    Hi Jeff,

    Is there a config setting in SQL Developer where I can make forward-word, backward-word, treat underscore as part of a word?



    • thatjeffsmith

      Yes, although it’s not intuitively worded, this will do it.

      Tools > Preferences > Code Editor > Use Change of Case As Word Boundary

      select first_name || last_name name, (salary + (salary * (nvl(commission_pct, 0)))) take_home_pay from employees;

      With that off, I can ctrl+right over the entire word, and if I double click on take_home_pay, the entire word is selected.

    • Avatar
      Rey Lacson

      Spot on Jeff! That’s what I want! Thanks so much for the tip! You’re right… I never would’ve guessed that setting was what I wanted. 🙂

      Thanks again!

  22. Avatar
    Joe Alexander Reply

    Hi Jeff,

    I’m using SQL Developer Version and I’m continually being prompted to save the contents of my script editor windows whenever my PC switches focus to another windows application. I have to click cancel several times before it disappears since I’m not finished editing these scripts. Have I accidentally turned on an Autosave feature/preference or something? I’ve been through the preferences and can’t see anything helpful for this issue.

    • thatjeffsmith

      Joe, you win the prize for the being the first!

      That sounds terribly annoying.

      Do you have this enabled? Tools > Preferences > Environment > Automatically Reload Externally Modified Files

    • Avatar

      Thanks Jeff, that was it. That’ll teach me for clicking around without knowing what I’m doing 🙂 Strangely, at least to me is that the files that were open weren’t Externally Modified Files so I didn’t think this was going to be the issue, in fact they hadn’t even been saved due to being quick ad-hoc queries.

      Is it better to also turn off Tools > Preferences > Environment >Save All When Deactivating or Exiting since it seemed to be happening only when SQL Developer lost focus?

      On a happier note, following your blogged instructions to load SQL Developer 4.0 on OSX worked like a treat, using Mavericks and Java 8 JDK. Thanks again for the inspiration.

    • thatjeffsmith

      I’m glad that pref ‘fixed’ it for you, but I’m not totally convinced something fishy isn’t happening. I’ve heard one other person saying that SQLDev was ‘seeing’ files change even though they apparently weren’t…

      Thanks again for being the first brave soul to use this new page 🙂

    • Avatar

      Hey Jeff,

      I’m trying to create a job (in SQL Developer 4.x) that runs a stored procedure within a specific time on a 45 minute interval on business days. I noticed that this job disappears/gets deleted automatically after it’s first successful run. Note that “Auto-drop” is FALSE and “Restartable” is TRUE. Am I missing something? Is this a bug? (Oracle

      Part of the job creation’s PL/SQL:

      job_name => ‘”USER_SCHEMA”.”JOB_01″‘,
      job_type => ‘STORED_PROCEDURE’,
      job_action => ‘OUT_PROC’,

      start_date => TO_TIMESTAMP_TZ(‘2015-06-22 09:30:00.000000000 ASIA/JAPAN’,’YYYY-MM-DD HH24:MI:SS.FF TZR’),

      repeat_interval => ‘FREQ=MINUTELY;INTERVAL=45;BYDAY=MON,TUE,WED,THU,SUN;BYHOUR=9,10,11,12’,
      end_date => NULL,
      enabled => FALSE,
      auto_drop => FALSE,
      comments => ”,
      job_style => ‘REGULAR’);

      name => ‘”USER_SCHEMA”.”JOB_01″‘,
      attribute => ‘restartable’, value => TRUE);

      name => ‘”USER_SCHEMA”.”JOB_01″‘);


      Side note: Appreciate the effort you put in this site, it made me prefer SQL Developer from the command line! Wishing you all the best.

    • Avatar

      Oops sorry looks like i posted my previous post under a reply. My apologies!

Reply To Mauricio Cancel Reply