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.

Go!

7,513 Comments

  1. Eric Morich Reply

    I want to use SQL Developer to access Hive2. Normal connection works fine but I’m facing connection issues when I have to use Kerberos. Does SQL Developer support Kerberos with Hive2 as indicated through optional connection parameters? Could not found any docs about this.

  2. As I try to import excel data into an existing database in SQl developer, I am stuck on Step 1. I followed what is in here: http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/ . I was successful last time but when I tried it for another excel file, it does not show me the Data Preview (which is Step 3 from the above link). When I press ” Next” nothing come up. I tried to modify the file name, file location and even restarted the PC, …still the same issue. any help is appreciated!.

  3. Hi Jeff

    Are there any plans to publish sql formatter that is used in sqldeveloper as java class so all could use it directly in database PL/SQL code. Eg. I would like to format dynamically generated sqls in PL/SQL.

    Kind Regards
    Tomasz Lesinski

  4. Hello Jeff,

    Thank you for your posts on Oracle SQL Developer Data Modeler.
    I have now downloaded the latest version and am trying to connect to SQL Server. Following your instructions, I have downloaded the JDBC drivers for SQL and included them in Tools > Preferences.

    Now, when I proceed to import Data Dictionary, I see the tab for SQL Server connection. I have entered the hostname, port and other information, however, it gives me an error when I test connection:

    “oracle.jdeveloper.db.ConnectionException: Test failed: Network error IOException: Invalid argument: create”.

    I must admit, I am also getting an error when I test proxy in Tools > Preferences.

    Kind Regards,
    Avnip

    • well that’s weird. what jTDS driver did you download? what version of sql server are you trying to connect to?

      can you ping that SS box from your machine?

      you can always do an RE from a DDL script – just generate it out of SSMS and then use the import feature in SQLDev

    • Thank you for the quick response Jeff.

      SQL Server is 2008 R2
      jTDS is 1.3.1 from sourceforge dot net
      I am able to connect to the SQL Server via SSMS

      Thank you for the tip regarding DDL scripts. Will try that next.

  5. I’ve been using SQLDeveloper for about 5 years and love it! I recently had to rebuild my development machine and decided to install the latest version (4.1.1.19.59). No matter what I do, I just can’t get rid of the FUZZY FONT that plagues the entire app; it’s not just the worksheet or query result panels!

    Can you please help me fix this? It’s the only app on my system that renders like this and since I use this program every day, almost all day, this is quite hard on my aging eyes!

    Thanks again for your work!
    Randy

  6. Hello,

    We are facing a weird issue and hope you can give some pointers on that.We have enabled sql profile for a particular sql and when I run it in SQL developer 4.0.3 it is not using that sql profile on the contrary we did see the profile was used in TOAD and sqlplus on the server.

    Thanks

    • i think profiles are case/text sensitive – are you running it exactly as you are in SQL*Plus?

      Also check the NLS parameters defined in preferences and make sure they match what you have going for your session in SQL*Plus

    • Thanks for your reply.Actually this is what I did ,I tried to change the name of the SQL profile from DEFAULT to TEST then did a ALTER SESSION SET SQLTUNE_CATEGORY = ‘TEST’; in SQL developer session after that when I run the query the sql_id does use the sql profile now but I am wondering why we need to do a alter session as it should be using the profile without that.

  7. How do I use Query Builder in SQL Developer 4.1 to create an Update query in 1 table? When I first started to use the app a menu would appear when dragging a table to the Query builder. That menu no longer shows.

    Thanks

    Dave

    • not sure, to the best of my knowledge, we’ve only ever supported SELECT statements in the query builder

      you could use it to build a select, then go to the worksheet and remove the select bit and add in an UPDATE or DELETE, keeping the WHERE

  8. Hi
    Have access to a oracle role[ not dba] which has access to some v$ views (inc v_$session)
    however these do not work in sqldeveloper (cant see v$session but can look at v$session_longops which is granted to public). Does role access work in sqldeveloper ? Im keen to look at monitor sql tool.

    • ‘monitor sql tool’ – requires more than just access to v$session

      monitor sql under tools menu is Real Time SQL Monitoring, is that what you want, or do you mean the Sessions report under Tools menu?

    • thks for reply.
      yes tools-> mon sql and monitor session.
      sry the v$session is only a example, the role has access to +100 views v$sql sqlarea lock sort_usage process + some dba_ tables
      but only longsops works which is ganted to public.

  9. Hi,
    I am using Oracle SQL Developer 4.0 tool. Though the DBA has given access to a specific user to run the Explain plan statements, I am not able to see the latest plan statements in the plan_table.
    I run the Explain plan for <>
    I get a message that Plan for succeeded.
    Then I query the plan table, I see the old values which was there before i ran new query.

    Pl help if any setting exists to refresh values.

    Thanks

    • why run explain plan for when you can just use the Explain Plan and AutoTrace buttons in the sql worksheet toolbar?

      what is your query, it’s probably missing a where clause.

      you can also try ‘show plan’

  10. Hi, I have a simple question how to display details of employees who are receiving salary more than ‘ALEN’ employee using emp table in scott i think it is self join


    • SELECT employee_id
      , first_name
      , last_name
      , salary
      FROM hr.employees
      WHERE salary >
      (SELECT salary FROM hr.employees WHERE employee_id = 187
      )
      ORDER BY salary DESC;

      Assuming that ALEN is employee_id 187…this is just one way to do it.

      I hope that I get an A on your homework.

  11. Hey there… know you are quite busy this month so no rush on this. I am not seeing the bind variable output within SQL Developer but CAN see it on SQL Plus using the same script.

    Wondering if it is a preference that needs to be set.

    —- SQL Developer (using F5, RUN script)

    SET serveroutput ON
    VARIABLE b_basic_percent NUMBER
    VARIABLE b_pf_percent NUMBER
    DECLARE
    v_today DATE:=SYSDATE;
    v_tomorrow v_today%TYPE;
    BEGIN
    v_tomorrow:=v_today + 1;
    DBMS_OUTPUT.PUT_LINE(‘Hello World’);
    DBMS_OUTPUT.PUT_LINE (‘Today is : ‘|| v_today);
    DBMS_OUTPUT.PUT_LINE (‘Tomorrow is : ‘||v_tomorrow);
    :b_basic_percent:=45;
    :b_pf_percent:=12;
    END;
    /
    print b_basic_percent
    print b_pf_percent

    PL/SQL procedure successfully completed.

    Hello World
    Today is : 25-SEP-15
    Tomorrow is : 26-SEP-15

    B_BASIC_PERCENT
    ——

    B_PF_PERCENT
    ——

    —-SQL Plus (same script and same machine)

    Connected.
    SQL> SET serveroutput ON
    SQL> VARIABLE b_basic_percent NUMBER
    SQL> VARIABLE b_pf_percent NUMBER
    SQL> DECLARE
    2 v_today DATE:=SYSDATE;
    3 v_tomorrow v_today%TYPE;
    4 BEGIN
    5 v_tomorrow:=v_today + 1;
    6 DBMS_OUTPUT.PUT_LINE(‘Hello World’);
    7 DBMS_OUTPUT.PUT_LINE (‘Today is : ‘|| v_today);
    8 DBMS_OUTPUT.PUT_LINE (‘Tomorrow is : ‘||v_tomorrow);
    9 :b_basic_percent:=45;
    10 :b_pf_percent:=12;
    11 END;
    12 /
    Hello World
    Today is : 25-SEP-15
    Tomorrow is : 26-SEP-15

    PL/SQL procedure successfully completed.

    SQL> print b_basic_percent;

    B_BASIC_PERCENT
    —————
    45

    SQL> print b_pf_percent;

    B_PF_PERCENT
    ————
    12

    SQL>

    Any clues?

    ~Thanks, Melody

    • Never too busy for you!

      All I have to say at the moment though is that it’s working for me. What do you see on the script output tab after running this?

    • I am not saavy enough to be able to insert a screenshot here. Will do on FB.

      I need some of your mojo!

  12. Anne Price Reply

    Jeff,
    Is there a way to transfer the sql developer data modelling project files to another computer? Can i just copy the projects folder , is that enough?

    thanks a bunch!
    Anne

  13. I am using sql developer 4.1.1.19 and am unable to import ORDS 2.0.9 that was created with sqldeveloper 4.0.0.12.84

  14. Troy Tschauner Reply

    Jeff, is there a way …
    Frequently, rather than building a query from scratch or using query builder, I will just click on the table or view and then click on the “Data” tab. Then I proceed to filter, sort and even hide columns. I get what I want quickly and easily. I would like, however, to see and have access to the query which produced that result set. I want to copy/paste it into a worksheet to make some additional tweaks and have it placed in my SQL history. Your thoughts?

    • hiding columns is done visually, not via SQL…filtering, depending on how you do it, is done client side.

      So quick answer: that won’t work….

      I don’t have a slow answer that will give you what you want, at least not today.

    • Troy Tschauner

      I guess this is a good spot for an enhancement request. Maybe a button or action item which lives under the “Data” tab to “Show SQL”. I just don’t see it as that big of a problem for SQL Dev to see which columns I have displayed rather than “*”, see my filters and sorts and then generate a SQL statement. When I first click on the “Data” tab, obviously the statement would be “Select * from My_Table_or_View”. As I filter, sort or otherwise modify the displayed result set (even if it is on the client-side) the dynamically generated “Show SQL” statement would be reflect the modifications.

    • right, but we’d be lying – b/c we do select * whether we’re showing the column or not. now if you wanted a context menu item, save display as query/report, that would dynamically build the SQL to give you what you see there, then that would be cool

    • Troy Tschauner

      Yes, you’ve got idea.
      That would be cool.
      If you are not doing anything this weekend, could you please develop that for us?
      Thanks.

  15. I am new to SQL Dev Data Modler and have an issue that may be me or the tool. I reverse-engineered an 11G database to version 4.1 data modeler. Views are validated, yet I cannot edit them or change column order. View Properties -> General -> Query Builder shows no columns or query. If I preview DDL, I see the current DDL. Where do I edit it?

    Thanks Master Jeff

    • I did (at least the tool says I did) and said so in the original post. The display no longer showed warning icons. How else do I know they have not been validated? Still nothing.

    • sorry, didn’t see where you had validated, and I think i misspoke, you need to parse them, not validate them. Try the ‘parse older style views.’

    • Thanks. Getting closer. The Parse SQL does nothing. Testing the Query, though gives me some insight. The view accesses through a DB Link. My user can execute the view just fine, but not the query directly. I get the 02019. 00000 – “connection description for remote database not found” from the tool and a SQL session.

  16. Are SQL Developer reports compatible with Microsoft SQL Server? Even the simplest query won’t run. It works fine with report type table, but when changing to a chart I get an error that I think indicates that SQL Server did not like the query. It is like SQL Developer is changing the query when it runs a chart versus a table.

    Here is a query:
    select ‘ ‘, highland.dbo.arcnamp.ycompn, 14
    from highland.dbo.arcnamp

    I know the query doesn’t make sense purpose wise, but I wanted it to be as simple as possible because the error says “Incorrect syntax near the keyword ‘Select’, Vendor Code 156”.

    I wish every database I worked with was Oracle, but sometimes I don’t have a choice.

    I am also fine if your answer is that charts only work with Oracle. I just want to know for sure, I hate giving up unless I know for sure something just can’t work.

    BTW it is because of your blogs and emails that I am an Oracle convert. Between SQL Developer and APEX who could resist Oracle.

    Thanks for your help now and over the past couple of years in your writings.

    • Not really, so don’t beat yourself up anymore trying.

      I wish every database you worked with was Oracle too!

  17. Mike Johnson Reply

    trying to use git with ssh with keys

    $> tail ~/.sqldeveloper/system4.1.0.19.07/o.jdeveloper.git.12.2.1.0.42.150416.1320/vcs.log
    [20:34:17.758 CDT 23.09.15] git ls-remote –heads ssh://[email protected]/nfs/infsrv_shared/git-repo/test.git
    [20:34:17.984 CDT 23.09.15] ssh://[email protected]/nfs/infsrv_shared/git-repo/test.git: java.lang.ArrayIndexOutOfBoundsException

    from command line
    $> git ls-remote –heads ssh://johmicd@ssh-1+banqualjss.its.bethel.edu/nfs/infsrv_shared/git-repo/banner_home.git
    Pseudo-terminal will not be allocated because stdin is not a terminal.
    Killed by signal 1.
    f428c73526fdbe1a5e378e366c2a19ddef4fefa7 refs/heads/master

    Any ideas of other logs to look for more info on.?

    • Mike Johnson

      sorry my last example was not right it should have been

      from command line
      $> git ls-remote –heads ssh://[email protected]/nfs/infsrv_shared/git-repo/test.git
      Pseudo-terminal will not be allocated because stdin is not a terminal.
      Killed by signal 1.
      f428c73526fdbe1a5e378e366c2a19ddef4fefa7 refs/heads/master

  18. I have created a few relational models. I see some objects are automatically created under logical model. I tried to delete them. But when I save, close and open the model again I see those object under Logical model. I can create a confusion with other users that I am created a logical model too. Is there anyway I can delete the logical model?

  19. Hi Jeff,
    We are getting an error ‘ Unable to launch the JVM located at *\jdk\jre\bin\server\jvm.dll’.

    Can you please help

    -Punit

  20. Hi,

    Many consider the context diagram (data flow diagram – level 0) as the one that contains system(s) or application(s) along with external entities that interact with them.
    I can’t see anything within the DFD tool to represent the system. Is the concept within Oracle a bit different and it assumes to start from what some others call DFD – level 1?

    Thank you!

  21. Hi Jeff,

    I have SQL Developer 4.1 and when I first started dragging tables into Query Builder a window popped up asking what type of query to create. I’ve since lost this window. How do I get it back or how can I choose the query type?

    Thanks, Dave

    • The Query Builder window is still there. When I drag a table into it another window would open asking what type of query to create (e.g. insert, update, etc.) That window no longer opens. So how/where do I choose the type of query?

    • if > 1 table, it defaults to SELECT

      DELETE, INSERT, UPDATE for > 1 table, doesn’t make sense

    • I want to update a field in Table1 with the contents of a field in Table2 where Table 1 and Table2 are joined.

    • How do I use Query Builder to create an Update query in 1 table?

  22. I am using SQLDeveloper v4.0.3.16, I wanted to execute below command automatically as and when I login to a particular DB. Is it possible?
    ALTER SESSION SET CURRENT_SCHEMA =;

    Thanks
    Bala

    • configure your login script

      have it do something like this:

      declare
      statement varchar2(200) := 'ALTER SESSION SET CURRENT_SCHEMA= BASIC_PRIVS';
      name varchar2(200);
      begin
      select ora_database_name into name from dual;
      if name = 'ORCL' then
      EXECUTE IMMEDIATE statement;
      end if;
      end;
      /

      Use a CASE statement to make the CURRENT_SCHEMA value dynamic based on the database you’re working with…

  23. Eric Rasinski Reply

    Jeff,
    I just downloaded Data Modeler 4.1.1.888 onto my MacBook Pro, opened a model and cannot CMD-Click on anything. Can’t get contextual menus, or add new items in the Design browser tree.

    Any thoughts?

    Can’t find anyone here with the same issue.

    Going back to 4.0.3.853 and everything works as advertised.

    Thanks.

  24. Sorry, to bother you with this simple task but I can not find a solution. I am having trouble finding an answer connecting “as sysdba” on the command line with sqlcl (SQLcl: Release 4.2.0.15.257.0801 RC). no matter what I try I can not get it to parse correctly.

    sql sys as sysdba@orcl

    SQLcl: Release 4.2.0.15.257.0801 RC on Fri Sep 18 11:47:45 2015

    Copyright (c) 1982, 2015, Oracle. All rights reserved.

    Password? (**********?)
    Database? (”?)
    USER = sys
    URL = jdbc:oracle:thin:@localhost:1521/orcl
    Error Message = IO Error: The Network Adapter could not establish the connection
    Username? (RETRYING) (‘sys’?)

    or

    sql sys@orcl as sysdba

    SQLcl: Release 4.2.0.15.257.0801 RC on Fri Sep 18 11:48:29 2015

    Copyright (c) 1982, 2015, Oracle. All rights reserved.

    Password? (**********?)
    USER = sys
    URL = jdbc:oracle:thin:@orcl
    Error Message = IO Error: Unknown host specified
    USER = sys
    URL = jdbc:oracle:thin:@orcl:1521/orcl
    Error Message = IO Error: Unknown host specified
    Username? (RETRYING) (‘sys’?)

    but it work if I do it interactive.

    Username? (RETRYING) (‘sys’?) SYS as sysdba
    Password? (RETRYING) (**********?) ***********
    Database? (RETRYING) (‘orcl’?) orcl
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    • as sysdba goes onto the end of the connect string, not the beginning

      ┌─[12:59:19]─[wvu1999]─[MacBook-Air-Smith]:/Applications/SQLDev411.app/Contents/Resources/sqldeveloper/sqldeveloper/bin$
      └─>./sql sys@\localhost:1521/orcl as sysdba

      SQLcl: Release 4.1.0 Release Candidate on Fri Sep 18 12:59:59 2015

      Copyright (c) 1982, 2015, Oracle. All rights reserved.

      Password? (**********?) ******
      Connected to:
      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

      SQL> select user from dual;

      USER
      ------------------------------
      SYS

      SQL>

    • Mike Johnson

      ok thank so much it is now working! don’t know why the second one was not all morning. I appreciate your helpfulness.

  25. Hi,
    I have SqlDevelopper installed in my Mac. However, I don’t know how to connect it to an Oracle database. Do I need to install VirtualBox? Is there any database that can be used in Mac without needin VirtualBox?

    Thanks so much!

Write A Comment