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!

Comments 4,146

  1. Back in April 2013 you did a blog post titled ‘Die! Or How to Cancel Queries in Oracle SQL Developer’ Very helpful, but SQL*Developer has gotten more mature and I wonder if it needs to be revisited. Back then you had a section titled ‘But Why Won’t My Cancel Work?’ where you install the OCIJDBC and configure by simply clicking on Tools -> Preferences -> Database (expand)->Advanced and then check the ‘Use OCI/Thick driver’. Did that work for all types of connection types (i.e.: Basic, TNS, etc.) or not? Now with 1.8.1 you can’t check the’ Use OCI/Thick drive’r unless you click on ‘Use Oracle Client’ and configure it. Or has the requirement overall changed. Please clarify. And thanks for all that you do for the SQL*Developer community!

  2. After disable SSH, NoSQL and Cloud extensions I’m not able to launch data modeler any longer.

    I’m getting the following errors:

    Product extension oracle.datamodeler could not be loaded. The product cannot start.
    Disabled extensions:
    oracle.sqldeveloper.onsd: Disabled by user
    oracle.sqldeveloper.cloud: Disabled by user
    oracle.sqldeveloper.ssh: Disabled by user
    oracle.datamodeler: Missing dependencies: oracle.sqldeveloper.ssh, oracle.sqldeveloper.cloud, oracle.sqldeveloper.onsd

    PRODUCT=Oracle SQL Developer Data Modeler
    VERSION=18.01000821035f
    VER=18.1.0
    VER_FULL=18.1.0.082.1035
    BUILD_LABEL=082.1035
    BUILD_NUM=082.1035

    Though I’ve downloaded the data modeler again, the error persists.
    Is there any way to re enable the extensions without using the IDE, so I can still using Oracle Data Modeler again?

    Thanks

    1. By the way, I also tried removing the AppData/Roaming, but still getting the error and not launching SQLData Modeler,

      Thanks

      1. Solved,

        Also removed the directory “Oracle SQL Developer Data Modeler” within AppData\Roaming dir.
        I didn’t remove that one. Sorry

        Thanks

      2. thatjeffsmith Post
        Author

        Sorry, you caught me on a plane, glad to hear you got it going.

        There might be a bug here, either it should work w/o those features, or we shouldn’t let you disable them.

  3. Hi Jeff,

    How to add PROFILE in the ddl to create user (or Create Like user) in SQL Developer 17.2?
    It includes, system privileges, and object privileges (when I check the box) but doesn’t include default profile.
    I want to select the same profile of the user which I am using to create alike.

    Thanks in advance.

    Shoaib

    1. thatjeffsmith Post
      Author
  4. Hello,

    When I’m trying to open a table with SQL Developer throw Oracle Client I get this error:

    An error was encountered performing the requested operation:

    ORA-01460: unimplemented or unreasonable conversion requested
    01460. 00000 – “unimplemented or unreasonable conversion requested”
    *Cause:
    *Action:
    Vendor code 1460

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author

        >>Oracle 10.2.0
        That’s your problem, we only support 11gR2 and higher.

        You can try an older copy of SQL Developer, maybe 3.0, if not, maybe 2.1.

  5. When I press up arrow in SQLcl to recall history, my terminal just beeps. I think it may not understand the encoding or emulation I’m using? Any suggestions? I’m on Mac High Sierra and using iTerm2. I’ve tried running it locally and on a Linux server with the same results.

    1. Status update… it appears to be related to “normal” vs “application mode” cursor keys. Programs like vim will change into application mode and then change back to normal mode when returning control to the terminal. SQLcl appears to require application mode cursor keys to work, but doesn’t set that up for you.

      I tried making a bash script to invoke it as follows:
      tput smkx
      ~/sqlcl/bin/sql “[email protected]
      tput rmkx

      That works fine until I try to use “edit” within SQLcl, which passes control over to vim, and unsets application mode cursor keys when it exits, at which point my up arrow is broken again.

      Bug?

  6. Sometimes I need to stop a query / procedure running in a sql-worksheet. Then I have to kill the session.
    It would be nice to see SID, SERIAL# of each worksheet session. Is there a way to get that information in the GUI ?

    1. thatjeffsmith Post
      Author

      By SQL it’s easy

      SELECT sid, serial#, 
        FROM V$SESSION
       WHERE AUDSID   = USERENV(
          'sessionid'
      );

      By GUI, the most straightforward way is the Tools – Monitor Sessions page. It has a ‘Kill Session’ feature built in as well.

      1. Hi Jeff,
        thanks for your answer. I want to describe the situation more detailed.

        Usually I have mor than one worksheet opened, e.g. ten worksheets. In one or two of that worksheets
        I get a problem and want to kill the sessions. Then it’s too late to start the sql query you suggested.
        Therefore it would be helpful to identify SID, SERIAL# inside the worksheet either in case a process is hanging.
        Another idea would be a configuration which allow to run automatically an sql-statement when I open a new worksheet. Then I can run your sql-stmt initially.

      2. thatjeffsmith Post
        Author

        The monitor session page shows you the current SQL, so you know for sure which one to kill.

        We have the login feature where you can have a query/script executed at connection time.

  7. Jeff,

    Is there any way to make a SQLDEV:LINK that
    a) drops you right into a table’s editable data grid and
    b) applies a filter to the grid

    I imaging a query result where clicking on a column would take you over to edit that particular row.

    I wish there was just some sort of documentation on creating links in SQLDeveloper!

    Thanks!

    1. thatjeffsmith Post
      Author

      Ummmm, no.

      I think for now it’s gonna be manually done where you copy the WHERE clause, ctrl+click into the table, and paste that into the Data tab filter input area…

  8. In SQL*Developer I can write a statement in a worksheet like ‘SELECT * FROM MY_TABLE’ and see the results in Query Results. I can then right click on a column header and hide various columns from the query results so that I can export the data, except those columns, to an file as insert statements. One of the columns that I want to export is an ‘activity date’ column where each row has a different date. But for the insert statements, I want to substitute ‘sysdate’ instead of the actual date from the record (so when inserted it will reflect the date/time that the statement was executed. But I don’t see in the export wizard where I can modify column values for the export. Is such possible?

    1. thatjeffsmith Post
      Author
      1. I want the insert script to reference ‘sysdate’ when it does the insert to get the current date/time when the script is run, not reference ‘sysdate’ for when the data is extracted. If I
        select sysdate activity_date from my_table;
        I get the date/time that the data is being exported. If I
        select ‘sysdate’ activity_date from my_table:
        I get in the script:
        Insert into MY_TABLE (ACTIVITY_DATE) values (‘sysdate’);
        which when executed results in:
        Error starting at line : 5 in command –
        Insert into MY_TABLE (ACTIVITY_DATE) values (‘sysdate’)
        Error report –
        ORA-01858: a non-numeric character was found where a numeric was
        expected
        So I need to ‘alias’ the data upon export, not when selected into the ‘Query Results’ panel. Or am I missing something?

      2. thatjeffsmith Post
        Author

        easier just to make the default value for your column definition to be SYSDATE, then when your script inserts a NULL for that value, SYSDATE will be used. will give you exactly what you want.

      3. I was also trying to avoid writing a query (or using query builder) that references seventy-nine columns and aliases one for ‘sysdate’ by using a ‘SELECT *’ and then hiding the two columns that shouldn’t be included in the export… while still being able to alias one column.

  9. I downloaded the new sql Developer version 18.1. The font is way too small in most of the areas that appear to be unchangeable, examples: titles, help screens, table names, etc. Is there any way to change these fonts? I know where to change the fonts for the code I type in myself.
    I reinstalled version 17.4 which is fine for me.

    1. thatjeffsmith Post
      Author
  10. I’m wondering if the following problem reproduces for anyone using SQLD 18.1.0.095.

    This morning when I logged into a database SQLDev prompted that my password would expire in 4 days. Fine, I clicked Ok and went about my task. Afterwards executing any sql statement returned ‘/ by zero’ and logging window shows the entries below. Changed my password in sqlplus in another session, came back to SQLDev, reconnected, and now all is well.

    Tim…
    =====
    SEVERE 139 19 oracle.dbtools.raptor.utils.Connections
    SEVERE 138 0 oracle.dbtools.raptor.utils.Connections / by zero

  11. I have version 17.2.0.188 running on windows 7 64bit. When I open SQL Developer, about 50 files open up in the tab. How do I restrict it to the last 5 ? Thanks.

    1. thatjeffsmith Post
      Author
  12. To save time!

    We usually sent many jobs simultaneously in order to update tables, create materialized views, recreate indexes, et al… and ,at the end of all these, with all that done, we generate hundreds of EOM end of month reports for management.
    (We had done this in previous Dev versions without problems).

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author

        We severely recommend doing as much as you can in a single instance of the tool, and if you need to run multiple copies, setup multiple installs.

        But you should be able to do everything you need in a single instance, including looking at > 1 object at once, running more than 1 query at once….

        I have no idea why you could run 3 copies but not 4, unless you’ve exhausted the memory on your machine.

  13. I am having an issue to open more than 1 instance of Dev 18.1 I used to open as many of 4 or 5 instances simultaneously on an oldier 4.20 version. Now, I got stuck constantly when opening the next ones (sqldeveloper64w.exe) in the SIGN IN screen and nothing in it functions, not even the X in the upper right corner. Please fix.

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *