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

    I am new to oracle VM, installed succesfully and also opened the terminal
    [email protected]: and clicked sql developer and connected but i got ORA12505 error. how to resolve this and connect to sql developer
    please suggest

  2. Avatar

    Hi

    I am unable to login into oracle sql developer. ORA 12505.

    I an new to oracle VM , installed successfully any information can you provide so that i would login in to Sql developer.

    how should i connect succesfully

    ~Mithlesh

  3. Avatar

    Hi

    I am unable to login into oracle sql developer. ORA 12505.

    I an new to oracle VM , installed successfully any information can you provide so that i would login in to Sql developer.

    how should i connect succesfully

    [email protected]

    ~Mithlesh

  4. Avatar
    Telugubaludu Reply

    We have VM (VDI) desktops, and have Oracle 11g client with Java 6 installed on our desktops. Recently our SQL Developer 4.0.3 stared crashing consistently running SQL Scripts, or just hovering over connections, or icons, or sometimes just doing nothing. Our team reinstalled 4.1.3 version as well, but still the same issue.

    Any thoughts?

    Thank you.

    • thatjeffsmith

      crashing how? please share any error message/core dumps you get

      413 won’t run with Java 6, so I’m assuming Java 7 is on there too

      how much memory does each desktop get?

    • Avatar
      Telugubaludu

      It doesnt leave any error messages on screen. My admin says SQLDeveloper comes with its own JDK (supported version) within the package, so no need of JDK8 installed on System explicitly.

      My desktop got 8 GB.

      This only happened with bunch of users at the same time a week ago. We are all developers working on some framework that supports only Java 6. FYI.

    • Avatar
      Telugubaludu

      Thank you, I will inform this to Admin group and try. Thank you for your time.

  5. Avatar

    Hi Jeff

    I am not sure can you help me but i want to ask you did you experience similar problem. I am using Centos7, SQLDeveloper Version 4.1.3.20, Java7.

    When i have stable internet connection everything works fine, but when i work from home where “something” interrupting my internet, sqldeveloper connection drops and then i am in problem, only way to restart SQLDeveloper is xkill command 🙁 . Trying to reconnect or refresh tree will not help me. If i leave it for like 10 or 15 min it will disconnect but is to long for wait.

    Sorry if my Eng is not best one. Thank you for your time, all best.

  6. Avatar

    How to run a script with SQLCL and exit on completion?

    SQLCL allows to execute an sql script from the command prompt, avoiding the interactive procedure of login, execute script and exit.
    After the script execution has completed, SQLCL doesn’t exit automatically.

    In SQLPlus there are two way’s to do this:
    1) put an exit (or quit) statement at the end of the sql script
    or call SQLPlus this way
    2) exit | sqlplus -L -S admin/[email protected] @clean_up.sql
    – or for windows: echo exit | sqlplus -L -S admin/[email protected] @clean_up.sql

    The second option doesn’t work for SQLCL. Is there a way to achieve the same in SQLCL, thus without exit statement within the script?

  7. Avatar
    Mark Lancaster Reply

    Hi Jeff

    When you export DDL scripts into folders in SQL Developer 4.1.3, packages and package bodies are exported with a “.sql” suffix. When you open the file to edit in SQL Developer, it displays it in the SQL editor, rather than the PL/SQL editor.

    Is it possible to configure exports to specify suffixes based on object type?
    e.g. package => “.pks”, package body => “.pkb”

    Also, directories and object names are always exported in upper case, e.g.
    @/Users/mark/TABLES/FLEXFIELDS.sql
    @/Users/mark/PACKAGES/ADMIN_PORTAL.sql
    @/Users/mark/PACKAGES/ALARM_CENTRAL.sql
    @/Users/mark/PACKAGE_BODIES/ADMIN_PORTAL.sql
    @/Users/mark/PACKAGE_BODIES/ALARM_CENTRAL.sql

    Is it possible to include a configuration option for lower-case file names?

    And finally, is it possible to use relative path names in the control script, so for the example above the output would be:
    @./tables/flexfields.sql
    @./packages/admin_portal.sql
    @./packages/alarm_central.sql
    @./package_bodies/admin_portal.sql
    @./package_bodies/alarm_central.sql

    Regards

    Mark

  8. Avatar

    Hello,

    We have oracle 11G installed and in our recent projects we had issues with some of the DB objects getting invalid especially views. The queries inside the views are referencing tables/views between the schemas on the same oracle instace.

    Using SQL developer when we right click on the object and compile, it said it was success but querying the SQL inside the views showed invalid identifier issue. These views originated in 8i->9i and now in 11G

    We spent a lot of time trying to figure out what is going on without much success. Finally, we were able to compile successfully by formatting the entire SQL for the view and replacing it.

    But doing this way resulted in extra space getting introduced on the column names and it took a lot of time to nail that down.

    I have a two part question,
    1. Why and when does the DB views goes invalid?
    2. Are there any issues using SQL formatter in SQL developer (4.0.2.15)?

    Any insights on this would be really helpful.

    Thanks,
    Jagan

    • thatjeffsmith

      Without seeing your code, I could only guess.

      But whitespace shouldn’t matter.

      Are they any issues with the formatter? Sure. That would cause your issue? Not that I know of.

  9. Avatar
    Aníbal Gattás Reply

    Hi Jeff, I’m facing an issue with SQL Developer (versions 4.1.2 and 4.1.3), using JDK 1.8.0_65. I unzipped the program, searched for the JDK directory and then opened it. I tried to create a new DB connection, but when hitting the OK button there (clicking in the new icon -> Database tier -> Database connections) nothing happens and the DB connection creation screen doesn’t appear. I also tried copying the connections.xml file from another machine into the ..\AppData\Roaming\… directory, but the connections are not being displayed either. Any ideas on how to solve this or what else can I do to solve it? Thanks in advance for your reply.

    • thatjeffsmith

      close sqldev

      go to your appData\Roaming\4.1.3 folder – nuke it

      restart sqldev

      if that doesn’t fix it, then delete the sqldev folder itself and ‘install’ it again

    • Avatar
      Aníbal

      Thanks for your reply Jeff. I did as you told me but had no luck. I even downloaded a previous version (4.1.1), installed it and tried with it but got the same error. My solution was to download SQL Dev with JDK included, then, after unzipping and running it, I was able to see the connections icons in the DB connection panel and could import my previous connections. I suspect then that this is, somehow, related to the JDK version I have or the env configuration. Thanks again. Greetings.

  10. Avatar
    Trey Cashon Reply

    Lazy coder question here.. Is it possible to configure SQLDeveloper to auto-close opening quotes, parathensis, brackets etc? A google search has not turned up anything of substance yet.

    This would be done similar to what something like Notepad++ does when you are writing HTML tags etc.

  11. Avatar
    Chris Fischer Reply

    I tried to post a new report to the SQL Developer Exchange site today. I got an error “ORA-01400: cannot insert NULL into (“DBTOOLS”.”XML_REPORT”.”CREATED_BY”)
    Unable to process row of table XML_REPORT.” Is there an official means of reporting this?

  12. Avatar

    Hello Jeff,

    i’m using sql developer 4.1.3.20.78, the output of the select query is clumsy as sql command prompt

    i would like to see in rows and columns order what should i do..?

  13. Avatar
    Amin Adatia Reply

    The Download page says => Version 4.1.3.901, Updated December 21, 2015
    The About Page says =>
    Oracle SQL Developer Data Modeler 4.1.2.895
    Version 4.1.2.895

  14. Avatar
    Nobert Kiesel Reply

    Another sqlcl bug: “describe user_objects;” gives

    Object Not Found: ribe user_objects
    Usage: DESCRIBE [schema.]object[@db_link]

    “desc user_objects;” works

  15. Avatar

    When I try to build a User Defined Report with SQL Developer version 4.1.3.20.78 the application freezes. The only way out is to kill it in the Windows 8 Task Manager. Things worked fine with with version 3.2.20.10.

    I have tried a few cleanup and reinstall operations to resolve the issue, nothing works. I have tested 4.1.3.20.78 with and without the UserReports.xml file left over from 3.2.20.10. In either case once I try to add a new report or modify an existing one, I freeze up when I click the Apply button in the Create Report pane.

    • Avatar

      If there is currently no UserReports.xml and I try to create a reports, when I go to save it, I get the freeze and the UserReports.xml is created with a 0 (zero) length file.

      If I have a UserReports.xml that was created with a v3 SQL Developer and has actual reports defined, then when I go to add or change a report with v4 SQL Developer I get a freeze when I go to save it and the UserReports.xml file again has a 0 (zero) length.

      I’ve looked for a method to debug this before my first posting, and the method that I found where you started it from the command line and from the bin directory showed no extra debugging related to what happened and/or lead up to the freeze.

      I’m totally hosed here, and have to use v3 to create reports, then can run them in the v4 as long as I don’t make changes.

  16. Avatar
    Brian Rollins Reply

    Hi Jeff:
    Thank you for the work you do to maintain this blog.
    I have a couple questions for you… I really like version 4 of SQL Developer but have experienced 2 frustrating things.
    1: I have Completion Insight set to change code to “Upper Keywords / Lower Identifiers”. However, it periodically will reset itself to “Lower Case”. I don’t know if this is bug or if there is something I can to to force it to retain my setting.
    2: I would really, really, really like to be able to tell SQL Developer what I consider a keyword to be. There are certain statements that it doesn’t recognize as being a keyword and will convert to lower case. For example , I want “TO_CHAR” to be upper case. Is there a way to edit the dictionary?

    Thanks in advance!
    Brian Rollins
    Anal Retentive
    Type A, OCD Certified
    Oracle Database Developer

    • thatjeffsmith

      Exactly what version of 4 are you running? The first thing would be a bug.

      to_char is a public synonym to the SYS function to_char()…so that gets resolved as an IDENTIFIER. If you want it upper cased, you can do that.

      Now, to answer your question, can you edit the list? No. But in version 4.Next, the list is being updated to pretty much pull every single word we can find out of the Oracle Database Docs, so it should be pretty complete.

    • Avatar
      Brian Rollins

      Hi Jeff,
      Thank you for the quick reply.
      To answer your question, I am using version 4.1.1.19
      It also lists a Build MAIN-19.59
      I hope this helps.

      I haven’t been able to determine what makes it reset, but it does so within the same session usually about once per day.

      I’ll look forward to the expanded “dictionary” in your future release.
      Thanks again and keep up the great work!

      Brian Rollins

  17. Avatar

    Hi Jeff,

    my previous comment was really too vague to be considered, but now I can be more precise. The issue is related with tables created from views using UNION. You can replicate the issue in this way (e.g. on the scott schema):

    create view v_emp_10_20 as
    select *
    from emp
    where deptno = 10
    union
    select *
    from emp
    where deptno = 20;

    create table emp_10_20 as
    select *
    from v_emp_10_20;

    Now if you try to update the table emp_10_20 in the data view of SQL Developer you will not be able to do it.

    According to the Oracle Support this is related to a fairly old (but still unsolved) bug in the DBMS (Bug 7338622). In practice the data in the ALL_UPDATABLE_COLUMNS view are incorrect, when the table is created in that manner.

    Nevertheless, maybe Developer could avoid checking the “updatability” of columns, which seems to be a useless info in the case of tables.

    Thanks for all useful infos provided in this blog!

  18. Avatar
    John Thomas Reply

    Jeff,

    Is there something up with your style sheet?

    In the Search box on your page, text entered is white on a white background.

    Regards,

    JT

  19. Avatar
    Okonita Okonita Reply

    Hi Jeff,

    First off – I hear great things about this site – very experience IT profs that I work with says this is a very helpful site.

    I have a question about SQL developer – I have a need to connect to DB2 for data extract or just to browse DB2 tables. Is this possible from to do this from SQL Developer? If so, how can this be setup? If not what are my options or alternatives to access DB2 from SQL Developer?

    Any and all assistance will be highly appreciated.

    Thanks

    Okonita

    • thatjeffsmith

      you need to get the jdbc driver from IBM – they require you have a license/account with them – and then you can follow the directions here

  20. Avatar

    I have version 4.1.1.19 and I and wanting to display the numbers in my results when I export the data to text file. How do I do this? I know in plsql/dev I would right click for and excel file and the row numbers would be automatically included in the results.

    • thatjeffsmith

      There’s no way to include row numbers, unless you include row numbers (SELECT rownum, …) in your query.

  21. Avatar
    Dingle Berry Reply

    Looked; could not find answer.

    I am looking for a replacement to scripts I have the use listagg against the DBA views to build pipe separated value (PSV) exports with headers.

    Dos the CLI suport formatting ‘hints’ /*CSV*/ ?

    What about PSV?

    Headers?

    Thanks! I enjoy your site.

    Rich

    • thatjeffsmith

      does it support the formatter comments, such as /*csv*/ – YES

      does it do pipe delimited output, – YES

    • thatjeffsmith

      I live in Cary – if there’s a few of you over there at the hospital, more than happy to come over and do a free training if you’d like.

  22. Avatar

    I opened a binary file as script once (an xls sheet), and the SQL Developer crashed.

    Now, every time I try to open a script file, its crashes again.

    Where do I can see some sort of cache I believe it uses to open files, to clean it and then use again this feature ? It is installed in Mac OS X Yosemite, and works fine.

    Thanks !

    • Avatar
      Julián

      Exactly, when I try to open a SQL script it crashes. I’m using version 4.0.2 in OS X, and worked fine until once day I opened a binary file by mistake…

    • thatjeffsmith

      weird

      remove the app

      go get 4.1.3

      when it asks if you want to import your settings from 4.0.2, say ‘no’

      You should still have the same sql history. you can export your connections from 402 and import them to 413 if you’d like

    • Avatar
      Julián

      I installed version 4.1.3 and it worked as you said, I wonder if was my mistake or not.

      Thanks !

    • thatjeffsmith

      no, i don’t think so

      there’s a file in the settings you probably could have deleted to reset whatever we did when you opened that file, but the benefit of new/features/bug fixes over finding that 1 file in the haystack in the old version will always win out in my book

  23. Avatar
    Norbert Kiesel Reply

    Question regarding sqlcl: I get errors for

    WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

    that are accepted by sqlplus (and seem to be correct according to the documentation). Any idea why sqlcl barfs for these? Many other `WHENEVER` formats are accepted by sqlcl.

    • Avatar
      Norbert Kiesel

      Happy to give something back. I always thought it was high time for Oracle to improve on the stone-age sqlplus, so very happy to see this.

      Is there a way to subscribe to new releases?

    • thatjeffsmith

      no, but do you do Twitter? Follow me or @krisrice for sqlcl updates.

      otherwise we push them every 6-10 weeks, at least here recently

  24. Avatar

    HI jeff smith

    actually I am trying to run a .sql file (which have an insert command) in batch file which should add a record in oracle sql developer which is in a remote server.I have tried by creating path environment variable(D:\sqldeveloper.zip\sqldeveloper\sqldeveloper\bin), but it just opens the application but it is not running the script.please help me on this..

    thank you.

    • thatjeffsmith

      you can’t feed connections and scripts to SQL Developer via command-line

      you can however feed it to SQLcl, which you’ll find in the \bin directory of version 4.1.2 and version 4.1.3 of SQL Developer. SQLcl is a new command-line interface, in the spirit of SQL*Plus but with many of the popular features in SQL Developer

Write A Comment