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 3,753

  1. I am trying to connect to github enterprise from the Team..git.. menu and if I want to connect to public github with username / password no problem. Internally with github enterprise this doesn’t work as it is expecting sso authentication. Looking at another tool that is working it is using OAuth for authentication, does SQL Developer support this and if so what are the details for doing so. Can’t find anything on this topic for simply connecting the client to an internal repository.

  2. Is there any way to share a connection.xml file? I work off my laptop sometimes and sometimes off my desktop, they are both on the LAN. I’d like to be able to put my connections file on my user drive so that when I don’t have to maintain 2 copies of them. Any ideas?

  3. Hi Jeff,

    We are using SDDM (v17.3) and the Reports functionality to generate Excel worksheets to maintain/update table and column comments. That works fine but we also like to do the same with views, but for “Table Views” there is no “Available Collections” on column level (as for “Tables” having an available collection named “Columns”).

    Is there a reason for this or can this functionality be added to a later version of SDDM?

    Thanks
    Jo

    1. thatjeffsmith Post
      Author
  4. Is there a UI preference that controls window handling such that everything is not tab based? Interested in having new windows open up in more a MDI style instead of the everything in tabs concept.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      I right clicked on a document in sqldev, a SQL Worksheet. Chose ‘close.’ It only closed that worksheet, and no others. I’m going to need more information from you to see if there’s a problem.

      1. (reposted changing ” text to ‘[…]’)

        You are correct. A false positive working with only two windows. Working with a colleague it appears there is a different close window interaction when [Close all worksheets on disconnect] is checked versus unchecked.

        How we tested:
        Database:Worksheet:Open a worksheet on connect = checked
        Database:Worksheet:New worksheet to use unshared connection = checked
        Database:Worksheet:Close all worksheets on disconnect = checked
        Database:Worksheet:Prompt for Save file on close = checked
        Create/save an Oracle connection using jdbc, do not specify password, [Save Password] = unchecked

        When opening a SQL window the method is right click connection in and click [Open SQL Worksheet]
        When SQL Worksheet opens type something to change window status to modified; ie. select * from dual
        When closing a window the ‘x’ on right side of tab is used.
        After 1st connect an initial SQL Worksheet opens, open 2 additional SQL Worksheets; remember to modify each SQL Worksheet window.
        =====
        Step 1: open connection, initial prompt for password.
        Step 2: open 2 additional SQL worksheets, no additional prompt for password (reminder to modify each window).
        Step 3: close the 3rd window. This will prompt for [Save changes], select [No], SQL worksheet closes, [Save changes] re-prompts, select [No], done
        (Main connection seems to be closed at this point.)
        Step 4: open new SQL Worksheet (3rd), will be prompted for password.
        Step 5: open new SQL Worksheet (4th), will not be prompted for password.

        Change:
        Database:Worksheet:Close all worksheets on disconnect = unchecked
        (restart SQL Developer)
        Retest from Step 1, now [Save changes] will behave as expected, main connection still appears to close on window close.

        Unexpected behavior:
        Closing window seems to close main connection or clears password from initial connect.
        When [Database:Worksheet:Close all worksheets on disconnect = checked] is in effect, [Save changes] reprompts using [No]

        For us this reproduced on Windows and Mac using SQL Developer 17.3.1.279.

        Tim…

        1. thatjeffsmith Post
          Author

          Database:Worksheet:New worksheet to use unshared connection = checked
          Database:Worksheet:Close all worksheets on disconnect = checked

          So if you toggle either of those above, it works as expected?

          1. Results following your thinking…
            —–
            Database:Worksheet:New worksheet to use unshared connection = unchecked
            Database:Worksheet:Close all worksheets on disconnect = checked
            or
            Database:Worksheet:New worksheet to use unshared connection = unchecked
            Database:Worksheet:Close all worksheets on disconnect = unchecked

            No double prompt for [Save Changes]+[No]
            No prompt for password opening new SQL Worksheet after closing a SQL Worksheet.
            This behaves has expected.
            —–
            Database:Worksheet:New worksheet to use unshared connection = checked
            Database:Worksheet:Close all worksheets on disconnect = unchecked

            No double prompt for [Save Changes]+[No]
            ***Prompts for password opening new SQL Worksheet after closing a SQL Worksheet.
            —–
            Database:Worksheet:New worksheet to use unshared connection = checked
            Database:Worksheet:Close all worksheets on disconnect = checked

            ***Double prompts for [Save Changes]+[No]
            ***Prompts for password opening new SQL Worksheet after closing a SQL Worksheet.
            —–

            Tim…

  5. How can you change the connection that is assigned to a “UNIT TEST” or a Suite of unit tests?

    When I first create a unit test in SQL Developer, it assigns a database connection to it, which is displayed in the upper right corner in a drop-down menu. However, when you try to change that connection, there does not appear to be anyway to save that change. I have tried committing my change to the unit test repository, but every time you reopen the same unit test, it will always revert back to the original connection.

    Thanks for any help you can provide.

  6. Hello. We’re getting Java exceptions in the log from trying to edit a pl/sql package that contains multi-line text.

    Version 17.3.0.271
    Build 271.2323

    Any suggestions? Thanks so much.

    SEVERE 968 5888 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)
    SEVERE 961 167 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)
    SEVERE 960 109 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)
    SEVERE 959 1712 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)
    SEVERE 956 7306 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)

    1. E.g. This is sufficient to raise the exception.

      CREATE OR REPLACE PACKAGE BODY demo_pkg AS
      PROCEDURE do_stuff AS
      v_text VARCHAR2(800 CHAR);
      BEGIN
      v_text := ‘a
      b
      c
      d
      e
      f’;
      END do_stuff;

      END demo_pkg;

      1. thatjeffsmith Post
        Author
  7. Hi Jeff. I’m using the new version of SQL Developer (17.3.1), but still facing connection resets since version 4.2 (it also happened to me on v 17.2). The number of connection resets have dropped, but they still occur even when working on SQL Developer, for example, I have 2 worksheets opened pointing to 2 different connections, I’m working for a while in one of them, When I want to use the other one, I get “Your database connection has been reset…” error message. I’m connecting to DBs from 11.2.0.3 to 12.1.0.1. It looks like there’s a timeout somewhere that drops them (just a hunch). Any idea on how to debug this? Thanks in advance for your help.

    1. thatjeffsmith Post
      Author
      1. Hi Jeff, thanks for the reply. I know there’s no timeout at the DB level (I’ve checked the profile the users have configured). How can I check if there’s a JDBC driver timeout?

        1. thatjeffsmith Post
          Author
  8. Morning Jeff,

    I have two questions:

    1. How can I set something in setting or preference in SQL Developer so I don’t have to put escape or set define off for ‘&’ symbol? I mean if I don’t to put in my every script or statement.

    2. How can I extract DDL for a user or role with all the privileges, like if I click on a user then click on SQL then it shows only user creation, but not the roles and privileges are granted, same for any Role. I mean in Toad it shows by default.

    Thanks a lot for your time in advance.

    1. thatjeffsmith Post
      Author
      1. Thanks Jeff for the answers.

        I got the solution for Q#2, but Q#1 still not resolved, I mean it is still asking for value (for &). How can avoid and escape globally (from SQL Developer menu).

        Thanks again

  9. Months_Between: I need to replicate this function in R. do you know where the source code is contained? The db is 11g; I’m really hoping I can find the implementation somewhere locally

    1. thatjeffsmith Post
      Author
  10. Hi Jeff

    Sub: Include date and time in the export dump file name.

    Thanks for the great site.

    I want to know how to put date and time in the export dump file name when I am exporting a dump file using the data pump wizard in Oracle SQL Developer.

    Thanks in advance
    Vadi

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  11. Hi Jeff,
    I recently downloaded sqldeveloper-17.3.0.271.2323 and when started using it I came across two issues so far.

    When generating DDL esp. for objects like Materialized Views I get a message like below in the beginning and the code it generated after that is incomplete.

    — Unable to render MATERIALIZED VIEW DDL for object APPS.AMS_ACT_METRICS_MONTHLY_MV with DBMS_METADATA attempting internal generator.

    Secondly when I tried to run AWR reports and when I have multiple Database ID’s and tried to pick the current ID from the LOV and when tried to pick the snapshot Start id it throws errors like below.
    The error pops out immediately when i clicked the ‘Browse’ Lov button.

    !Error Encountered!
    An error was encountered performing the requested operation:
    Missing IN or OUT parameter at index:: 3
    Vendor Code 17041

    I tried with the latest bundle sqldeveloper-17.3.1.279.0537 and still having these two issues stated above.

    I was not having those issues in the previous version 17.2
    Not sure whether this has to do with any preferences setting in the new version or there is a bug in this release.
    Appreciate if you could review and update with your findings.

    Also I happened to delete the old version from my computer without keeping a backup copy.
    So is there a way I can download 17.2 version until these issues are addressed?
    I looked around and could not find any links to download archives.

    Thanks

    1. thatjeffsmith Post
      Author
      1. Thanks for your quick update Jeff.
        To clarify, I was working with non-RAC databases and after I reverted to 17.2 the awr reports and DDL generation for Mat.Views are working fine.
        I know these worked when I had 17.2 version earlier.
        Do you know of when the latest version will get these issues addressed?
        Thanks,

        1. thatjeffsmith Post
          Author
        2. thatjeffsmith Post
          Author
          1. Hi Jeff,
            I was connected to 11.2.0.4 when experiencing issues.
            The issue happens in 10g (10204),11g R2 (11203,11204) and 12c (12.1.0.2)
            I do not have a 12cR2 database yet.

            Thanks

  12. Hi Jeff,

    I have started using sql Developer (Version 17.2.0.188) a couple of weeks ago, and everything is working fine except that the PDF report creation. It seems to create the PSF report, but I can’t find it anywhere.

    Also, is there any tool to create a user interface menu? (either within or third party?)

    Sorry if my questions are obvious, but I have been away from oracle for quite a few years and a lot has happened!

    Thanks,
    Ladon

  13. I am trying a simple SELECT of an SDO_GEOMETRY column, and that fails:

    $ sql scott/tiger
    SQLcl: Release 12.2.0.1.0 RC on Fri Oct 20 17:50:47 2017
    Copyright (c) 1982, 2017, Oracle. All rights reserved.
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

    SQL> desc us_cities
    Name Null? Type
    ———- ——– ——————
    ID NOT NULL NUMBER
    CITY VARCHAR2(42 CHAR)
    STATE_ABRV VARCHAR2(2 CHAR)
    POP90 NUMBER
    RANK90 NUMBER
    LOCATION MDSYS.SDO_GEOMETRY

    SQL> select * from us_cities;
    Oct 20, 2017 5:51:06 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
    SEVERE: oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:7797)
    java.lang.NullPointerException
    at oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:7797)
    at oracle.dbtools.db.SQLPLUSCmdFormatter.processTypeAttributesForStruct(SQLPLUSCmdFormatter.java:7594)
    at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:1182)
    at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:262)
    at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:236)
    at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:137)
    at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:65)
    at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:578)
    at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:494)
    at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:62)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:364)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:218)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:331)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:221)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:336)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:343)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:982)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:399)

    1. thatjeffsmith Post
      Author
      1. I get the same error:

        $ /opt/sqlcl/bin/sql scott/tiger

        SQLcl: Release 17.3.0 Production on Sun Oct 22 11:14:53 2017
        Copyright (c) 1982, 2017, Oracle. All rights reserved.
        Connected to:
        Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

        SQL> select * from us_cities;
        Oct 22, 2017 11:15:10 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
        SEVERE: oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:8440)
        java.lang.NullPointerException
        at oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:8440)
        at oracle.dbtools.db.SQLPLUSCmdFormatter.processTypeAttributesForStruct(SQLPLUSCmdFormatter.java:8221)
        at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:1264)
        at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:270)
        at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:244)
        at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:142)
        at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:67)
        at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:856)
        at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:767)
        at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:80)
        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:390)
        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:216)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:337)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:225)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:390)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:401)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1225)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:477)

      2. A simpler test:

        SQL> create table t1 (g sdo_geometry);
        Table T1 created.
        Elapsed: 00:00:00.537

        SQL> insert into t1 values (sdo_geometry(‘POINT(0 0)’));
        1 row inserted.
        Elapsed: 00:00:06.619

        SQL> commit;
        Commit complete.
        Elapsed: 00:00:00.005

        SQL> select * from t1;

        Oct 22, 2017 11:18:45 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
        SEVERE: oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:8440)
        java.lang.NullPointerException

  14. Is it possible to disable the ‘Other Users’ node in sql developer 4.1.1? I am working in an educational setting and users should not be able to see each other’s work.

    1. thatjeffsmith Post
      Author

      No, but you can create users that don’t have privs to see other schema objects, their details, and obviously their data.

      If you rely on the UI to ‘hide’ stuff, you’re gonna be screwed when the students figure out how to just write a SQL statement to see whatever they want.

  15. HI Jeff is there a way in SQL developer if my ABC_1 table has one col name MEMO_TEXT , It shows Text information with RTF tags like this below

    “{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Times New Roman;}}
    \viewkind4\uc1\pard\f0\fs20 ‘ This is my text abacedefgh’

    How can I change setting or something else so that my table ABC_1 – col MEMO_TEXT shows only
    This is my text abacedefgh

    1. thatjeffsmith Post
      Author
  16. Hi Jeff,

    I would like to report some problems I have with the latest SqlDeveloper 17.3

    1) Put a multi-line string inside an editor panel (i.e. a string inside single quotes) then try to edit it.
    For me it does not work. The string blinks or disappears either partially or entirely. I have to remove
    the initial quote to be able to edit the dynamic sql strings enclosed into some pl/sql procedures.
    2) weird lock ups.. It happens that, when the database is slow to respond, e.g. upon package compilation, the entire interface locks up until the compilation has finished. This is quite bad.
    3) I normally suspend my PC to RAM. After reviving the system, then Sqldeveloper interface is normally screwed, i.e. the various components appear like they haven’t be refreshed. Sometimes it is possible to have the application back to normal by resizing it, sometimes it is not. It has to be restarted.
    4) Upon moving my PC from a LAN to another one, if I forget some connections active and I try to execute a command using these connections then the corresponding editor freezes for a long while so that normally I have to kill and restart the app..

    Thank you

    1. thatjeffsmith Post
      Author

      1 – fixed last night, go get version 17.3.1.
      2 – the connection is busy…you can’t do anything on that connection until it’s available again.
      3 – me too. Never have issues. I have Windows 7, 16GB of RAM, and am using Oracle Look and Feel in the preferences. I never ever have these problems on my Mac either.
      4 – Yup, that’s gonna suck. The JDBC driver doesn’t like it when a connection goes away. The timeouts are what they are. Just try a reconnect first, or disconnect before you unplug your machine and move it.

      1. 2 – ok, THAT connection is busy but this should not prevent me to click on other editor tabs, both associated to that connection and to others. What I mean is that the interface, while compiling a package, is completely locked up as if it is single threaded. I am working on a remote slow development server so this lock may last a few seconds.

        5 – and one more thing, :-). the default NLS settings for the dates is DD-MON-RR. If I try to export “inserts” from the data rows obtained from a query that contain date columns with the value, e.g., 9999-12-31, I get something like

        Insert into EXPORT_TABLE values (to_date(’31-DEC-99′,’DD-MON-RR’));

        This insert however inserts 1999-12-31 in the table, not the original value.
        Technically this is not a bug, but the “spirit” of the export command should be, IMHO, to restore the original value, regardless to what the NLS setting is.

        Thank you very much

        1. thatjeffsmith Post
          Author

          the default NLS settings for the dates is DD-MON-RR
          Dates are stored as dates. We see strings, but they’re formatted that way via NLS_DATE_FORMAT. If your setting is indeed DD-MON-RR, your date would be returned as ’31-DEC-99′.

          Also, running this:

          INSERT INTO EXPORT_TABLE VALUES (to_date(31-DEC-99,’DD-MON-RR’));

          Will insert a date of December 31st, 1999 – the date format mask ensures that. That’s the same date as ‘1999-12-31’ – it’s just that the format mask is different. If you always want it to look the same, embed the date formats in your queries.

          1. Okay, maybe I was not clear, but my question was different. The problem is that when the table contains 9999-12-31, export uses NLS_DATE_FORMAT to create the insert statement and therefore loses information from the century, so 9999 is transformed in 1999.

          2. thatjeffsmith Post
            Author
          3. Yep 9999-12-31 is like infinity for the application I am working on.. when I try to export I must care about the NLS else I risk to upload the wrong data.. It happened :-). I changed the NLS on my machine but there are many here, and in general, if the purpose of export is to export the data in a reliable way maybe it should be better to not rely on NLS

          4. thatjeffsmith Post
            Author

            I don’t want to argue with you, yes this could happen. Knowing your date formats and how your apps store data is important. That’s why I advise always including the date format explicitly in your code and queries.

          5. I cannot agree more and in fact what I normally do in my code is to do an explicit formatting. In this case however it is the sqldeveloper export tool that generates the insert statement based on the NLS_DATE_FORMAT setting that happens to produce wrong effects in the exposed case. The usefulness of the export tool is thus limited by this behavior.

          6. thatjeffsmith Post
            Author
    1. thatjeffsmith Post
      Author
      1. Code:

        Clear Columns Computes Breaks
        set lines 180 pages 30 arrays 30
        col log_time format a19
        col sid format 9999
        col username format a19
        col osuser format a20
        col serial# format 99999
        col spid format a9
        col lockwait format 9999999
        col St format a2
        col CX format a2
        col machine format a28
        define var1=1
        define var2=1
        select * from (Select a.sid, a.serial#,
        substr(to_char(a.logon_time,’ dd-mm-yy hh24:mi:ss’),1,20) log_time,
        a.process, a.osuser, a.username, b.spid,
        substr(a.status,1,1) St, a.lockwait,
        decode(a.server,’NONE’,”,substr(a.server,1,2)) CX, a.machine
        from v$session a, v$process b
        where a.paddr = b.addr and
        a.username is not null
        )
        where &&var1 like &&var2
        order by 8,6,3
        /

        1. thatjeffsmith Post
          Author
  17. Hi Jeff,
    My question is about Data Modeler.
    Is there a way to automate for example “schema compare” , “schema extract” and/or other activities.
    I guess if there is a option to start it via command line with parameters, or using SqlCli.
    Do I miss something in documentation ?
    Thanks

    1. thatjeffsmith Post
      Author
  18. I’m using the last version from SQL Developer, and sometimes I see that the environment is stucked, I try to do one thing, and until past one minute, the environment is active again. Is there any issue with the tool?

    I have installed SQL Developer with Java 8. How can I improve the tool, faster for example?

    Thanks in advance.

    1. thatjeffsmith Post
      Author

      Can you describe what is happening ‘sometimes?’ Is it possible the connection is just busy running a query? If you have a slow network connection to your database, there’s not much we can do to improve things.

      1. Any script that I try to run from the below error:

        SQL> @sess
        Oct 15, 2017 9:33:27 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor
        SEVERE: Could not process url:file:/home/oracle/DBA/sess.sql
        Oct 15, 2017 9:33:27 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
        SEVERE: java.io.Reader.(Reader.java:78)
        java.lang.NullPointerException
        at java.io.Reader.(Reader.java:78)
        at java.io.BufferedReader.(BufferedReader.java:101)
        at java.io.BufferedReader.(BufferedReader.java:116)
        at oracle.dbtools.raptor.newscriptrunner.ScriptParser.scriptParserInit(ScriptParser.java:87)
        at oracle.dbtools.raptor.newscriptrunner.ScriptParser.(ScriptParser.java:83)
        at oracle.dbtools.raptor.newscriptrunner.ScriptParser.(ScriptParser.java:75)
        at oracle.dbtools.raptor.newscriptrunner.FallbackParserProvider.(FallbackParserProvider.java:22)
        at oracle.dbtools.raptor.newscriptrunner.SqlParserProvider.getScriptParserIterator(SqlParserProvider.java:25)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:169)
        at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.runExecuteFile(SQLPLUS.java:3870)
        at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.run(SQLPLUS.java:210)
        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQLPLUS(ScriptRunner.java:406)
        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:243)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:337)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:225)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:390)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:401)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1112)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:477)

        1. thatjeffsmith Post
          Author
  19. Formatting in 17.2. I used the preference ‘Number of Commas per Line’, but now you tell me that that has been deprecated. I often deal with big select statements. If I use the default formatting of SQL Developer, then they get expanded to over 400 lines. It’s hard to work with that. Here’s what it looks like with 8 commas per line.
    https://postimg.org/image/85hbeabip7/

    However, if I try to get the same in 17.2, then it splits the Select in a strange manner(I assume it is just respecting the max characters per line property)
    https://postimg.org/image/3132tdxkij/

    Why was this preference removed? I liked it a lot.

    1. thatjeffsmith Post
      Author

      The 8 commas per line looks horrible to me, and I’d not want to support that code. Of course, this is a subjective matter…you obviously feel differently.

      We had multiple months of beta giving folks an opportunity to provide feedback, and no one cried foul on this one going away.

      We could always add it back. Please add a request to sqldeveloper.oracle.com, encourage your friends to vote for it, and we can take a look at adding it back.

      Or, you can keep a copy of sqldev v4.1 around just to be used for formatting.

  20. old dbms_job (before scheduler).
    Hi Jeff,
    I have some old fashion jobs and i cannot see the information about last execution, because of ORA-942. My sqldeveloper is 4.2.0. I can create jobs and i can see the information from other software.

    If you can tell me what is the grant i need i will apreciate.
    Connections, scheduler –> DBMS jobs.

    thanks in advance.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  21. Hi Jeff,

    I’m having a confusing time right now between versions of SQL Dev with spooling the current date to a filename (in other words, it worked previously and now it doesn’t).

    In version 3.2.20.09 I can run the below script successfully and it creates a file with the desired name invoice_20171011.csv (for today).
    In version 4.1.5.21 it just spits out a file with the name SPOOL.LST.

    The script:
    COLUMN FileName NEW_VALUE SetDate
    SELECT ‘invoice_’||TO_CHAR(SYSDATE,’yyyymmdd’)||’.csv’ FILENAME FROM DUAL;
    SPOOL &&SetDate
    SPOOL off

    I’m relatively new to SQL but have been trawling the net for days trying to work this out as I figured it was something I just didn’t know but I’ve finally decided to ask instead of wasting more time.

    The only difference I can see between the two is that in 3.2, SPOOL is coloured like an identifier but in 4.1 it is coloured as a keyword.

    Any ideas?

    Thank you in advance.

    1. thatjeffsmith Post
      Author
      1. Thanks Jeff, I decided to try 17.3 this morning (AU) and can confirm that it works in the current version.

        Thanks for the reassurance I wasn’t hallucinating 🙂

        Considering this works in 17.3 I won’t bother raising a bug fix request.

        1. thatjeffsmith Post
          Author
          1. Yep, experienced it, read about it, extended deadlines and waited.

            Downloaded the update today and all good, thank you! 🙂

            PS – option to import previous installation setup is Gold.

  22. Jeff,

    Isn’t it is funny how the simplest of things annoy us the most? Installed 17.3.0.271 build 271.2323. In Preference … Code Editor->PL/SQL Syntax Colors I set the color for ‘PLSQL Brace’ to my preference. ( If you must know Twilight Bold-Italic Yellow ). As I type a brace it appears as selected, but in under a second it reverts to the ‘PLSQL Opperator’ color. Is there a setting I am missing that would keep my preferred colors?

    1. thatjeffsmith Post
      Author
      1. Yes was OK in 17.2 . The issue exists on both Linux and Windows. On windows have tried both look&feel options Oracle and windows

        TR

        1. thatjeffsmith Post
          Author
  23. I noticed in a screen shot in one of Jeff’s posts that there are attributes within a single entity that appear in different colors. I have not been able to find how to format the display of individual attributes (e.g., color, bold). Any guidance? Thanks. Nadav

  24. Dear Jeff,

    I have installed the latest version of the SQL Developer. Upon setting up the preferences I ran into a problem with the syntax highlighting of comments. If I choose to set a color to highlight the background of any comment then every blank space (and tab space) is highlighted in the same color (even if it is not in a comment).
    Is this a normal behaviour or some bug?

    Thanks.

  25. This is probably a really silly question, but is there a way to set up SQL developer so that when I open up a new worksheet or a saved file it opens up next to my current tab? It did this when I was on an older version but I was upgraded to 17.2.0.188 and now newly opened items always open at the end.

  26. V17.2
    Tools => Monitor Sessions

    I go to my DBMS_SCHEDULER job line and look at the Active SQL. The resulting output is from some other sid/serial on a different instance. My command is showing INSERT while the Active SQL is for a User query.

    1. thatjeffsmith Post
      Author
  27. I’m new to SQL Developer and am looking for some basic assistance. I’ve installed version 4.3.1 (that’s what our department has) and have configured it to talk to DB2. I’m able to do a successful test. But then when I click on “Connect”, I get three error boxes that pop up

    1. An error was encountered performing the requested operation:
    DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=SYSCAT.SCHEMATA, DRIVER=4.13.127
    Vendor code -204

    2. An error was encountered performing the requested operation:
    DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=4.13.127
    Vendor code -516

    3. An error was encountered performing the requested operation:
    DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=4.13.127
    Vendor code -514

    But when I click OK, I still have an actual connection to the dB and am able to do my usual queries in the query builder.

    I’m wondering what these error messages mean so that I can make them go away, and what functionality am I missing out on because something didn’t load or connect properly.

    I’m an application developer who uses this tool to retrieve data for reporting purposes.

    1. thatjeffsmith Post
      Author

      If you’re doing regular DB2 work, I’d ask for the native DB2 tools. SQL Developer provides DB2 functionality/connectivity for the sole purpose of migrating your database to Oracle. So our query support is basic, and most of the other features in SQLDev are not available for DB2.

      No idea what those messages mean. Probably OK to ignore them if your queries are running OK.

      1. Thanks Jeff. I was thinking of just leaving them alone as they don’t “appear” to be causing any issues. That said, would you have any idea how I might be able to suppress them?

        Thanks again!

  28. Any idea why TNSPING command from sqlcl is throwing this error for any db I try?

    SQL> show version
    Oracle SQLDeveloper Command-Line (SQLcl) version: 17.2.0.184.0917

    SQL> tnsping mydb
    Sep 22, 2017 11:29:50 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
    SEVERE: java.net.URLClassLoader.getAndVerifyPackage(URLClassLoader.java:634)
    java.lang.SecurityException: sealing violation: package oracle.net.ns is sealed
    at java.net.URLClassLoader.getAndVerifyPackage(URLClassLoader.java:634)
    at java.net.URLClassLoader.definePackageInternal(URLClassLoader.java:654)
    at java.net.URLClassLoader.defineClass(URLClassLoader.java:689)
    at java.net.URLClassLoader.access$400(URLClassLoader.java:95)
    at java.net.URLClassLoader$ClassFinder.run(URLClassLoader.java:1184)
    at java.security.AccessController.doPrivileged(AccessController.java:686)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:604)
    at java.lang.ClassLoader.loadClassHelper(ClassLoader.java:850)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:829)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:329)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:809)
    at java.lang.ClassLoader.defineClassImpl(Native Method)
    at java.lang.ClassLoader.defineClass(ClassLoader.java:346)
    at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:154)
    at java.net.URLClassLoader.defineClass(URLClassLoader.java:729)
    at java.net.URLClassLoader.access$400(URLClassLoader.java:95)
    at java.net.URLClassLoader$ClassFinder.run(URLClassLoader.java:1184)
    at java.security.AccessController.doPrivileged(AccessController.java:686)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:604)
    at java.lang.ClassLoader.loadClassHelper(ClassLoader.java:850)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:829)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:329)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:809)
    at java.lang.Class.forNameImpl(Native Method)
    at java.lang.Class.forName(Class.java:278)
    at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.createProtocolInstance(PingCmd.java:144)
    at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.ping(PingCmd.java:98)
    at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.handleEvent(PingCmd.java:57)
    at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:446)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:214)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:336)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:225)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:405)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:416)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1128)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:492)

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. That definitely did the trick. I’m running sqlcl from a user who also has dba rights and access to $ORACLE_HOME… and $ORACLE_HOME in the PATH…. but again, not CLASSPATH. I’m sure there are possibly several ojdbc variants as part of the standard db install. In my case, I’m using latest PSU of 11.2.0.4.

          2. thatjeffsmith Post
            Author
  29. Hi Jeff,

    You are doing great work, thanks.

    I have a quick question, maybe it is already answered in your form, i tried little bit but couldn’t get the answer.

    When I create a new connection, by default it goes to Basic Connection Type, however I want to use my tnsfile so I select TNS then it shows database (services) names, but they are multiples, I mean several entries for one database/service name, even though there is only one entry in tnsnames.ora file.

    Please post a solution or workaround at your earliest.

    Thanks and regards.

    1. thatjeffsmith Post
      Author

      How many TNS* files do you have in that directory? I’m betting, more than one.

      We read and use all of them – because SQL*Plus does. It’s just not noticeable in SQL*Plus because there’s no GUI there to demonstrate it so easily.

      1. It is. you are the man.

        Yes, I had several tnsnames.ora files because we have one golden on a server and whenever it gets updated, and we reboot our own laptops the local tnsnames.ora is updated from the golden and before that a script renames the local file to something different (with time and date stamp). I moved the other files out of the Admin folder and I see only one entry. Thanks a lot.

        So it reads from every file, even though it has different name like tnsnames_sep20.ora ?

        Anyway, thanks for your reply and the fix.

  30. SELECT name, usable_file_mb, round(usable_file_mb/(case when type=’NORMAL’ then total_mb/2 when type=’HIGH’ then total_mb/3 else total_mb end)*100,2) as percentage
    FROM v$asm_diskgroup;

    This might have been asked/blogged about before as it’s been a bug for years; but I’ve not noticed it when Googling. The percentage column shows null in SQL Developer. In SQL Plus it shows the real value. A bunch of other v$ queries have the same issue, is this fixed in a newer version of SQL Dev (I’m on 4.0.3.16)

  31. New sql developer 17.2 hangs when I try to debug. It will not open the anonymous block and run the program. Shows like running, watch window pops up but nothing happens.

    Previous 5 versions all took settings from each other and never had problems.

    1. thatjeffsmith Post
      Author
  32. I’m currently on release 3.2.2 of SQLDeveloper and would like to upgrade to the 4.2 release. I currently have a major application developed in my 3.2.2 version. Question is, can I upgrade to the 4.2 release with no effect on any of the Procedures, sequences, tables etc. to my current application or do I need to export and re-import the application after upgrading it.

    1. thatjeffsmith Post
      Author
  33. Jeff,
    This may seem very elementary but i am trying to find a video tutorial on how to build a RDBMS from scratch.
    Im looking for the basics to building
    I understand and took classes on sql plus
    and querying and such but im lost in starting from scratch.
    I have the oracle 12c
    and also the express 11g
    could you guide me??
    pupmike

    1. thatjeffsmith Post
      Author
  34. Previously there were more options to generate code (Advanced Format, now Embed/expose – Ctrl Shift F7). Is there any way to configure other formats like c#, vb like before or is it only limit to java, Python and pl/sql?

    1. thatjeffsmith Post
      Author
  35. Hi Jeff!

    I just started using SQLcl and it looks great! Running scripts and looking for error messages has always been a pain and I was thinking that if I were able to highlight the ORA-messages in the output that would be awsome. What are the chances that some form of output parsing rules/functions would make it into SQLcl?

    Maybe there already is functionality in there that I could use for this?

    1. thatjeffsmith Post
      Author
  36. Hello, Jeff. I would like to ask if there is a way to find my already stored database passwords on my Databases and if there is a way, can you please analytically explain the procedure step by step. I have tried through an Oracle Sql developer extension that exists on the internet and it didn’t work. Moreover, there are some python or java codes but didn’t work for me, too. These codes exist in the case there is an .xml export of the databases (in which the oracle sql developer asks a password for encryption). The version of Oracle Sql Developer is Version 4.2.0.17.089. Thank you, very much! BR. Telis

    1. thatjeffsmith Post
      Author

      You’ve lost your password, but SQL Developer knows what it is, b/c at one point you told it to change your password?

      If this is the case, simply use SQLDev to change your password once you’re connected.

      1. Thanks for the immediate answer. I forgot the password, but sql developer knows it b/c I had used the save option in order to get stored automatically and never need to insert every time I log in.
        Is there any other way without the option of resetting it (the password)? Thanks again, Jeff.

        1. thatjeffsmith Post
          Author
  37. Hi Jeff,
    Just looking at the DBA=>database=>tablespaces=>select tablespace=>sort by size tab.
    The sort decreasing (of the objects) starts with 9 and then finishes with 1 (no matter how many digits). I think this is because it is attempting to sort a character string not a number. I’m using version 4.2.0.16.
    Are there any settings I have missed out on to get the sort to function correctly?
    Regards
    Colin

    1. thatjeffsmith Post
      Author
  38. Hi Jeff,

    I would like to use the Object browser / Data to change content of a view that is having insteadof triggers. Running update statements directly works of course fine but in the Object browser / Data, the cells are grayed out – not for editing. Are there any preferences or.. to make the view content (data) available for update?

    Thanks in advance!
    Jo

    1. thatjeffsmith Post
      Author

      We look to see if the view columns are avail for updates, see ALL_UPDATABLE_COLUMNS, then we try to pull a ROWID for each row. If we can’t, then you can’t update the view with the grid. You can also look at the ‘Use ORA_ROWSCN for DataEditor insert and update statements’ preference.

      1. Thanks for the good explanation. The instead of trigger can bypass the ALL_UPDATABLE_COLUMNS. But still there is a need for a rowid/ora_rowscn in the where condition of the update statement from the grid to the database. My best option is to add a primary key constraint (novalidate..) to the view but I guess this is not good enough for the update statement.

        Br
        Jo

  39. I installed Oracle Sql developer 17.2 on my mac, trying to add third party JDBC driver but there is no option of preference present in tools. So not able to add third party JDBC driver.
    Please help.

    Thanks Prasad

    1. thatjeffsmith Post
      Author
  40. Hi Jeff,

    I learned about the Db Doc functionality in SQL Developer thanks to your previous post “JAVADOC for the Oracle Database a la DBDOC”. I am running SQLDeveloper v17.2.0.188.1159. Currently the DB Doc will not generate for standalone functions or stored procedures, only those that are encapsulated in packages. Can you point me in the right direction for finding out if/when this feature could be available? It would be extremely beneficial to my organization.

    Thanks for all the great information re: SQLDeveloper BTW, truly appreciated.

    1. thatjeffsmith Post
      Author
  41. Hi Jeff,
    when exporting a cart using the “separate directory” option multiple times the exported files are never replaced. with every export a new set of files is created with a prefix to make the filenames unique. that is a bit cumbersome because i want to check in the changed file in our svn. in the moment i have to delete all files prior to exporting them.
    is there an option to deactivate the behavior so that exporting will overwrite existing files?

    1. thatjeffsmith Post
      Author

      The cart has a CLI available using the SDCLI exe in your bin directory. Script it such that a bat or bash script deletes the old files first, then run your cart. Then check your stuff in.

  42. Hello jeff,
    Below query gives me complete table description, relationship, primary key foreign key etc.. could you please help me translating below sql server query to oracle

    USE [Database_Name]
    — ===============================
    — Description: GENERATE DATA DICTIONARY FROM SQL SERVER
    — =============================================
    CREATE proc [dbo].[spGenerateDBDictionary]
    AS
    BEGIN

    select a.name [Table],b.name [Attribute],c.name [DataType],b.isnullable [Allow Nulls?],CASE WHEN
    d.name is null THEN 0 ELSE 1 END [PKey?],
    CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [FKey?],CASE WHEN e.parent_object_id
    is null THEN ‘-‘ ELSE g.name END [Ref Table],
    CASE WHEN h.value is null THEN ‘-‘ ELSE h.value END [Description]
    from sysobjects as a
    join syscolumns as b on a.id = b.id
    join systypes as c on b.xtype = c.xtype
    left join (SELECT so.id,sc.colid,sc.name
    FROM syscolumns sc
    JOIN sysobjects so ON so.id = sc.id
    JOIN sysindexkeys si ON so.id = si.id
    and sc.colid = si.colid
    WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid
    left join sys.foreign_key_columns as e on a.id = e.parent_object_id and b.colid = e.parent_column_id
    left join sys.objects as g on e.referenced_object_id = g.object_id
    left join sys.extended_properties as h on a.id = h.major_id and b.colid = h.minor_id
    where a.type = ‘U’ order by a.name

    END

    Thanks and Regards
    Sindhu

    1. thatjeffsmith Post
      Author
  43. Hi Jeff,

    Is SQLcl available in any open repository like Maven central, Github, some oracle repo ?
    If not is there any ways to get the latest stable version in an automated way?

    Right now I am downloading it manually from the oracle website, but if we decide to use it as a replacement of SQLPlus there must be some way to automate the installation.

    Regards,
    Mitko

    1. thatjeffsmith Post
      Author
      1. To be honest I personally have the same problem with the current oracle client installation.
        I hope one day we will have it in Homebrew, Apt-get or Yum.

        In the java world the jdbc is already in maven, but it’s still quite cool to use the sql formater from SQLcl.

        The main difference is that it’s well know and in a corporate world it either comes as part of the OS image or there is already an approved procedure to do it.

        The opensource version would be quite cool.

        Cheers

  44. Hi
    This is not a comment but a question: There used to be a spatial add-on called Raptor. We deal with a lot of spatial data and I am interested in using SQL*Developer to manage spatial data. Are there any spatial tools available that I can use within SQL*Developer ? If there are none then do you know IF there will be any available any time soon ?

    1. thatjeffsmith Post
      Author
  45. I’m trying to use the select /*csv*/ option to create a csv file and then load the file using bcp. My problem is something to do with line terminators. When I export the file from the grid using export everything works fine, but when I try to spool the file using the script option and try to load the file, I’m getting unexpected EOF issues.

    I’m using version 4.2.0.17.089 build 17.089.1709

    1. thatjeffsmith Post
      Author
      1. Actually – I’m trying to load it into Sybase – as I said, if I export the data from the grid into a csv file it loads fine using bcp. However, I have over 150 tables and I want to use the spool option

        spool “H:\Migration\08282017\CATSDB_08282017.csv”
        select /*csc*/ * from SAPSR3.CATSDB;
        spool off;

        1. thatjeffsmith Post
          Author
  46. Hi Jeff,

    Is it possible to use expression to define foreign keys names in Data Modeler 17.2?
    I would like to name my foreign key like this {model}_{table abbr}_SUBSTR(1,3,FRONT,{column})_FK_I_DECODE({relationship},SUBSTR(1,8,FRONT,{relationship},”,{relationship})

    Best Regards,
    Antonija

    1. thatjeffsmith Post
      Author

      Not in the UI but you could probably write a transformation script to do it. So it would run and dynamically rename all of your FK names. You’d have to write it in javascript, o you wouldn’t have access to oracle SQL functions.

        1. thatjeffsmith Post
          Author
  47. In SQL Developer Data Modeler, could you please tell me how the Overlapping Attributes part of the Entity Properties is supposed to work? I’ve tried checking various boxes and clicking Apply then OK, but have yet to see any affect from it. The boxes in the Folded column won’t allow me to check them.

    Also, there doesn’t appear to be anything in the help which explains this feature.

    I’m running version 17.2.0.188 Build 188.1159.

    Thanks

    1. thatjeffsmith Post
      Author
  48. Jeff-
    Application developers often have to copy data from our Production databases into our Test and Development databases to debug data errors happening in applications. Right now, application developers have to request that a DBA export Production data and import the data into Test and Dev. This often takes days because of the DBA’s workload. Developers have access to all the data in Production, so is there a way to use SQL Developer to copy the data instead of bugging the DBA’s all the time?

    1. thatjeffsmith Post
      Author
  49. Why in 17.2 don’t work copy and paste (to Worksheet) in list of database objects (tree Connections)? in previous version 4.x works ok. I know is work drag and drop but I like use C&P.

    1. thatjeffsmith Post
      Author
  50. Re: How do I compare two query result sets by comparing grid to grid ?

    If I run one query – the result set gets output to the grid – and then run another query with it’s result set output to another grid is there a way to compare the grid result sets and output the differences to another grid or window ???

    Is there a plugin for Oracle SQL Dev 4.1 that will do this ??? I would LOVE to have this functionality as I’m always comparing data from one query versus another query. The export to Excel and it’s comparison functionality is painfully slow and NOT user friendly.

    Thank You 🙂

    1. thatjeffsmith Post
      Author
      1. Thanks Jeff, I’m familiar w/the minus command. I just want to select two grids, right-click, compare and see a third grid w/the differences.

        1. thatjeffsmith Post
          Author

          And what happens if each grid has 100,000,000 rows in it? That’s the conundrum i face each time I look at this feature request. Comparing a few hundred or thousand rows is no big deal.

  51. Jeff,

    I am trying to copy objects from one scheme to another across different connections. Table ddl statements fail due to missing table space. Is there a way to turn off table space option when selecting objects in Tools -> Database Copy  Object Copy.

    Thank you

    1. thatjeffsmith Post
      Author
      1. Thanks Jeff! That worked. However, I would like to point out few things for consideration:
        1) constraint ddls fail because they are executed twice – once as part of table ddl, second time as an alter statement. It should be one or the other when both are selected.
        2) Package bodies need to be explicitly selected otherwise only specs are copied. I have not found an easy way to select multiple package specs and bodies, short of clicking one by one.
        3) Dragging could be impractical/cumbersome with tens of connections in your explorer window. It would be nice to right click on selected objects and select schema to copy to + options.
        4) It would be nice to fix Database Tool copy to remove tablespace clause or make it selectable.

        Thank you!

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  52. Hey Jeff,
    Love your info and product. My question is where did the equivalent to the /datamodeler/xmlmetadata/doc that a few folks seeking info on the SQL Developer Data Modeler object API for scripting were referred to end up for releases 4.2+ of SQL Developer Data Modeler? I cannot seem to find this folder or the docs in most recent releases.

    We believe that some custom scripts for our shop may be helpful to streamline certain naming, standardization and generation workflows. How can we find out more about the objects, properties and methods available within scripts?

    Thanks,
    Jim

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        Thanks for the information – I have completed an SR for this enhancement.

        I also mentioned in the SR a potential bug within the SQL developer unit tester when specifying the expected exception number. Shouldn’t this number be negative to be consistent with the PL/SQL routine “raise_application_error” and the PL/SQL compiler pragma “exception_init”?

        Using a negative exception number in the unit test does not catch the exception. e.g. specifying -6510 or +6510 doesn’t work but 6510 does.

        Many thanks,
        Mark.

        1. Hi Jeff,

          MOS suggested I raise a SQL Developer Enhancement Request and the SR has been closed. Here is the number of the feature request: #45481

  53. Hi Jeff,
    I am using Oracle SQL Developer version 4.2.0.17. My question is : Does it support to migrate Sybase ASE 16 to Oracle 12.2, as I don’t up to Sybase(15) in the options.

    Regards,
    ‘Deep’

  54. Dear Jeff,

    Could you please tell me how to get tab-separated column results under SQL Developer 4.2? Under 4.1, I was used to using the following script:

    col TAB# new_value TAB NOPRINT
    select chr(9) TAB# from dual;
    set colsep “&TAB”

    select * from table;

    but that seems to no longer work in 4.2. Is it a bug?

    Many thanks,

    Matyas

  55. Is there a way to script the export function?
    I have a report setup that I can export to a xlsx file. I would like to script the report and export so I can run it as a scheduled task from the OS. Even better would be to send it via email.
    I know there are many ways to accomplish this, just thought I would try with SQL Developer.

    Thanks,
    Rob

  56. Hi Jeff,

    I am new to the unit testing functionality within SQL*Developer v17.2.0.188 so forgive me if this question is obvious. When creating a new test, I must decided whether the desired outcome of a PL/SQL subprogram invocation is successful or an exception is raised.

    When detailing the expected exception, the product allows a number or the word “ANY” which is similar to the WHEN OTHERS generic handler. Is it possible to handle PL/SQL named exceptions rather than relying on system or user-defined exception numbers?

    Many thanks in advance,
    Mark.

      1. thatjeffsmith Post
        Author
    1. thatjeffsmith Post
      Author
  57. I use SQL developer pretty much every time I touch Oracle so kudos on your efforts. Do you remember working in the Honors office?

    1. thatjeffsmith Post
      Author
  58. Hi Jeff. What happened to Snippets in SQL Developer V4.2.0.17.089? It doesn’t appear in the View menu like it did in 4.1.3.20.

    1. thatjeffsmith Post
      Author
  59. Hi Jeff,
    I need to create multiple data export files by executing 5 sql scripts in succession, each spooling to a different CSV output file.
    I can do this in SQLDeveloper v4.1.5.121 but it prefixes the output with the CarriageReturn and LineFeed characters ( CR + LF. )
    How can I get rid of these ?
    Below is a listing of one of my scripts and the top 2 lines of the output

    I was hoping sure this is a simple task but I searched for hours for this fix without success.
    Took me 4 hours to figure out how not to get the Prompt lines (SQL >> @ C: … ) at the beginning of the file !!!
    Thanks for the help.
    Aubrey

    <>
    set SQLFormat CSV
    set echo off
    set feedback off
    set HEADING off
    set verify off
    spool C:\WorkInProgress\text1.txt
    select * from VDS_USER;
    spool off

    <>

    “ID”,”NAME”,…

    <>
    @ C:\WorkInProgress\testSQLDev.SQL

    1. thatjeffsmith Post
      Author

      a TON of work went into the script engine (the SQL*Plus stuff in sqldev) between version 4.1 and version 4.2. In the latest SQLDev, I don’t see any leading blank lines when running your script. Can you try upgrading?

  60. Please provide a walkthrough for creating data flow diagrams in the Data Modeler.

    I cannot find anything in the documentation. Usage is certainly not intuitive.

    1. thatjeffsmith Post
      Author
  61. SQL Dev Version 17.2.0
    Database 12.2

    I have 3 separate “apps” running mainly because if I have a long running job then SQL Dev basically freezes the app and I cannot multi-task on the same connection. It seems every day I get connection reset
    “Your database connection has been reset. Any pending transactions or session state has been lost” pop-up on one of these 3 SQL Dev apps running.
    I would like to be able to submit a query or insert or update in the worksheet and at the same time look at another table or view or Package.
    Regards

    1. thatjeffsmith Post
      Author

      use an unshared worksheet for your long running queries

      Your database connection has been reset. Any pending transactions or session state has been lost – you shouldn’t be getting these on your v17.2 connections…you’re seeing it frequently or…?

      1. Every 20 – 30 minutes
        But we are also running into ORA-00600 Error in the same session. So may just be that error which is the culprit

        1. thatjeffsmith Post
          Author
      2. I have the same issue but it occurs repeatedly and not due to long running DML and not tied to an ORA-600 error that I am receiving, though it happens on those as well. I typically have two instances of SQL Developer running on a Windows platform, each with multiple tabs and multiple instances connected. The lost connection issue when it occurs does not affect open connections to other instances. I have re-installed 4.2.0.17.089 Build 17.089.1709 and it still occurs. Searching the web shows others with the same exact issue but no real work around. This should be an active bug that is being addressed. What can we do?

        1. thatjeffsmith Post
          Author

          This should be an active bug
          If you can describe a reproducible scenario in 17.2, then probably. Assuming it’s not the DB or Network kicking your connection.

          And, then you should open a service request with My Oracle Support.

  62. I am trying to set up SQLDeveloper to use LDAP, I have SQLPLUS working great but SQLDeveloper gives the error when every I select the LDAP Servers.
    “Status : Failure – [LDAP: Error code 32 – No Such Object].

    Configuring an advanced connection using “jdbc:oracle:thin:@ldap:/………” also works.

    Any idea what I am missing?

    Thanks

    Roger

    1. thatjeffsmith Post
      Author

      SQL*Plus doesn’t use a thin jdbc driver, so if you want SQL Developer work with ldap as you have with SQL*Plus, you have to have SQL*Plus use a THICK connection so it will see your sqlnet.ora/etc.

      1. Thanks for your quit response,
        Do you mean have SQL Developer use a THICK connection?
        If so have tried setting “Use OCI/Thick Driver” and still the same error.

        Testing the Oracle Home located at C:\oracle\product\12.1_64bit\client_64bit
        Testing client directory … OK
        Testing loading Oracle JDBC driver … OK
        Testing checking Oracle JDBC driver version … OK
        Driver version: 12.1.0.2.0
        Testing testing native OCI library load … OK
        Success!

        Thanks
        Roger

        1. thatjeffsmith Post
          Author
  63. Hi Jeff,

    2 questions for you:

    1. Identity. I read your post about it (http://www.thatjeffsmith.com/archive/2014/01/defining-12c-identity-columns-in-oracle-sql-developer-data-modeler/), but can’t get mine to work….

    I created a relational model. Checked the following preferences (main ones, for the app):
    * preferences -> data modeler -> model -> rdbms -> 12c
    * preferences -> physical -> Oracle -> default identity ddl -> identity clause
    * preferences -> physical -> Oracle -> default auto increment ddl -> default clause (though I also tried it with ‘none’ and ‘trigger’, but didn’t see a difference)

    When I create a new table, add a number (12,0) column, designate it a primary key, and preview DDL, there is no mention of identity… I can double-click the column to see general properties. Neither “auto increment” nor “identity column” are selected automatically… (why not? would’ve expected that as a default for foreign key…)

    I select both “auto increment and identity column”. Apply. Preview DDL. A trigger is generated. Click the column again, go into “auto increment” preferences. The “generate trigger” checkbox is selected. Unselect. Generate DDL again. Get a sequence…. It doesn’t seem to be able to get identity going….

    I added a physical 12c model (though not doing anything with it at the moment). Looking at column properties from there, I see “auto increment”, but “generate DDL” is empty. I select “identity”, save everything, but DDL preview still generates a sequence, not an identity clause…. Could you please help?

    (As an aside, main preferences seem to be a bit busted on a Mac. There is no scroll bar on the windows with the checkboxes–you can see them scroll into the box, but you can’t get to what’s below the window line….)

    1. thatjeffsmith Post
      Author

      on the relational model, table properties. select the column, hit the properties button – do you have ‘auto increment’ and ‘identity’ selected there?

      It’s working for me in 17.2

      1. Yes, have both checkboxes. Both are unchecked. Check them. Then if the “trigger” checkbox under “auto increment” is checked, I get a trigger, else I get a sequence. Been struggling with this most of the day today, can’t get it to work in 17.2. Could it be getting overwritten by a higher level property or some such? What is your “preferences -> physical -> Oracle -> default auto increment ddl” clause set to (though I’ve tried every option…)

        1. thatjeffsmith Post
          Author
          1. No-no,

            I’m saying I’ve checked all 3 levels of preferences where this is mentioned…

            1.
            – Create a dummy table. Column name = ‘id’, logical, numeric, (12,0), check ‘PK’ box.
            – Double-click column. Click ‘general’. The ‘auto increment’ and ‘identity’ check boxes are not checked. Check them. Apply.
            – Click at the ‘auto increment’ link. Note that there is a ‘generate trigger’ checkbox at the bottom, and it’s checked.

            What I’m saying is that if I leave that ‘trigger’ box on the ‘autoincrement link’ page checked, I get a regular non-identity column and a trigger. If I uncheck it, I get a regular non-identity column and a sequence….

            2. I looked at the relevant preferences (main ones for SQL Developer):
            * preferences -> data modeler -> model -> rdbms -> 12c
            * preferences -> physical -> Oracle -> default identity ddl -> identity clause
            * preferences -> physical -> Oracle -> default auto increment ddl -> default clause (though I also tried it with ‘none’ and ‘trigger’, but didn’t see a difference; what do you have here?)

            3. I made sure that I have a 12c physical database.
            Looking at column properties from there, I see “auto increment”, but “generate DDL” is empty. I select “identity”, save everything, but DDL preview still generates a sequence, not an identity clause….

          2. Hi Jeff,
            Identity not generating is still a problem… Do you kno what it might be down to? What are your global preferences set to (see my last response for mine).
            Thanks again!

          3. thatjeffsmith Post
            Author

            No idea. It should be working. Try opening a post on our modeler forum, and the developers can delve deeper, and we can share screenshots back and forth more easily.

  64. Yes, other changes are persisting… Upgraded to 17.2 this morning. Deleted tables/FKs. Changes persist after re-opening, so yay.

    However, from clicking “open” menu to getting the box to select model to open took about 10 mins. Tried a few times, same deal. Running on a Mac (Sierra) with 5-6G memory free (8G total)… A bit puzzled by that…

    Also, opening DM (or SQL session), top icon bar is pretty bare, and I have to click “Window -> Reset to factory settings”. How do I make it remember it? Similarly, opening a DM, the file selection box defaults to an old directory. How do I make it remember the location of the last file opened?

    Many thanks!

    1. thatjeffsmith Post
      Author

      Ok, we’re talking about multiple problems now. Let’s stick with the model one.

      Can you create a new design, from scratch. Then add 2 tables. Save it. Then delete a table. Save it. Close the design, and re-open – is your table ‘gone?’

      If so, then I think there’s something wrong/corrupted with your other design.

      1. Hi Jeff,

        Following the upgrade to 17.2, the deletes were working correctly (so perhaps a 4.2 issue on a Mac??). The problem was how long it was taking to load a model.

        I found your article about resetting to factory settings permanently (http://www.thatjeffsmith.com/archive/2015/08/how-to-reset-your-sql-developer-preferencessettings/). Having done that, the icon bar now shows up properly AND the time to open the model is now only a few minutes. Whatever was causing the long delay looks to have been cleared. (Default directory also wiped clean!)

        This whole thing with deletes and upgrade looks to be fixed now. Thanks again for your help!

        1. May have spoken too soon… Was fine quitting and restarting earlier (in terms of clicking “open model” and getting the directory window to select model)–was taking about a minute after resetting preferences. Just started it up again, and for no good reason it’s back to taking 15 minutes to give me the selection box (to open the dmd file)….

  65. I read a post about an Oracle SQL Developer user that was sick and tired or it….but you were kind enough to email him and you addressed most of the items he was frustrated with. (This was a post from about two years ago.) He listed one of the items that has been bugging the “heck” out of me…but he did not include any of your advice/suggestions.

    When I am working in SQL Developer, on a data grid, I can press the tab key to advance to the next column…then, for no apparent reason, the next time I use the tab key, it will cause the “edit value” dialog button to appear in the field and I can tab from column to column anymore. The only way that I have found to “restore” the tab key is to exit out of the application and then re-launch it. Is there a setting or preference that can control this behavior? Thanks in advance for any feedback.

    1. thatjeffsmith Post
      Author
  66. Hello Jeff,
    this might be a simple Task and I’ve tried to find the answer, but it’s still not clear.
    We’re using Windows user profiles with space restriction for Appdata.
    Now the SQL Developer (Version 4.1.2.20.64) is storing the System Cache data within the roaming Profile.
    I’ve found a Workaround to add “AddVMOption -Dide.user.dir=C:\sqldeveloper” at “\sqldeveloper\bin\sqldeveloper.conf”.
    Do you know any other way to solve the Problem?

    Many thanks in advance!
    Bastian

    1. thatjeffsmith Post
      Author
  67. Hello Jeff,
    this might be a simple Task and I’ve tried to find the answer, but it’s still not clear.

    We’re using Windows user profiles with space restriction for Appdata.
    Now the SQL Developer (Version 4.1.2.20.64) is storing the System Cache data within the roaming Profile.
    I’ve found a Workaround to add “AddVMOption -Dide.user.dir=C:\sqldeveloper” at “\sqldeveloper\bin\sqldeveloper.conf”.
    Do you know any other way to solve the Problem?

    Many thanks in advance!
    Bastian

  68. Hi Jeff,

    After I delete tables from a relational DM (right-click, “delete object”), save the model, and exit SQLDev, when I start it up again and open the model, all those tables come back!! I don’t seem to be able to delete them permanently… How do I do that?

    Even worse, when I delete foreign keys from a table, they *also* come back!! If I rename/recycle the tables these were once keyed to, the FKs resurrect themselves under the old names and an empty relationship.

    Happens every time… How do I make deletes permanent, esp. around FKs? Is there some kind of magic “purge” option?

    Running SQL Developer 4.1.1.19 on a Mac (Sierra). I had a newer version on a different laptop late last year, and it was having the same problem.

    Thanks in advance!

    1. thatjeffsmith Post
      Author
      1. I know, I had 4.2 before, and I remember seeing the same behaviour.
        Not sure what you mean by removing from diagram but not model… Right-clicking in the diagram has “remove object” and “remove from view”. When I select the former, the table disappears from the navigator dropdown of tables….

        (Right-clicking tables in the navigator and clicking “delete” also doesn’t stop them from coming back after restarting the application 🙁 )

        1. thatjeffsmith Post
          Author
  69. Hi Jeff,
    I just got a new Lenovo Yoga 910 with a 13.9″ UHD (3840 x 2160) display. Unfortunately, SQL Developer does not scale UI components (Menu, Buttons/Icons, Font). I’ve tried Ide.FontSize but it does not really help. I mean the fonts get bigger but everything else remains tiny. It is impossible to work with the tool.
    How can I make SQL Developer properly scale on UHD displays?

    1. thatjeffsmith Post
      Author
        1. I’ve only got “Metal” and “Oracle” look and feel options. Both don’t scale. Will there be UHD support in a future release?

      1. Hi,
        Thanks, I already followed the advise in the link. It just makes the fonts a little bigger but anything else remains tiny (Menu, Buttons/Icons, etc.). And with the bigger font, some items are overlapping and get unreadable.
        I was hoping for a proper high DPI scaling solution…
        Best regards,
        Beat

  70. BREAK ON … DUPLICATE does not work ?

    16:26:42 NFP2TST1:[email protected]>BREAK ON SECTION SKIP 1 DUPLICATE;
    16:26:42 NFP2TST1:[email protected]>select ‘test’ section , 1 from dual
    2 union
    3 select ‘test2’, 2 from dual
    4* union select ‘test2’, 3 from dual;

    SECTI| 1
    —–|———-
    test | 1

    test2| 2
    3

    EXPECTED OUPUT IS

    SECTI| 1
    —–|———-
    test | 1

    test2| 2
    test2| 3

    1. thatjeffsmith Post
      Author
  71. COLUMN LIKE is not supported ?
    COLUMN FORMAT 999,999,990.00 is not supported ?

    — begin of transcript —–
    13:16:48 NFP2PRD1:[email protected]>col
    COLUMN posted_flag ON
    FORMAT a11
    COLUMN account ON
    FORMAT a10

    13:16:30 NFP2PRD1:[email protected]>col posted_flag like account;
    SP2-0158: unknown COLUMN option “like”

    13:16:50 NFP2PRD1:[email protected]>col d2 format 999,999,990.00
    SP2-0246: Illegal FORMAT string “999,999,”

  72. Hi Jeff,

    When I format the code, Is there any way to keep multiline comments intact?

    I use Java-style code comments:

        / **
        * Retrieve the value from the associative array
        *
        * @param p_array the associative array
        * @param p_key the array key
        *
        * @return the value if exists
        * /

    And SQL Developer formats it to:

        / **
       * Retrieve the value from the associative array
        *
       * @ Param p_array the associative array
       * @ Param p_key the array key
        *
       * @ Return the value if exists
        * /

  73. Dear Jeff,

    I installed APEX & ORDS on a test DB and they work well togheter. I created a workspace in APEX, some sample tables, then configured Restful services, and I can access them using the url:

    http:///ords/test/hr/employees/

    I own another Oracle DB with a user, a tablespace and few tables filled with data I want to access via REST from a Webapplication. I don’t want to install APEX here, I don’t need it and I don’t want to create a new tablespace like APEX_xxxxxxxxxxxxxxxxxx.
    I just want to create REST handlers (GET, PUT etc) for those existing tablespace/tables, even by plsql calls (it’s not a problem for me, I don’t care about a GUI) using ORDS.

    Is it possible?

    Thanks for your attention. Best Regards,

    Mario.

    1. thatjeffsmith Post
      Author

      Absolutely. Just install ORDS – no need to install/configure APEX. Then use SQL Developer or SQL*Plus to setup your RESTful Services. Click on the ‘ORDS’ link up top and you’ll see my tutorials on the subject.

  74. break on .. duplicate also does not work.

    16:26:42 NFP2TST1:[email protected]>select ‘test’ section , 1 from dual
    2 union
    3 select ‘test2’, 2 from dual
    4* union select ‘test2’, 3 from dual;

    SECTI| 1
    —–|———-
    test | 1

    test2| 2
    3

    expected ouput is
    SECTI| 1
    —–|———-
    test | 1

    test2| 2
    test2| 3

  75. COLUMN LIKE is not supported ?
    COLUMN FORMAT 999,999,990.00 is not supported ?

    — begin of transcript —–
    13:16:48 NFP2PRD1:[email protected]>col
    COLUMN posted_flag ON
    FORMAT a11
    COLUMN account ON
    FORMAT a10

    13:16:30 NFP2PRD1:[email protected]>col posted_flage like account;
    SP2-0158: unknown COLUMN option “like”

    13:16:50 NFP2PRD1:[email protected]>col d2 format 999,999,990.00
    SP2-0246: Illegal FORMAT string “999,999,”

  76. Jeff,
    There is no reply button/link in your comment/reply. So, I am using the new reply instead of replying to yours.

    I don’t see this behaviour if I create or recreate the package with SQLPlus or SQL Developer worksheet.

    1. thatjeffsmith Post
      Author
  77. Hi Jeff,
    I have been reading your website/blog since may be 2012 and find it very useful. But, I have not posted any comment or question so far. So, here’s my first question.

    Q.) Why does SQL Developer each time the package or the body is compiled insert white spaces (12 of them in 4.2.0) between package name and rest of the create or replace statement part before it?

    Here is what happens with each compile

    Original statement (1st line):

    create or replace package my_pkg
    is

    end my_pkg;

    After recompile:

    create or replace package my_pkg
    is

    end my_pkg;

    This happens with both package spec and body.
    After a few recompiles and several spaces inserted by SQL Developer, the package name is way off the screen and not visible unless I scroll to the right.

    Thanks in advance.

    1. thatjeffsmith Post
      Author

      That’s definitely not supposed to be happening, nor is it a known issue.

      How are you compiling it – as a sqlplus script in a worksheet, or in the code editor?

      Are you keeping this in a file?

      1. Jeff,
        Thanks for the reply, This is happening when using stored procedure/package code editor to compile: the two gears toolbar button. Actually, I have seen this behaviour for a long time. Just never took the time to post about it.

        1. thatjeffsmith Post
          Author

          Something is ‘afoot’.

          If you use the View > Log > Statements panel, you can see what we send to the db on a compile and when loading the source code into the editor.

          1. Jeff,
            I don’t notice anything (like inserting a tab or spaces) going in the SQL or PL/SQL sent to the database.

            However, when I query the dba_soruce using one of the statements from the statement log, I see spaces between “create or replace package” and package name.

            I see the owner (in ALL CAPs) and a dot (.) inserted before the package name even though in my create statement I don’t include it.

            Looks like something happening in the database or the statement log does not show actual statement for create procedure.

            This is on a DB that I use everyday and is on 12c.

            I just now tested this in another DB that’s on Oracle 11c and don’t see this behaviour. However, I have noticed this in Oracle 11c also.

          2. thatjeffsmith Post
            Author
  78. Hi Jeff,
    I believe you have said many times that SQLcl supports all SQL*Plus commands (with certain documented exceptions). I have found this difference in behavior of the PROMPT command:

    In sqlplus (version 12.1.0.2.0):
    SQL> PROMPT “test”
    “test”

    in SQLcl (version 4.2.0.17.097.0719):
    SQL>PROMPT “test”
    test

    What happened to the quotes under SQLcl? Why is it dropping them? I do not think sqlplus had a problem with unquoted strings and users had to use quotes – it was always the user’s choice to have or not to have them in the output.
    This difference is important for some of our scripts. Is this a feature of SQLcl and is here to stay, or it may change in some future release?

    1. thatjeffsmith Post
      Author
  79. Hi Jeff – new to ORDS. Have a good example working in our dev area. Wondering if you have a good suggestion about how to promote to a clustered weblogic production environment? I’m a little confused on what is stored where.
    Thanks!

  80. Hi Jeff,

    In a similar vein to deferred constraints, how does one do function-based indexes in the Data Modeler to have them be captured correctly in the DDL that gets later?

    1. thatjeffsmith Post
      Author
  81. Hi Jeff,
    Just upgraded to 4.2. I have a query I use that incorporates variables to be used so that I can run several different queries without having to put the same information in each query. In previous versions, I was able to “comment out the line” with the use of — and the Run Statement command would skip all the commented lines, and execute the desired line, regardless of how many commented lines were between the first line (with my variables) and the desired line. Now the lines have to be commented out using /**/ at the beginning and end of the line for the same effect. Is there a way to set it so that — will work as I utilize Ctrl+Slash as the key stroke to comment/uncomment the lines wanted.
    WITH my_vars AS (SELECT ‘99999’ AS val1, ‘XXXXX’ AS val2 FROM DUAL)
    –SELECT * FROM TABLE1, my_vars WHERE FIELD1 = val1 and FIELD2 = val2;
    –SELECT * FROM TABLE2, my_vars WHERE FIELD1 = val1 and FIELD2 = val2;
    SELECT * FROM TABLE3, my_vars WHERE FIELD1 = val1 and FIELD2 = val2;
    In the example above, previously Ctrl+Enter would execute for Table3, but now it doesn’t. Is there a setting that needs to be changed?
    Thank you,
    David

    1. thatjeffsmith Post
      Author
      1. So will it wait until a new version or like a hot fix? Is there a way to be notified if/when it gets fixed? Is there a bug number I can follow?

        1. thatjeffsmith Post
          Author
  82. When I launch SQLCL, it stores its user related information to %USERPROFILE%\AppData\Roaming\sqlcl folder. Can it be stored some where else. For e.g. I can set IDE_USER_DIR environment variable for SQL Developer to store it in a different folder. But this environment variable does not work for SQLCL. Please let me know.

    1. I found that if I set the APPDATA environment variable to appropriate path then it stores the values correctly. For now I am using this workaround for portability . I hope in future releases sqlcl will support IDE_USER_DIR environment variable.

      1. thatjeffsmith Post
        Author
        1. Thanks. The method mentioned by kris works for SQL Developer but not SQLCL itself. It seems that SQLCL does not use the SQL Developer options. It stores aliases.xml and history.xml in a sqlcl folder in APPDATA. Also using the environment variable allows me to quickly unzip the new version without having to modify the delivered file which will be overwritten. My goal is to install SQLCL and SQLDeveloper on a citrix maching where I want to write all config files to a shared nas drive for each user using %USERNAME% variable. So far setting APPDATA and IDE_USER_DIR before launching the SQLCL and SQLDEVELOPER works fine.

          1. thatjeffsmith Post
            Author
  83. Hi Jeff,

    In SQL Developer Data Modeler, how does one do defferred constraints? I have an application table and a payment one. The relationship is 1:1, so I would expect a deferred constraint, but can’t find a way to draw one… Or is there a way to ‘inject’ it into DDL?

    Also, I’ve noticed an occasional scenario when I have table X and want to create a new table with a foreign key to it. Although I enter the same exact values into the FK boxes, some end up with the ‘many’ crow foot in the new table and others end up with a ‘one’ two-bar in the new table… I can’t tell what’s causing this behaviour and/or how I cam change this. Could you please help?

    1. Hi Jeff,
      Wondering if this one got lost in the shuffle… How does one designate FKs as deferred? I am running SQL Developer 4.1.1.19.
      Thank you!

      1. thatjeffsmith Post
        Author
        1. Hi Jeff,
          Hope you enjoyed the beach. Sounds nice! 🙂
          On 17.2, I don’t see any checkbox like that…
          I’ve got ‘mandatory’, ‘transferable’, ‘in arc’, ‘deprecated’, and ‘generate ddl’…
          There is a ‘discriminator column’ dropdown on the same screen and ‘dependant columns constraints’ section in the nav bar, but that’s all that’s noteworthy… Would it be possible for you to post a screenshot of where that option is?
          Thank you!

  84. I’m using 4.2.0 version of SQL Developer and found a pretty weird behaviour about bind variable. After using following commands:
    VARIABLE var1 NUMBER
    EXECUTE :var1 := 10
    SELECT * FROM scott.emp WHERE emp_id = :var1; — here I get a prompt to input bind variable

    The steps same works in SQL*Plus.
    Did I missed some condition to make it work?

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  85. I have to run data patches against production databases on a regular basis. For years I have used SQL*Plus without problems. Recently I decided to start using SQL Developer. The F11/Commit button has failed on a couple of occasions leaving a lock in the database and users complaining that their data has not changed. Do you have any idea what I might be doing wrong? The SQL Developer version is 4.2.0.17.089. The database version is 11.2.0.4.

    1. thatjeffsmith Post
      Author
  86. I am using SQL Developer to import data from an Excel csv file into Oracle table. Works like a charm except when attempting to import a text cell with line breaks and formatted lists into a CLOB field. Is this even possible?

    1. thatjeffsmith Post
      Author
  87. Hi Jeff,
    We are planning to migrate a one TB sybase database onto Oracle 12c. We are thinking to use SQL Developer Migration Workbench. We will need to migrate a number of sybase databases. Surprisingly, I have not found any case study in the web regarding size of database migration, whereas, other third party tools publishing case study with size of databases.
    My worry is: Will it be possible to migrate >1TB database in stipulated release window. Release window starts on Friday 20hrs and ends at Sunday 8hrs.

    1. thatjeffsmith Post
      Author

      You need to do some test runs first. And you’ll want to use the offline data move, which will move the data much faster than the online data move. If you have GoldenGate licensed, then the data will move even faster.

      1. Hi Jeff,
        Thanks for your reply.
        Unfortunately, we don’t have Golden Gate licensed. If I find any article/case study/document where oracle claims that the fastest way to move data offline is nnnn/sec using SQL Developer Migration Workbench, then I can take that as an example and can start communicating with stakeholders to provide them an idea about elapsed time. All metadata,procedures etc.. will be migrated in one weekend and next weekend data needs to be migrated.

        1. thatjeffsmith Post
          Author

          SQL Developer is our migration platform. It’s not the fastest, it’s the only.

          How long it will take will depend on the nature of the data, your hardware, and so many other variables. That’s why you need to do a couple, if not many, test runs. Then you can hit your launch day with confidence.

          Also, moving data vs translating stored procedures is completely different. Testing the migrated stored procedures can take a long time, and will probably require some developer intervention to fix things that weren’t completely translated.

          1. Hi Jeff,
            Now, the client has changed plan. They first want to upgrade Sybase ASE from 15.7 to 16 and then later of the year, they want to migrate to oracle 12.2.
            My Question: Does SQL Developer support ASE16 migration? I see, up-to Sybase(15) in the offline options window.

          2. thatjeffsmith Post
            Author

            Maybe. I don’t think we’ve tested it from 16. It’s weird they want to do a major upgrade and then turn around and do an entire platform migration.

            If nothing major has changed in 16 and with the drivers, you should be OK. But try it first for sure.

  88. Hi Jeff,
    I’ve been scouring your site for the last couple of hours, so please forgive me if this question is answered somewhere that I’ve overlooked. Basically – I’d like to a way to jump quickly to a pre-established certain table in a specific connection, whether open or not? I hate to use the terms Bookmarks or Shortcuts, as they already have meanings in SQL Developer that don’t match what I’m referring to. Perhaps “Favorite” or “Project” would be better. Actually, “Project” would be great – open a saved project, and specific tables (down to connection.schema.table) would open.
    So – am I just totally overlooking this feature? Possibly available as a 3rd party extension? Even the ability to launch SQL Developer with those tables already open would be useful: configurable via command line parameters, perhaps?

    1. thatjeffsmith Post
      Author
  89. Hi Jeff,

    In our environments we are facing SCN issues due to DB links (SCN value suddenly increasing due to external db links) .So instead of db links we want to use ORDS .Could you please guide me how to do it?

    Thanks in Advance.

    Regards
    Ramesh.D

    1. thatjeffsmith Post
      Author
  90. In the SQL History for release Version 4.1.5.21 the TimeStamp shows as milliseconds even though the NLS for TimeStamp is “YYYY/MM/DD HH24.MI.SSXFF9”. How do I change SQL History TimeStamp to match the NLS TimeStamp?

  91. Hey Jeff.

    I’ve been trying to get a funnel chart to work and have not been successful.

    I tried multiple variants, but here’s the current version of the query that I’m using…

    select ‘Stage 1’ as Stage, 100 as Txns
    from dual
    UNION ALL
    select ‘Stage 2’ as Stage, 50 as Txns
    from dual
    UNION ALL
    select ‘Stage 3’ as Stage, 25 as Txns
    from dual

    The funnel chart that is produced shows all 3 stages as 0%.

    Any suggestions on what I might need to do to get the funnel chart to show Stage 1 as 100%, Stage 2 as 50% and Stage 3 as 25% would be much appreciated.

    Thanks,

    Bill

    1. Hi Jeff,

      Just checking back to see if you can provide any insight on how to get the funnel chart working. Thanks!

  92. Hi,
    Oracle SQL Developer gurus . Somehow the query result tab is not opening when running a query though all possible options are checked in the preferences. Any clue why?
    Thanks.

    1. thatjeffsmith Post
      Author

      Is it possible it’s just hidden? There’s 2 triangle looking buttons you can hit to hide/expand the output panels. Can you see script output, how about an explain plan?

  93. Go where?
    Hi Jeff,
    How do I ask a question about SQLcl here ? I am on the “Ask A Question” page, and I see your introduction with the three reminders, and a “Go!”, followed by a long list of comments from users with your answers, but no place to enter my question besides the “Leave a Reply” section at the very bottom. Is that the place to ask my question? Or maybe you have stopped accepting questions on this website?

    1. I guess that was the place to ask the question. Then, here it is:
      I do not have special settings limiting what protocols SQLcl should use to connect to my databases. When I try to connect with a wrong password, SQLcl seems to try at least twice, first using jdbc:oracle:oci8 and then using jdbc:oracle:thin (see below). Both attempts get rejected with “ORA-01017: invalid username/password”. Is it really trying twice? (I do not have privileges to verify that from the database side). If so, why try the second time if it is clear that the first attempt successfully found the database? This can exhaust my allowed attempts for logins with a wrong password and lead to getting the account locked. Is this a feature or something that can be improved?

      SQL> conn myname/[email protected]
      USER = myname
      URL = jdbc:oracle:oci8:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP
      )(HOST = xxx.xx.xx.xxx)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED
      ) (SERVICE_NAME = MMREP) ) )
      Error Message = ORA-01017: invalid username/password; logon denied
      USER = myname
      URL = jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP
      )(HOST = xxx.xx.xx.xxx)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED
      ) (SERVICE_NAME = MMREP) ) )
      Error Message = ORA-01017: invalid username/password; logon denied

      Warning: You are no longer connected to ORACLE.

      1. thatjeffsmith Post
        Author

        It’s the right place alright – well one of several right places, including My Oracle Support, the Forums, etc. That’s a bug. It should only be doing that when we can’t resolve the database, not when the username/password is wrong. Can you post this on the forums so I can have the DEV take a look? I can only partially reproduce it at the moment, but as soon as you get the 1017, it should immediately stop and ask you to re-enter your username and password.

  94. Hi Jeff,

    We have hit a strange issue with SQL Devleoper v4 (4.1.5.21 to be specific) which I am struggling to find much info about.

    We have enabled sqlnet encryption with the following set in our DB server side sqlnet.ora:
    SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256, SHA1, MD5)

    We can connect to the database hosted on here fine using SQL Dev v3.0.04 but when using the above v4 version the connection fails with host checksum errors and sometimes hangs.

    In the DB alert log we are getting this:
    TNS-12599: TNS:cryptographic checksum mismatch

    Very little info on this around other than some known OEM agent bugs.

    As soon as I remove SHA256 from the checksum types in the sqlnet.ora file the connection works fine from both versions.

    Are you aware of anything in SQL dev settings / configuration that might cause this ? I know it is a longshot…

    Thanks
    Paul

    1. thatjeffsmith Post
      Author

      The two HUGE differences in v3 and v4.1 are, the version of Java, and the version of the JDBC driver.

      I’m assuming you’re also using a THICK connection and are using two different versions of the Oracle Client?

      1. This is an out of the box setup, we are actually accessing it via a remote desktop due to security, we have checked and neither oci/thick or oracle client are selected.

        1. thatjeffsmith Post
          Author
  95. Hi,

    My question is about ORDS in APEX and the oauth2 authentication.

    I follow this example for ORDS in the Database and everything’s working fine.
    https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication

    When i’m trying to do the same thing in ORDS in APEX, i’m stuck where i’m calling the oauth/token to receive a token

    ORDS :
    curl -i -k –user CLIENT_ID:CLIENT_SECRET –data “grant_type=client_credentials” https://localhost:8080/ords/my_user/oauth/token

    ORDS APEX:
    curl -i -k –user CLIENT_ID:CLIENT_SECRET –data “grant_type=client_credentials” https://localhost:8080/ords/sandbox/oauth2/token

    When i’m calling the link https://localhost:8080/ords/sandbox/oauth2/token in a url, i get a 404 Not Found.

    Do you know the equivalent to oauth/token in ORDS APEX ? Is there a setting that needs to be activate so that the oauth2/token url will work ?

    Regards,
    Martin

    1. thatjeffsmith Post
      Author
  96. Hey Jeff, quick question…

    Is there any tool that you’re aware of (or plans to add to SQL Developer maybe?) that will export from an oracle database into a netcdf file?

    1. thatjeffsmith Post
      Author
  97. Hi,

    My question is about ORDS in APEX and the oauth2 authentication ?

    I follow this example for ORDS in the Database and everything’s working fine.
    https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication

    When i’m trying to do the same thing in ORDS in APEX, i’m stuck where i’m calling the oauth/token to receive a token

    ORDS :
    curl -i -k –user CLIENT_ID:CLIENT_SECRET –data “grant_type=client_credentials” https://localhost:8080/ords/my_user/oauth/token

    ORDS APEX:
    curl -i -k –user CLIENT_ID:CLIENT_SECRET –data “grant_type=client_credentials” https://localhost:8080/ords/sandbox/oauth2/token

    When i’m calling the link https://localhost:8080/ords/sandbox/oauth2/token in a url, i get a 404 Not Found.

    Do you know the equivalent to oauth/token in ORDS APEX ?

    Regards,
    Martin

  98. Jeff. I read a post you had about exporting connection profile and from SQL developer. Do you know much about SQL Navagator? Well I just received a new PC old had SQL 6.2. New will have SQL 10. Anyway you know how I can bring this email connections over to new PC with passwords of course?

    1. thatjeffsmith Post
      Author
  99. I ran EXEC SP_MY_PROGRAM(‘USER’);– SID 99
    I am observing data insertion from another session(599) after some time I am not able to see PROGRAM RUNNING SESSION (99) but data is getting inserting into table.
    May I know what might be the reason and how do i know when program got completed.

  100. Hi Jeff

    I am receiving the “SELECT LIST INCONSISTENT WITH GROUP BY” warning on my entire Select statement, but cannot determine why, since I am not using Group By anywhere in the script. My code is 162 lines, so I didn’t think you would want it posted here, but I can – or can separately – provide it if you would need it to help.

    I am hoping that you have simply seen this behavior before and might be able to point me in the right direction to resolve.

    I appreciate any assistance that you can give.

    Best regards,

    PS – I just upgraded to Version 4.2, and that is when this started …

    1. thatjeffsmith Post
      Author

      can you make it happen with a 10 line query instead? there should be an underline squiggle on your code SOMEWHERE to show where the parser is getting upset.

      You can of course just completely ignore those ‘helpers’ if they’re not actually helpful

      1. The warning squiggle is under the entire 94 lines of the Select statement, but I tried your suggestion of reducing the code … and I found the ‘line’ that SQL_Dev doesn’t like:

        case when (select count(*)
        from tpsdba.validation_exceptions
        where cust_no = tpsdba.shipment_header.cust_no
        and validation_no = 6020) > 0 then ‘Yes’
        else ‘No’
        end validation_exception_6020,

        As you noted, it’s just a warning, and runs perfectly fine anyway – but is there another way to write this that won’t get SQL_Dev upset?

        TIA,

        1. I am never a fan of count(*) to find out if the value is or is not zero. And it is the count that is fooling the sqldev parser thinking it needs a group by

          Here is an example using exists from scott/tiger

          SELECT D.* ,
          coalesce( ( SELECT 'Yes' FROM dual WHERE EXISTS
          ( SELECT 1 FROM scott.emp e WHERE e.deptno = d.deptno ) ) , 'No' ) has_emps
          from scott.dept d

          1. Thanks TR. I agree that using EXISTS is better than COUNT when I don’t need to know the actual number, but I’m not sure that the COUNT is where the parser is confused – I changed the code to remove the CASE and just leave the subquery (including the COUNT) and the warning went away.

            (select count(*)
            from tpsdba.validation_exceptions
            where cust_no = tpsdba.shipment_header.cust_no
            and validation_no = 6020) validation_exception_6020,

            The users can figure out Yes/No by the results of Zero or Non-Zero.

            But thanks for replying.

      2. “Select list inconsistent with group by” can show up in reports involving “union all”:

        select null, count(*), avg(sal)
        from emp
        where comm is null
        union all
        select deptno, count(*), avg (sal+comm)
        from emp
        where comm is not null
        group by deptno

        adding “group by null” clears the warning.

        I do not prefer to add “group by null” because:
        – it is not required
        – it leads to proliferation of clutter in the code base
        – it spreads misinformation and misunderstanding

  101. Jeff,
    I’ve used SQL*Developer to import spreadsheet data into Oracle tables for projects. Recently did an import and it created 1,332 rows…but functional user kept saying there were only 1,205 rows in the spreadsheet. The difference was that 127 rows were hidden within the spreadsheet, but apparently the import found them… like lost sheep. Would it be possible to add a selection to the import feature to ‘ignore hidden rows’?

    1. thatjeffsmith Post
      Author
      1. According to the Apache POI documentation for Excel you can call getZeroHeight() for a row read. If it returns ‘true’ the row is hidden. If it returns ‘false’ the row isn’t hidden. Wouldn’t that work?

        Dan

        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author

            unofficial way, go to sqldeveloper.oracle.com and open an item – your friends can vote it up

            official way, go to my oracle support and open a service request on sql developer

            I recommend route #1

  102. Hi Jeff,

    A feature suggestion for the Cart. Add a checkbox to disconnect from the Destination Connection after a Copy Objects operation is completed. Not having an option like this leaves these connections open. A related comment, when a tree is collapsed and connections are in folders there is no indication where any connections are opened.

    Later there is a risk of e.g. doing something against objects with the same name in the wrong database. A SQL Worksheet window remains open to that connection and it is possible to inadvertently paste code & execute code in it. I also wish there could be a better way to visually discern different connections. I use now the colored frame that can be setup for a connection, but e.g. a different background color or pattern would be probably better.

    Thanks!

    1. thatjeffsmith Post
      Author
      1. Perhaps it could be something very subtle that could be used for alerting that it is production, etc. We don’t work there most of the time anyway.

        What about the other thoughts I had, for the cart and to indicate that folder has open connections? Perhaps you want me to post this on the forum instead? I wasn’t sure where is the better place.

        1. thatjeffsmith Post
          Author

          But we already have something very subtle – the connection color feature. It draws a border around the windows AND it colors the connection text in the worksheet and tree.

          I don’t like the idea of automatically closing connections to be honest

          Having a decorator on a folder though to show it has one or more open connections under it isn’t a horrible idea. I’ll mention it to the developer.

          1. Jeff,

            I know what you saying. However, consider this scenario. There is a frame around around the worksheet in production (these are very tiny and sometimes almost not noticeable if one gets carried away with some work). At some point you switch away from that worksheet (actually not really relevant). The connections are still open. Say, all connections have all tables expanded and there is a good number of them (doesn’t have to be a lot). Maybe you want to rename a table in development. By mistake you pick the table in production with the same name …….. The font and color are exactly the same. There is no way to tell in which database/schema the table is located. You have to scroll up to check the location. The color is only different for the connection name.

          2. thatjeffsmith Post
            Author

            Right, but consider this – maybe if production is involved, you don’t even have it in your SQL Developer list of connections. You’d only ever push changes there via automated jobs/sqlcl/sqlplus…or if you know PROD is lurking around, you slow down, and double-check, every time.

            We could do more to show connection stuff in the tool, but folks would still need to learn how to stop running with scissors. I’m willing to look at this again, but I’m not seeing it as a critical issue today in terms of feedback from users.

          3. Jeff, I am not sure that I agree with that point. There is so much more functionality in SQL Developer than there used to be few years ago, including the DBA tab, Change Management, etc. I think this functionality conflicts with your suggestion not to include production connections. Also all connections are managed through one common place, i.e. Connections. Let’s forget for a moment about production and consider other pre-prod environments. Perhaps you could consider, in addition to changed connection text color, a colored frame around the expanded objects (the area in the browser) under a particular connection that needs caution or to be different for some reason.

          4. thatjeffsmith Post
            Author

            I’m saying if you’re having problems remembering if your connection is PROD or not – consider having a locked down copy of SQLDev that’s ONLY for PROD.

            At the moment, we’re not looking at doing more to call out specific connections with color themes.

  103. Sqldeveloper unit testing : How do I check values of a user defined collection used as a parameter to a procedure or function as part of the assert outcome. The collection is at the database level as an object. I’d like to be able to assert along the lines of coll(1).rec.item = somevalue
    Thanks for any help

    1. thatjeffsmith Post
      Author
      1. Thanks for response, I’ve posted this on the community under SqlDeveloper.
        Title: SQLDeveloper Unit Testing Collections when used as parameters to program units
        Tag: unit test
        Regards
        Andrew

  104. Sql Developer 4.2.0 has one issue that prevents me from migrating the team to using it. There are cases where we develop a sql statement then open a sys_refcursor based on the string of that SQL statement. In 4.1.5 the Crtl-Shift-F7 brought up a dialog with an option to build a concatenated string. In 4.2.0 there is no such option in the dialog.

    What would it take to bring that feature back?

  105. In Version 4.2.0.17.089.1709 the “File > Compare With” options are grayed out. Is this feature no longer supported?

    1. thatjeffsmith Post
      Author
  106. Hi Jeff

    Thanks to your explanation about SQL Editor Code Templates (August and November 2014) I was able to setup some nice templates and more will come.
    But what’s about the second table on the SQL Editor Code Templates pane. The one with the four rows, starting with Id #time#. How could it be used and why is this table not editable?

  107. I am trying to use JDK 1.8.0_131 with sql developer 4.1.5.21.78 windows 64 bit.

    I get error

    Incompatible version of libocijdbc[Jdbc:121020, Jdbc-OCI:122010

    Anybody have any help please? I have spent 3 hours on this issue so far

    1. thatjeffsmith Post
      Author
  108. Hi Jeff,

    Is it possible to include the contents of a bind variable in a graph title in a SQL Developer chart report?

    If so, how?

    Thanks,

    Bill

    1. thatjeffsmith Post
      Author
  109. Jeff,

    I want to setup Unit testing module without using the GUI for 100+ users so that they dont have to go to the screen every time .

    Is there any API or script made available by oracle to setup unit testing ( from creating user for the repository , provide grants to the user, create repository and providing access to users ) so that we can customize as per the needs or its not possible at all.

    any suggestion is appreciable.

    1. thatjeffsmith Post
      Author

      >>so that they dont have to go to the screen every time
      Go to the screen to do what?

      You’re asking for a way to configuring the unit test repository connection automatically for a group of users so they don’t have to?

      1. requirement is to create the setup (create users connections, repositorys for the unit tests and drop the same) on demand. As the number of users and multiple databases, is there any way to do same via script or command by providing the details .

        Thanks Jeff.

        1. thatjeffsmith Post
          Author

          spinning up the repository(ies) could definitely be scripted. Create it with the GUI, then export it out to whatever script type you’d like.

          There is a sdcli command to create a connection, I’ve not used it before though.

          Having your users associate the connection to the UT repos, they’d have to do that.

  110. Hi

    Could this be made a feature in SQl Dev:

    Datamodel like view on a view

    Ie. View xxxx – includes 4 tables x,y,z,v, and the x is a view again …..
    So that you from one view could see in DM format all the tables involved and what links them together (where/on) clauses ?

    really usefull when troubleshooting

    1. thatjeffsmith Post
      Author
      1. Hmmm … but if I do ie

        LECT session_key, NVL2 (MIN (incremental_level), ‘:’ || MIN (incremental_level), NULL) co
        FROM V$BACKUP_DATAFILE_DETAILS d
        GROUP BY session_key
        )
        SELECT command_id AS “Backup Id”,
        b.status AS “Status”,
        TO_CHAR (b.start_time, ‘dd.mm.YYYY HH24:MI:SS’) AS “Start Time”,
        TO_CHAR (b.end_time, ‘dd.mm.YYYY HH24:MI:SS’) AS “End Time”,
        b.time_taken_display AS “Duration”,
        b.input_type || d.co AS “Type”,
        b.output_device_type AS “Device”,
        b.input_bytes_display AS “Input Size”,
        b.output_bytes_display AS “Output Size”,
        b.output_bytes_per_sec_display AS “Output Rate (Per Sec)”
        FROM V$RMAN_BACKUP_JOB_DETAILS b, SUMMARY d
        WHERE b.start_time > TRUNC (SYSDATE) – 14
        and b.session_key = d.session_key (+)
        ORDER BY b.start_time DESC

        I just the 2 “tables” involved – not the underlying views – I would like a model of whats going on underneath in a modellike drawing.

        Ideal when debugging

  111. Hi, Is it possible to migrate Sql Server 2008/2012 database to Oracle using sdcli feature of Sql Developer 4 ?

    1. thatjeffsmith Post
      Author

      Yes, but you wouldn’t do that for the first migration. You’d use the SQLDev GUI to run through the migration first. Then if you needed to automate steps of that migration going forward, THEN you’d use SDCLI.

      1. Hi Jeff,
        I have successfully migrated Sql Server db to Oracle using GUI. But when I execute below mentioned command I get “Error:java.sql.SQLException: Driver class not found. Verify the Driver location”.

        sdcli migration -actions=capture -conn=sqlserver

        Driver Imported to sql developer – jtds-1.2.jar

        1. thatjeffsmith Post
          Author

          I think you might need to open a support ticket, or maybe post this on the Migrations Forum page, so you can say exactly what you’re doing in full detail.

          Also, you will get better migration with jtds 1.3

  112. Hi Jeff,
    I am interested to see if there is a way to save a project that has multiple worksheets as a single project that can be opened (and all the separate tabbed worksheets come up when the project is opened in SQL Developer). is there a way to do this?

    1. thatjeffsmith Post
      Author
    2. Hi Jeff,

      Could you please help me to understand which are the unsupported datatypes that can be passed as arguments for an oracle procedure when trying to test using the Unit Test of SQL Developer (3.2.20.10). Thanks in advance.

  113. Hey Jeff,

    I wasn’t able to find an email to reach out to you with. I am reaching out here because you are using an embed from our website and unfortunately it is now in violation of Google’s terms and conditions. In order to avoid disavowing your website we will need to have it changed as soon as possible. Sorry for the inconvenience, please reach out to me at the provided email within the next 72 hours so we can help get it fixed before submitting our disavow. Thanks!

    1. thatjeffsmith Post
      Author
  114. Jeff,

    Thanks for the site. Solving question before being asked, like http://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/

    But in testing some code I tried as I would in Sql*Plus. Example at https://community.oracle.com/thread/888365.
    This examples show printing the output of a refcursor and that the refcursor is closed after the print statement. To see the issue change the select in line 4 to ‘SELECT * FROM all_objects’

    Two problems I experienced
    1 The print command ( borrowed from Sql*Plus) does not honor the pagesize and linesize settings. Other session variables do honor these setting, so it is only refcursor with an issue.

    2 In Sql*Plus print closes the refcursor, while sqlcl and Sql Developer do not.

  115. set linesize 32767; not working in logon.sql on windows 7 sql-devleper 4.2 (no jdk ver-jdk8u131). any suggestions?

    1. full script…
      alter session set nls_date_format = ‘YYYY-MM-DD HH24.MI.SS’;
      alter session set nls_timestamp_format = ‘YYYY-MM-DD HH24.MI.SS’;
      alter session set nls_timestamp_tz_format = ‘YYYY-MM-DD HH24.MI.SS TZR’;
      set linesize 32767;
      set newpage NONE;
      set pagesize 50000;
      set trimout OFF;

      1. thatjeffsmith Post
        Author

        We auto set linesize to the width of the output panel…Use a show linesize and resize the window to see for yourself.

        Add the set linesize to your script to override the dynamic setting

        1. i did set it in my script, but it does not stay the value i set it? i have wide tables and want to see data without wraping. how do i get it to set in the script so it stays instead of reseting to width of window?

          1. thatjeffsmith Post
            Author
          1. thatjeffsmith Post
            Author

            I already explained it to you, once the script output window opens, we see how wide it is, and then set that to = linesize.

            if you want a terminal experience, open a terminal…or force linesize to what you want in your script

        2. there is got to be a way to set it once instead of on every sql? the setting in the login.sql should override settings even after the ui load.

          1. thatjeffsmith Post
            Author
          2. so, i have to set linesize every time after i make a new connection to solve this?

            can we get an word-wrap option in sqldeveloper? giving a word-wrap option, like other developer programs, would be a great help. this way when someone unchecks the word-wrap option it sets the the linesize to maximum for that system.
            please give this serious consideration for an option in future versions. i am sure it would be much appreciated by the sqldeveloper community.

            thanks for your time though, i was going crazy thinking it was me.

          3. as a side note, doesnt adding display code in with sql code breaks separation of concerns (SoC)?

          4. ps. i donloaded sqldeveloper4.2 no-jdk, installed jdk131. ran sqld and it wraps by default. ran select * from table and i didnt play with output.

  116. Hi there,

    I am on Windows 10 64-bit and SQL Developer 4.2.0.17.089. In Preferences -> File Types I set the .sql file type to be opened with SQL Developer, a green marker is set in the “Open with SQL Developer” column, so I click OK. But my .sql files aren’t opened with SQL Developer and when I enter Preference -> File Types again the green marker has gone. Is this a bug?

    Thanks,
    Bud.

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        Found it. One has to run sqldeveloper.exe as an Administrator to make and keep the File Types settings.

  117. Still learning my way around. I coded a package that does not do any commits (or rollbacks). I made sure auto-commit is not checked in my Preferences. However, when I used the editor to run an anonymous pl/sql block to execute my procedure (in my pkg) — the data was committed to the database. I found this out when my rollback did not work.

    Thoughts?

    I really want to be in control of when commits are done. I often use the same data in my test and rollback between each execution.

    1. thatjeffsmith Post
      Author
  118. Hi Jeff,

    After reading one of your post on SQL Developer reports, i started using them. Undoubtedly they are un-explored powerful feature of sql developer.
    Using these reports, i started automating many of my daily tasks and analysis queries. This saves a lot of time for me.
    I am using V4.1.5 and having an issue with bind values in child report.

    I have a report which is using couple of bind variables. When i configure this report as a child report to other, on first instance it’s invocation works correctly by taking bind variable value from the parent report. But when tried to open same child report as standalone or from another report, still it shows the result for the value passed from the first report.
    When i checked the bind values for the child report, it’s storing the value for bind variable from first report (in place of NULL_VALUE) and hence any later report invocation is giving same result.

    So, every time i need to clear bind value of the child report before running it again.
    Is there any setting which controls this?
    I tried with V 4.2 but still having same issue.

    Thank you

    1. thatjeffsmith Post
      Author

      >> But when tried to open same child report as standalone or from another report,
      I’m not clear on this, exactly how are you making your child report available somewhere else?

      1. One of the report has few bind variables in it’s where clause and this report is set as child report for 2-3 other reports.
        When this report is invoked from a parent report, it’s storing the passed in bind variable in the child report bind value section.
        Later run of the report as standalone or from another report, still it’s giving same old result.
        Only when I go and reset that bind value in child report to NULL_VALUE, then it’s either prompts for value (standalone run) or accepts it’s value from parent query correctly.

    1. thatjeffsmith Post
      Author
  119. Hi Jeff. I’m a huge fan of SQL Developer and I’ve been using it since it started as the Raptor project. Right now I’m facing an issue with the new release 4.2 as it keeps dropping the opened sessions, when running a simple query from a SQL window or trying to edit some object. I have installed also version 4.1.5 and have no issue with that one. This started to happen with 4.2 EA version, and I thought it was some sort of a bug that should be solved on the final release, but I’m still facing it with this new release. I’m using Win10, have installed Java 1.8.121 and doing the connections using JDBC (Basic Connection Type) and it’s the same I used for version 4.1.5.
    I also tried adding : AddVMOption -Doracle.net.disableOob=true to the sqldeveloper.conf file, but it didn’t help. Any ideas on how can I troubleshoot and fix this issue? Thanks in advance for your reply.

    1. thatjeffsmith Post
      Author

      It’s a known issue and we’re working on a fix.

      Some, but not all customers are running into it. Here’s how to avoid it, if you have a 12c Instant Client available, USE IT. That should avoid the issue, which lies in the 12.2 JDBC driver itself.

      Otherwise, I would go back to using 4.1 until our fix is available.

      1. Thanks for the quick answer Jeff, I wasn’t aware it was a known issue. I already went back to 4.1.5, and will check periodically for the fix.

  120. I am NEW to sql*developer but NOT NEW to pl/sql.
    Just started a new job where I want to use sql*developer – I’ve been enjoying your videos.

    QUICK QUESTION: I was surprised to find out that compiling a package automatically writes the package to disk. Is there a way to change that? I only want to write to disk when I choose “file -> save” or “file -> save as” . I had removed a bunch of code from a pkg to try something crazy and was surprised my pkg on disk had been modified automatically. I can’t find a discussion on this.

    Is what I want to have happen, possible? How?! Thanks –

    1. thatjeffsmith Post
      Author

      No i don’t know of a way…but you could undo the changes and save the file..that won’t do a compile.

      If you look at the History panel, you could also get back where you had been before.

      1. Thanks for the quick reply and groovy answer – no one writes about this! I will definitely use the History panel.

        Today I pinned things and created my own short cuts and even floated a window to my other monitor – all based on info I got from your site. It’s fun.

        Signed,
        I might be a nerd.

  121. Semicolon at the end of the line comment of SQL query ends the scope of query?

    For example this

    select *
    from table
    –where 1=1;
    where 1=0;

    with the cursor before the semicolon selects all.

    Is that the desired behavior in the new Oracle SQL Developer 4.2?
    I dont think so. At least in older versions was this different.

    If i put some chars behind the semicolon on the same line, the scope of the query will be processed correctly ant the result is nothing.

    1. thatjeffsmith Post
      Author
  122. Hi Jeff,

    I have an issue pasting into sqlcl.
    When trying to paste multi-line sql statement into sqlcl, it is pasted into multiple commands.
    For example, copying the following sql statement:
    SELECT /*+ MONITOR */ d.dname, LISTAGG(e.ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
    FROM scott.emp e , scott.dept d
    where e.deptno = d.deptno
    GROUP BY d.dname
    ORDER BY d.dname;

    it will end splitted into 5 separate commands not one command as follows:
    SQL> SELECT /*+ MONITOR */ d.dname, LISTAGG(e.ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
    FROM scott.emp e , scott.dept d
    where e.deptno = d.deptno
    GROUP BY d.dname
    ORDER BY d.dname; SELECT /*+ MONITOR */ d.dname, LISTAGG(e.ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees

    Error starting at line : 1 in command –
    SELECT /*+ MONITOR */ d.dname, LISTAGG(e.ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
    Error at Command Line : 1 Column : 96
    Error report –
    SQL Error: ORA-00923: FROM keyword not found where expected
    00923. 00000 – “FROM keyword not found where expected”
    *Cause:
    *Action:
    SQL> FROM scott.emp e , scott.dept d

    Error starting at line : 1 in command –
    FROM scott.emp e , scott.dept d
    Error report –
    Unknown Command

    SQL> where e.deptno = d.deptno

    Error starting at line : 1 in command –
    where e.deptno = d.deptno
    Error report –
    Unknown Command

    SQL> GROUP BY d.dname

    Error starting at line : 1 in command –
    GROUP BY d.dname
    Error report –
    Unknown Command

    SQL>
    ORDER BY d.dname;

    Error starting at line : 1 in command –
    ORDER BY d.dname
    Error report –
    Unknown Command

    Any inputs/workarounds to fix this?

    Regards
    Ahmed

    1. thatjeffsmith Post
      Author
      1. I’m using win 10. I tested from CMD, CYGWIN and MobaXTerm.
        It didn’t work from all the three terminals.
        If I opened a sqlplus session from the same CMD , and tried exactly the same sql, it will work.

        Note that this sql statement is copied from somewhere in the internet.
        If I write it myself in Notepad, It will work from sqlcl on CMD, but not from sqlcl on Cygwin or MobaXTerm.

        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
          2. thatjeffsmith Post
            Author
        2. If I may jump in here I have seen this before, This is related to charset and or line termination. Look at the pasted code with a hex editor and compare the end of lines with code that works. The end of line should be CRLF or just LF ( 0x0a ) .

          1. Thanks Rudkin for your inputs.
            I noticed that for the code that works, each line end with CRLF, but for the one that is not working , it is ended up with LF only.

            It worth mentioning that sqlplus absorbs this difference without issue, but sqlcl error out with lines that end up with LF only.

            Also, it worth mentioning that if you just copy/paste the code that has this issue into a text editor like “Notepad++’, it will automatically adjust the end of lines to CRLF, then if you copied that code from Notepad++, it will work fine.

            I’ll have a separate post about this point shortly, I’ll share it with you once completed.

            Thanks Rudkin & Smith for your support.
            Ahmed

  123. I would love a way within SQL Developer to switch connections in the script. One that used the SQL Developer stored connections with the password, since including the password in the script is a no-go. Something like

    conn !my_connection_name_here

    I use this constantly on Microsoft SSMS using the syntax

    :connect server_name_here

    Any chance we’ll see that in SQL Developer or the cl?

  124. Thanks Jeff for clarifying that repository can be created only by a highly privileged user.

    what DB privileges are needed for the user to have the ability to create the repository?

    1. Hi Jeff
      the user I am using has all elevated privileges including
      Select on DBA_TAB_PRIVS
      Execute on DBMS_LOCK

      however, still I am getting the error.
      is there any other role / priv we are missing.
      thanks in advance

  125. I am trying to use Oracle SQL developer 4.2 for UNIT testing. And would like to create repository with the same user that I am logged in. I get a message “Incorrect repository version: you must upgrade this repository to use it”
    how can I update this or create a new one?
    do I need DBA privileges for this purpose

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. Hi Jeff
            I am missing something ….

            Our Oracle DB is 11g and am using Oracle SQL Developer 4.2.
            created the connection to the DB
            Then View–>Unit Test
            this created a repository Unit Tests
            when I tried to associate this to the main DB connection, gives a message
            “No repository was found on the selected connection.
            would you like to create one now ?

            when I click Yes, the msg is
            “Required roles do not exist
            You will now be prompted for connection info to grant needed permissions”
            and then it prompts for sys pwd.

            is it necessary that only sys has this role to create another repository?

            is there any other way to create the repository.

            Also I am not able to get the
            UNIT TEST NAVIGATOR window

            can you guide step by step as to how I can start creating repository and test cases ?

          2. thatjeffsmith Post
            Author
  126. We have been using the modeler read all the objects in an APEX schema (tables, views, constraints, indexes, sequences, triggers) for this project and we have run across some issues:

    • The table FCI_L_MAJOR table is invisible within the model. I learned of this because the FCI_ENROLLMENT table has a foreign key constraint with the FCI_L_MAJOR table that link does not appear in the model either. So I created another version of it in the model and when I tried to create the foreign there in the Referenced Table dropdown you see both tables
    • When I exported the SQL from the model it’s fine except that the sequences don’t have the START WITH clause which is important since there is data already in the tables.
    • When I imported the schema I tried to suppress the schema name from the model what happened is that no objects came across into the model.

  127. hi Jeff
    We are using SQL Developer version 4.1.3.20

    The problem for some developers is that when they open in SQL developer file with extension ‘pkg’ – the file is open in text editor and SQL developer does not give the options to compile it
    The other developers can open the same file type in pl/sql editor.
    None of developers have pkg file type defined in Tool – preferences – file types.
    What is the problem here and how can we resolve it to be consistent for all developers.

    TIA

    1. thatjeffsmith Post
      Author

      we have pks and pkb not pkg in the extension preferences.

      What happens if they open a pkg spec or body from the DB and then save it as a pkg file?

      If I do that, and then close it, and open the file, it goes into the pl/sql editor with the compile and other pl/sql specific features available.

  128. Hi, struggling with the way SQL-DEV (SD) displays dates vs TOAD in the query results . I rely on timestamps! So I changed my SD pref to add HH:MI:SS AM to the date format in DB / NLS setting. When running a query that truncs other date fields that I only need to see the short date, SD displays DD-MON-RR 12:00:00 AM.. In TOAD, the trunced date displays as expected, just the date…
    any guidance is appreciated…

    1. thatjeffsmith Post
      Author

      They’re artificially hiding the time from you – if you ask me, they’re training you to learn a bad habit.

      If the time component is important to you, then build it into your query, and don’t rely on what NLS is set to. Even if the time is midnight, it’s still there – either you want to see the time, or you don’t.

  129. Hi Jeff, I have read several tips on making SQL Developer faster, but I have not found the problem I am experiencing as being addressed.
    In short this is NOT a SQL running slowness. I am experiencing a 1-2 minute delay when I click on the export option on a SQL result set? There are additional delays in traversing the explore file structure. Once I get a file type and name selected I am fine, but getting there is becoming progressively slower.
    I am running Windows 7 on a Lenovo with 4GB mem.

    1. thatjeffsmith Post
      Author

      With SQL Developer CLOSED, try this

      I went under this folder – “AppData\Roaming\SQL Developer\system4.1.5.21.78\o.sqldeveloper.12.2.0.21.78” and opened “product-preferences.xml”.
      Went to the line
      hash n=”URLFileChooserPaths”
      list n=”DEFAULT_CONTEXT”
      url protocol=”file” path=”/G:/Queries/Reference.sql”

      and under list n=”DEFAULT_CONTEXT” I had LOTS of file paths. I deleted them and kept couple of them which I really need. That made ALL the difference.

      1. Thanks! I had about 75 URLs in there that I didn’t need. It sped up some from that change. I still have about a 30 -60 second wait from when I right click export, before I see the export wizzard screen.

        1. thatjeffsmith Post
          Author
          1. Yes, that might be it. Anyway, the response time did improve with your recommendation. Thanks!

  130. Hi Jeff,

    I have entered an Oracle support bug last year, b/c we are migrating 20+ TB of Sybase databases to Oracle using SQL Developer. We have found that single column primary keys (which is over 90% of primary keys) don’t migrate correctly.

    Here is the support ticket:

    SR 3-12869054401 : SQL Developer migration tool omits column name in single column primary keys

    Here is the response from Oracle support:

    “The workaround is to use 2 keys or more for the PK to get passed this issue. ”

    What are your feelings on this? Going back and adding 2 columns to each primary key in our legacy Sybase databases just to migrate them to Oracle doesn’t sound like a solid plan.

    Thanks,
    Brian

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
  131. Hi Jeff

    First, *many* thanks for your contribution – you’ve been a lifesaver more times than I’d like to admit!

    Sorry if my post is a bit long, but I want to give you as complete a view as possible.

    I’m trying to build a dev environment on my Ubuntu 16.10 box. I’ve set up an Oracle VM with Centos7 and installed Oracle 11.2 in it.

    Since the version that comes with the DB is *way* too old, I’m trying to install sqldeveloper-3.2.20.09.87 (I need this version due to a plugin requirement).
    I unzipped the file into /opt. I then ran the following and copy-paste the output here:


    $ java -version
    openjdk version "1.8.0_121"
    OpenJDK Runtime Environment (build 1.8.0_121-b13)
    OpenJDK 64-Bit Server VM (build 25.121-b13, mixed mode)

    $ which java
    /usr/bin/java
    $ ls -la /usr/bin/java
    lrwxrwxrwx. 1 root root 22 Mar 1 16:08 /usr/bin/java -> /etc/alternatives/java
    $ ls -la /etc/alternatives/java
    lrwxrwxrwx. 1 root root 73 Mar 1 16:08 /etc/alternatives/java -> /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/jre/bin/java
    $ ls -la /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/jre/bin/java
    -rwxr-xr-x. 1 root root 7344 Jan 20 19:37 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/jre/bin/java

    $ /opt/sqldeveloper-3.2.20.09.87/sqldeveloper.sh

    Oracle SQL Developer
    Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved.

    Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ~/.sqldeveloper/jdk
    /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64
    Error: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/bin/java not found

    Additionally (as root):

    # find / -type f -name java
    /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.131-2.6.9.0.el7_3.x86_64/jre-abrt/bin/java
    /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.131-2.6.9.0.el7_3.x86_64/jre/bin/java
    /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/jre/bin/java
    /var/lib/alternatives/java
    /ora01/app/oracle/product/11.2.0/db_1/jdk/bin/java
    /ora01/app/oracle/product/11.2.0/db_1/jdk/jre/bin/java
    /ora01/app/oracle/product/11.2.0/client1/jdk/jre/bin/java
    /ora01/app/oracle/product/11.2.0/client1/jdk/bin/java

    So, what is it? Do I have an SDK for sqldev to work?
    What must I do to make sqldeveloper-3.2.20.09.87 start, *without* messing with the DB (and its java) installation?

    Thanks in advance for your time

    Greg

    1. thatjeffsmith Post
      Author

      Yes, you need a JDK.

      For a version that old, Java 6 is probably ok…But we don’t support open JDK, only Oracle Java. You could probably point it to the jdk in the Oracle home bin.

      1. Hi Jeff and many thanks for your answer.

        I wasn’t aware that only Oracle Java is supported – as a matter of fact, I’ve installed sqldeveloper-3.2.20.09.87 using /usr/lib/jvm/java-8-openjdk-amd64 and it works fine – but it’s in the host (Ubuntu), not in VM (Centos).

        In the VM, pointing to either db or client’s jdk (they are the same) leads to error (the same error for both):


        $ /ora01/app/oracle/product/11.2.0/db_1/jdk/bin/java -version
        java version "1.5.0_51"
        Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_51-b10)
        Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_51-b10, mixed mode)

        $ /ora01/app/oracle/product/11.2.0/client1/jdk/bin/java -version
        java version "1.5.0_51"
        Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_51-b10)
        Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_51-b10, mixed mode)

        $ /opt/sqldeveloper-3.2.20.09.87/sqldeveloper.sh

        Oracle SQL Developer
        Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved.

        Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ~/.sqldeveloper/jdk
        /ora01/app/oracle/product/11.2.0/db_1/jdk
        Exception in thread "main" java.lang.UnsupportedClassVersionError: Bad version number in .class file
        at java.lang.ClassLoader.defineClass1(Native Method)
        at java.lang.ClassLoader.defineClass(ClassLoader.java:621)
        at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124)
        at java.net.URLClassLoader.defineClass(URLClassLoader.java:260)
        at java.net.URLClassLoader.access$100(URLClassLoader.java:56)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:195)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
        at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)

        If I install Oracle java(jdk) 6 or 8 in the VM, what must I do to make certain that the db is not affected (PATH etc)?

        Thanks once more
        Greg

        1. thatjeffsmith Post
          Author

          Don’t run sqldev on the VM…Save those resources for your database.

          Install sqldev on your host, and grab version 4.2 with Java 8, and just connect to the database on your VM. Then you have nothing to worry about.

  132. Hi Jeff

    Downloaded Sqldeveloper (V4.2.0.17.089, Build 17.089.1709) and every now and then getting Connection reset message

    And Code Outline, it does not shows outline for some of the packages, can see below messages in logging page

    SEVERE 17410 655 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.dbtools.raptor.plsql.structure.OutlinePanel.getPopupMenu(OutlinePanel.java:385)
    SEVERE 17409 200 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.dbtools.raptor.plsql.structure.OutlinePanel$2.mouseClicked(OutlinePanel.java:153)

    1. thatjeffsmith Post
      Author
  133. The table is there. The table does not exist. sqlcl-4.2.0.17.096.0933

    SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = ‘DIM_FCTS_CMC_CLMD_IDCD_CD’;

    TABLE_NAME
    ——————————
    DIM_FCTS_CMC_CLMD_IDCD_CD

    SQL> DESCRIBE DIM_FCTS_CMC_CLMD_IDCD_CDERROR:
    ——————————————————
    ERROR: object DIM_FCTS_CMC_CLMD_IDCD_CD does not exist

    1. thatjeffsmith Post
      Author
  134. SQL CL v4.2.0.17.073
    Windows 7

    I have a local variable for TNS_ADMIN
    in SQLDev I can use the TNS alias
    In SQLCl I get an error where SQLCl is looking for jdbc connect string
    If i use the jdbs connect string with /Service_name then I can connect.

    What am I missing in making the tnsnames alias to work?

    1. thatjeffsmith Post
      Author
      1. HI Jeff,

        I am using SQL Developer Data Modeler 4.15 and I am using the compare feature of the modeler but I am getting some odd results when I read the data dictionary from an APEX 5.0 instance. What I am getting is additional tables that are not in the schema, any idea why this is happening?

        1. thatjeffsmith Post
          Author
          1. The list of tables are below and they are the same the two tables that are not in either model are agency and AGENCY_CONTACT . These two tables are not there.

            FCI_ADDRESS
            FCI_ENROLLMENT
            FCI_HOUSING
            FCI_INIT_ENROLL_INFO
            FCI_INTERNSHIPS
            FCI_L_AGENCY
            FCI_L_CODE
            FCI_L_COLLEGE_DEPT
            FCI_L_CONTACT
            FCI_L_COUNSELORS
            FCI_L_INTERNSHIP_TYPE
            FCI_L_MAJOR
            FCI_L_MEETING_TYPE
            FCI_L_PROGRAM
            FCI_MEETING
            FCI_STG_ROSTER
            FCI_STUDENT
            FCI_STUD_CONTACT

          2. thatjeffsmith Post
            Author
          3. The tables I am talking about are not APEX tables but user created tables that I am comparing to the model. I selected the application tables I created in the in my workspace for the FCI application and compared it the FCI model in the modeler version 4.15. What I got is two tables that are not in the model or in the schema I was comparing. That is AGENCY and AGENCY_CONTACT which are in neither. Why?

          4. thatjeffsmith Post
            Author
  135. When using the Sqlcl “ddl” command to retrieve the source for a proc or package, empty lines within the source are being dumped. Is there some-or-another option that controls this?

    1. thatjeffsmith Post
      Author

      Blank lines?

      There are options for how the DDL is shaped.

      I am HR ON orcl > SHOW ddl
      STORAGE : OFF
      INHERIT : ON
      SQLTERMINATOR : ON
      OID : ON
      SPECIFICATION : ON
      TABLESPACE : ON
      SIZE_BYTE_KEYWORD : ON
      PRETTY : ON
      FORCE : ON
      REF_CONSTRAINTS : OFF
      PARTITIONING : ON
      CONSTRAINTS : OFF
      INSERT : ON
      BODY : ON
      CONSTRAINTS_AS_ALTER : ON
      SEGMENT_ATTRIBUTES : OFF
      1. A proc that should have empty lines..

        SQL> create or replace procedure
        2 uselessproc(
        3
        4 — What a useless argument
        5 LevelOfUseless varchar2
        6 )
        7 as
        8 begin
        9
        10 /*
        11 This proc has plenty of open lines in it
        12
        13 Open lines are important to the readability of code
        14 */
        15
        16 dbms_output.put_line(‘I do nothing useful ‘);
        17
        18
        19 end;
        20 /

        .. is returning without them..

        SQL> ddl uselessproc;
        CREATE OR REPLACE EDITIONABLE PROCEDURE “SCOTTM”.”USELESSPROC”
        (
        — What a useless argument
        LevelOfUseless varchar2
        )
        as
        begin
        /*
        This proc has plenty of open lines in it
        Open lines are important to the readability of code
        */
        dbms_output.put_line(‘I do nothing useful ‘);
        end;
        /

        1. If you have setup the formater to your liking, you can do:

          SQL> ddl uselessproc;
          SQL> format buffer

          Not prefect but better the eating blank lines.

  136. Hi Jeff

    Thanks for your site. it is really useful.

    In Oracle SQL Developer, how can I increase the number of entries shown in the
    File -> Reopen menu?

    Thanks in advance
    Regards
    Vadi
    Bengaluru, India

  137. Hi Jeff,

    we’re attempting to integrate Oracle SQL Developer unit testing with Bamboo, and in doing this we have to first setup the DB connections. The Bamboo script can run on multiple agents, so it’s best to create a connection first (passwords can be stored securely in Bamboo), then import and run the tests, then remove the connection. So here’s what we tried for creating the connection (Windows PowerShell):

    .\sdcli migration -actions=mkconn -connDetails=”MyConnection:oracle:TEST_USER/[email protected](DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBSERVER.SOMECOMPANY.COM)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB123.SOMECOMPANY.COM)))”

    …or with TNS:

    .\sdcli migration -actions=mkconn -connDetails=”MyConnection:oracle:TEST_USER/[email protected]

    In both cases we get the following error message:

    Error:Problem creating connection:Test failed: IO Error: Connect identifier was empty.

    Do you have a clue what we’re doing wrong? Thanks a lot for any help.

    Jan

  138. Jeff,

    I am trying to improve my workflow by reducing the number of times I export data from SQL Developer into Excel. I often find myself copying data from SQL Developer to Excel in order to quickly locate a column in a large set of data.

    When working in SQL Developer’s Query Result window, CTRL+F only evaluates the data returned from my query, excluding column headers.

    Is there a way to quickly search in the Query Result window for a column name? I do not see anything listed in the Shortcut Keys section of Preferences.

    Connor

    1. thatjeffsmith Post
      Author
      1. Thanks Jeff. Single Record View does help a tad. It would be great to have “Column Names” as selection in the Options section of the Find window.

        1. thatjeffsmith Post
          Author
          1. Just following up on the improvement of searching in the query results window. My workflow is constantly disrupted when needing to quickly find data in a large set.

          2. thatjeffsmith Post
            Author

            Nothing yet. It’s not scheduled for a release, but that could change. If you want formal updates for an enhancement request, please submit a Service Request to My Oracle Support.

  139. When exporting ODDM (4.1.5) designs to reporting schema, it seems that only the logical diagram can be save as pdf while any subview diagrams can not. I checked DMRS_DIAGRAMS.DIAGRAM_PDF column and records for subview diagrams are empty. Where is the problem or this is the expected feature for this version. The previous version 3.1.2 can creates pdf diagrams for logical or subviews diagrams when export to reporting schema.

    1. thatjeffsmith Post
      Author
  140. Hi Jeff,

    I came across a response from you to my exact issue at http://theoracleemt.blogspot.com. I have posted your response below. I am using version 4.1.5.21 and I normally return queries with several hundred thousand rows. These queries often take a while so before I export to .xlsx I bring all of the data into the grid as to not run the query again. I normally have no problem doing this and then exporting for several hundred thousand rows and roughly 20-30 columns. However lately, Oracle has been hanging, freezing, crashing and I get the memory error. I’m confused as to why this is happening all of a sudden when I am normally able to do it without an issue. Your response is from 2015 so I am wondering if anything has changed since then or do I just need to run the query 2x to export it.

    thank you

    thatJeffSmithOctober 6, 2015 at 10:23 AM
    So from what I can tell, this refers to the scenario where the user attempts to fetch ALL the data from the database to the grid before doing the export.

    There’s only one reason I can think of for doing this – the query takes a very long time to run, and you don’t want to run it again to do the export.

    If the amount of data is high enough to overwhelm the JVM as it’s put into the Grid, then it’s going to ‘hurt.’

    If you’re doing exports, you should never run into memory issues…UNLESS…you’re exporting to Excel and you’re using the older XLS format instead of the newer XLSX format.

    Our library for creating XLS files doesn’t support letting go of the data as it’s written to the spreadsheet, so after 100,000 rows or so the JVM is exhausted and the app will ‘hang’ – or in newer versions it will complain. So almost always:

    + don’t fetch all the data down to the client first
    + use XLSX not XLS

    1. thatjeffsmith Post
      Author

      Are you writing to XLSX?

      Unless the query takes 10 minutes to run, I wouldn’t fetch the data down to the sqldev grid first – asking it to keep 100k+ rows of 20-30 columns is excessive.

      And even if the query takes 10 minutes to run, I’d write a script to spool the data out to a CSV file and run that instead.

      1. I am writing to xlsx. The individual query takes anywhere from 20-30 minutes. I just attempted to export directly instead of filling the grid first and it was running for over an hour so I killed it. I have attempted to spool the data previously and could never get it to work properly. Like I mentioned I normally fill the grid without an issue with a lot of data, it is only recently that it has been a problem.

        1. thatjeffsmith Post
          Author

          So, I’ll assume the query can’t be tuned.

          Have you tried increasing the JVM max heap size?

          >>I have attempted to spool the data previously and could never get it to work properly
          What did you try?

          I would use SQLcl, it’s much lighter weight and shouldn’t have any issue handling that amount of data.

          1. The Query is a bit of a beast but I have tuned it as much as I think is possible. My DBA’s do not allow the creation of temporary tables so it involves a lot of sub queries.

            I was reading about the JVM max heap size but I must admit that is way over my head and I’d be fearful I would break something. Maybe I will have to find someone to assist me with that.

            The spool process appears to be restricted by my DBA’s as well.

            SQLcl is not something I have seen before. I was looking at it on the Oracle site and the key features didn’t seem to be relevant, so not sure what I would use that for.

            It looks like the heap size may be where I need to go, so I will try and hunt down some assistance with that.

            thank you for your assistance as always!

          2. thatjeffsmith Post
            Author

            SQLcl is a better SQL*Plus. Not a GUI. Just a command line interface. So it requires less resources, runs faster, but not as click-button-y as SQL Developer.

            To add memory to SQLDev, just find your product.conf and edit it such that you have this near the bottom
            AddVMOption -Xmx2048m

            That’ll let SQLDev take 2GB of RAM when its running.

            I talk about this in detail here.

  141. Hi Jeff,
    in a sql devloper report i have a bind parameter of the date datatype. i wonder if there is a chance to set the actual date as default vaule (sysdate)?

  142. Hi Jeff,

    I am trying to pick up changes made between models or a model and a database for a materialized view. I want to do this to get the DDL for the mat view – ie the change that is going to be applied to a previous model. When I get to the Pending Changes screen the mat view is not marked with the yellow caution triangle icon as other changes are. So, it appears to not recognize the change. But, if I drill down into the object I know changed until I see the properties in the bottom half of the Pending Changes screen and look at the query by double clicking the elipses it shows me the change. Clicking the generate DDL button does not put the query in the DDL. Is there a setting somewhere that will allow the query changes to be picked up?

  143. Hi Jeff,

    I’m having a weird issue with completion insight on sql developer 4.1.5

    As i’m typing my table aliases and the period that follows, the completion pops up no problem. Same when i type the first letter of the table/view/whatever i’m looking for. However, as soon as I type any more letters, autocomplete goes away and will not return unless i completely erase what I’ve typed and start over. It also will not appear with ctl+space.

    Am I overlooking something silly?

    Thanks

    1. thatjeffsmith Post
      Author
      1. that is correct. i would then have to delete what i’ve typed up until the popup would normally appear (so until the period or the first letter).

        1. thatjeffsmith Post
          Author
  144. Hi Jeff,

    Does SQLcl have some sort of buffer that holds the results of queries? It seems that way given the weird results I’m getting . If so is there some way to clear it?

    In order to demonstrate the different time datatypes in Oracle for some managers, I created a table having a column of datatype TIMESTAMP WITH LOCAL TIME ZONE and inserted some rows using the localtimestamp function. Doing a query on the table without changing the session timezone gives the expected results. However, in the same session, changing the session time zone then repeating the query gives the same results as the first query!!! If I create a new session, change the session time zone and query again, this time I get the expected results.

    In short, the first query on the table returns the expected results given the session time zone but subsequent queries always return the same result regardless of the current session time zone.

    I’ve tried this experiment using SQLplus and a SQL Worksheet in SQL Developer – both of these return the proper result for the current session time zone.

    Am I missing something?

    Thanks.
    Norm

    1. thatjeffsmith Post
      Author
      1. Unless I’m mistaken, NLS_TIMESTAMP_TZ_FORMAT doesn’t apply here – the issue I’m seeing is with the TIMESTAMP WITH LOCAL TIME ZONE datatype – that NLS format only applies to TIMESTAMP WITH TIME ZONE.

        Just for grins and giggles I tried changing both formats (with and without the TZ) – no difference. Remember, I’m only seeing this behavior in SQLcl, not with any other client I’ve tried.

        1. thatjeffsmith Post
          Author

          we don’t ‘listen’ for when date/timestamp formats change in the session – we catch them if you run an ALTER SESSION for example, but if you’re changing it via a script or stored proc, the client won’t know about it and continue to display the time/dates in the old format

          i think to help you i need specific examples of what you’re talking about with a test-able scenario

          1. Here is the results using SQLplus (instantclient 12.1 on Windows 7):

            SQL> desc date_table
            Name Null? Type
            —————————————– ——– —————————-
            TIME_STAMP_TZ TIMESTAMP(6) WITH TIME ZONE
            TIME_STAMP_LTZ TIMESTAMP(6) WITH LOCAL TIME
            ZONE

            SQL> select sessiontimezone from dual;

            SESSIONTIMEZONE
            —————————————————————————
            -04:00

            SQL> select time_stamp_ltz from date_table;

            TIME_STAMP_LTZ
            —————————————————————————
            17-MAR-17 05.42.30.596926 PM
            17-MAR-17 05.42.55.639511 PM

            SQL> alter session set time_zone=’-6:00′;

            Session altered.

            SQL> select time_stamp_ltz from date_table;

            TIME_STAMP_LTZ
            —————————————————————————
            17-MAR-17 03.42.30.596926 PM
            17-MAR-17 03.42.55.639511 PM

            SQL>

            Note how the returned timestamp values are now 2 hours earlier due to the change in the session time zone. This is what I expected.

            Now using SQLcl (latest version – again on Windows 7)

            VIENS @ patd >select sessiontimezone from dual;
            SESSIONTIMEZONE
            America/New_York

            VIENS @ patd >select time_stamp_ltz from date_table;
            TIME_STAMP_LTZ
            17-MAR-17 05.42.30.596926000 PM
            17-MAR-17 05.42.55.639511000 PM

            VIENS @ patd >alter session set time_zone=’-6:00′;

            Session altered.

            VIENS @ patd >select time_stamp_ltz from date_table;
            TIME_STAMP_LTZ
            17-MAR-17 05.42.30.596926000 PM
            17-MAR-17 05.42.55.639511000 PM

            VIENS @ patd >disc

            Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
            With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
            @ >connect [email protected]
            Password? (**********?) *********
            Connected.
            login.sql found in the CWD. DB access is restricted for login.sql.
            Adjust the SQL_PATH to include the path to enable full functionality.
            VIENS @ patd >alter session set time_zone=’-6:00′;

            Session altered.

            VIENS @ patd >select time_stamp_ltz from date_table;
            TIME_STAMP_LTZ
            17-MAR-17 03.42.30.596926000 PM
            17-MAR-17 03.42.55.639511000 PM

            VIENS @ patd >

            Note the sequence of events:

            Connect to the database.
            Query the table – get the expected results for the current session time zone.
            Change the session time zone.
            Repeat the query – note the results are the same as the first query when they should be 2 hours earlier.
            Disconnect from the session and log in again.
            Change the session time zone
            Repeat the query – this time the results are 2 hours earlier as expected.

        2. thatjeffsmith Post
          Author
          1. I’ve tried a number of experiments over the last couple of days (including accessing the test table via a database link) that has me convinced that this is a bug in SQLcl (which BTW also exists in a previous version – I tried). Given this I guess I need to create an SR with support.

            Thanks for listening.

            Norm

          2. thatjeffsmith Post
            Author
          3. thatjeffsmith Post
            Author
          4. If it really is JDBC, this is really disturbing!! This means that JDBC doesn’t support a datatype that has existed since (I believe) Oracle 9i!! I wonder what else isn’t working right.

            I believe I’m using a thick client:

            VIENS @ patd >show jdbc
            — Database Info —
            Database Product Name: Oracle
            Database Product Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
            With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
            Database Major Version: 12
            Database Minor Version: 1
            — Driver Info —
            Driver Name: Oracle JDBC driver
            Driver Version: 12.1.0.2.0
            Driver Major Version: 12
            Driver Minor Version: 1
            Driver URL: jdbc:oracle:oci8:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pimsdb2.ext.ray.com)(PORT =
            1521)) ) (CONNECT_DATA = (SERVICE_NAME = patd.pimsdb2.ext.ray.com) ) )
            Driver Location:
            resource: oracle/jdbc/OracleDriver.class
            jar: C:/Users/94884/Documents/instantclient_12_1/ojdbc7.jar
            JarSize: 3698892
            JarDate: Fri Aug 29 06:14:11 EDT 2014
            resourceSize: 2285
            resourceDate: Thu Aug 28 19:14:38 EDT 2014

            I did try this testing using a SQL Developer SQL Worksheet using both a thin and thick client connection – this WORKS in both cases. This really has me puzzled since from what I read in your blog SQL Developer and SQLcl use the same engine – why would one work correctly but the other not?

            Norm

          5. thatjeffsmith Post
            Author

            I didn’t research it deep enough to see if JDBC driver supports it or not – just found that link I shared.

            Depending on version of SQLcl and SQL Developer, you could be using a different JDBC driver.

            I would open a SR with MOS.

          6. Oh – another thing – I see the same problem running SQLcl on Linux (RHEL6) so it’s not a Windows specific issue.

            Norm

          7. Not sure it’s worth the effort to pursue this issue with support at this time (don’t get be started about the royal pain is has become to create tickets on MOS – I and my coworkers only create tickets when in dire need of support). Guess I’ll just stop using this tool since I don’t trust it and will advise my coworkers not to use this tool.

          8. thatjeffsmith Post
            Author

            I have the answer.

            It’s a SQLcl bug. We’re not catching that the timezone has been updated. I’m looking for a workaround for you now until we can patch SQLcl for you. I know if you change the TZ on your machine it will reflect that for your data…but I’m looking for a way to pass the TZ to the JVM at start-up time.

  145. Hi Jeff,
    I’d like to know what in background process when connection to oracle database with Kerberos authentication check box checked. What sqlnet parameters sqldeveloper use it…? No Oracle client involved setup. How it created TGT? It is looks to me it does not do anything in client, because I didn’t specify any krb5 configuration on client machine. database on 12.1 on windows 2012; running sqldevloper on windows 7, 2008r2, 2012. all successful connection. Tried use sqlnet trace, no details. I really need to know, I need the same way in my applications.
    Thanks Marina

    1. thatjeffsmith Post
      Author

      I’m not a kerberos guy…but if you didn’t provide any thin details, then it must be coming from sqlnet.ora – you don’t have anything on tools > database > advanced page configured?

    2. Hi Jeff,
      Thanks for quick response.
      tools > database > advanced — no values configured or sometimes I use tnsnames directory. Both ways it is working fine.

  146. Hi Sir,

    We are planning to use SQL Developer unit test utility for testing PL/SQL programs. We are able to test successfully procedure contains data type char,number and date with dynamically value passing. But I am not able to find a solution testing dynamically with PL/SQL record type input as well as passed. We are successful in doing the test with static values. Only problem with Dynamic Value Query.

    Can you please help me how to do this?

    Regards
    G. Srinivasa Rao

    1. thatjeffsmith Post
      Author
  147. Hi Jeff,

    I am struggling to find a way to change the cardinality in the model. By default I saw that is set as one to many and I want to change in 1 to 1.

    I found only a solution do add in the comment cardinality=1..1 but for me it doesn’t work. Version 4.1.5, build 907

    Thanks a lot,
    Vlad

    1. thatjeffsmith Post
      Author
  148. Hi Jeff,
    Please bear with my newbie question,
    I just installed sqlcl for mobaXterm on my win7 _x64 and it appears to not like queries being pasted with line-breaks. What am I doing wrong?
    I don’t have this issue with sqlplus and thank you in advance.

    example:
    [email protected]> select sysdate
    from dual;

    Error starting at line : 1 in command –
    select sysdate
    Error at Command Line : 1 Column : 15
    Error report –
    SQL Error: ORA-00923: FROM keyword not found where expected
    00923. 00000 – “FROM keyword not found where expected”
    *Cause:
    *Action:
    Elapsed: 00:00:00.228

    Error starting at line : 1 in command –
    from dual
    Error report –
    Unknown Command

  149. Hi, Jeff

    Is it possible to configure preferences so schema identifier is shown along with the name when i open procedure or package code.
    I know it is possible for tables. But for PL/SQL objects i did not find it.

    Thnks.

    Mike R.

    1. thatjeffsmith Post
      Author
  150. Modeler & Roles – Modeler has numerous database properties that can be added & edited. I added some roles, but don’t see a way to generate a script from Modeler?

    Thanks!

    1. thatjeffsmith Post
      Author
  151. I run into a somewhat unexpected behaviour with SQLcl when using “sqlformat delimited”. I’m trying to generate CSV output importable into Excel and I need to escape certain columns with =”xyz” instead of just plain “xzy” for that.

    set sqlformat delimited ; =” ”

    does the trick very well except for the first column of the result set.

    select to_char(1), to_char(2) from dual;

    returns

    =””1″;=”2″

    instead of

    =”1″;=”2″

    as I would have expected. Is there any reason why SQLcl does treat the first column differently here?

    1. thatjeffsmith Post
      Author
  152. It was great seeing you at the last UTOUG Fall Symposium! Hope the rest of your Spartan races for the year went well! Aroo!

    I know the functionality exists to export a single query result to a MS Excel worksheet (with option to name the worksheet) as well. I also understand the functionality exists to export data from multiple objects (tables/views/materialized views) data to separate worksheets in a single workbook file.

    But is there a way to export multiple custom query results to a single MS Excel workbook with each query result in it’s own worksheet?

    This would be very useful (extra credit for providing some metadata in the query comments to override/name the worksheet tabs).

    Thanks so much!
    Alex

    1. thatjeffsmith Post
      Author
  153. Been exploring sqlcl as and alternative to Sql*Plus for some of the users. I am having two issues. First is returning a resultset of about 2000 rows about 2 minutes in sqlcl and 5 second in sqldeveloper , using F5 run script.

    To test if changing sqlformat would change the speed I discovered an issue that really concerns me. When I use set sqlformat default I properly receive an error I knew is in the view. When I use set sqlformat ansiconsole I retrieve a portion of the rows but not error.

    I was testing various sqlformats and json fails to show error but csv does.

    Is this a bug or a feature?

    1. thatjeffsmith Post
      Author
      1. It could be any error in the select but in this case it is an ORA-01427, I can be reproduced with :
        SELECT o.*,
        ( SELECT created FROM all_objects A WHERE A.object_name = o.object_name ) new_date
        FROM all_objects o

        run as both default and ansiconsole. The failure is seen in default mode, but just a partial resultset is seen in ansiconsole. This fails because the same object name exist in two schema and at least one buffers worth of data is returned before the errant row.

  154. Hello Jeff,
    Before I ask my question, I should disclose I am completely new to Oracle SQL developer, (& to databases in general).

    Question: How do I export a query output to SPSS (Statistical Package for Social Sciences)?
    (I saw your /*csv*/ post, and ran it successfully, but have been unsuccessful when importing it into SPSS). My apologies if this is a remedial question…. but thank you in advance for the assistance.
    Kiki Martin

  155. Hi Jeff,

    In my table to insert Persian characters so when I want to retrieve data then got wrong characters. How to set character set or get correctly data? in other tools such as SQL Developer or Toad everything is right without any setting.

    1. thatjeffsmith Post
      Author
      1. I’ve checked in Linux although conversely displayed Persian character and next in windows XP/7/8/10 but I could not display correctly however I have followed these links:
        http://www.thatjeffsmith.com/archive/2016/08/unicode-and-oracle-sqlcl-on-windows/
        http://stackoverflow.com/questions/14109024/how-to-make-unicode-charset-in-cmd-exe-by-default
        https://ss64.com/nt/chcp.html
        http://www.wikihow.com/Customize-the-Font-in-Windows-Command-Prompt

        For check you can use:
        @https://gist.githubusercontent.com/Saeed-Hassanpour/8b299844aea7cfc27450a6af4bdc71ad/raw/5e39163bb2da9db0e91e2bae683bd9ce5e290486/sqlcl.sql
        Right result:
        Welcome to Parto Pardazesh Fartak(IRANAPEX)
        به شركت پرتو پردازش فرتاك (ايران اپكس) خوش آمديد

    1. thatjeffsmith Post
      Author
  156. Unit Testing:
    I’m wondering if there is reason for not allowing Variable Substitution in the teardown phase? I’d like to have that functionality. (I’m on 4.2 EA)

    1. thatjeffsmith Post
      Author
  157. Hi Jeff,
    I am trying to use the latest SQL Developer 4.2.0.16, but I am unable to launch it. I am getting complains about msvcr100.dll file. I have followed the instruction (found on the net) to edit sqldeveloper.conf file and comment out SetJavaHome. I never get prompted for Java Home and for whatever reason it’s looking under ~sqldeveloper\jdk\bin instead of ~sqldeveloper\jdk\jre\bin.

    Do you have any trick to make it work?

    Thanks

    1. thatjeffsmith Post
      Author

      yeah, it’s a bug with the EXE – you need to copy/create that directory manually and put the DLL in there

      it’s already been fixed for production release, sorry for the confusion/inconvenience

  158. Hi, Jeff.
    One more question.
    I want to use the ‘SQLDeveloper Navigate ShortKey’ for moves cursor on the SQL-Worksheet
    (go to marks, go to the last edit), but it works only in EditorWindow
    I often edit large scripts and ‘bookmarks’ can really help me in my work.
    Why does it not work?
    Maybe i misunderstand the idea?

    1. thatjeffsmith Post
      Author
      1. I also would like to use bookmarks in SQL Worksheet to navigate within a long script. Googling and Oracle documentation do not help me enable bookmarks. I can toggle the view for bookmarks, but cannot determine (or find) how to create a bookmark in my code.

        Help?
        john3

  159. Hi Jeff, firstly thank you for an awesome website!
    I’ve decided to try the query builder, and have run into a problem –
    I drag a couple of tables in, but when I right click on a table in Query Builder, the click doesn’t do anything – I don’t get any context menu with an option to uncheck all.
    For comparison’s sake, I can double click the table title to change the alias, or left click other areas to get a context menu no problem.
    So unless I want to use all the columns (not likely) it’s not of much use to me!
    Is there a preferance or setting I’m missing?
    Running vs 4.1.3.20
    Thank You!!

  160. Jeff,

    In SQL Developer a pop up box appears where values can be entered and bound to parameters. I want to pass a parameter that binds to a CSV, i.e. value1,value2 … etc., so that I can write my where clause as:

    WHERE app.attribute1 IN (:P_CSV)

    so that the parameter becomes

    WHERE app.attribute1 IN (‘value1′,’value2’)

    Is this possible?

    1. thatjeffsmith Post
      Author
  161. Hi, Jeff. Thank you for thatjeffsmith.com 🙂
    I am using Oracle SQL Developer in my daily work. Please tell me whether you can change the position of the “Query result area” between the right and the bottom of the SQL-worksheet or make a “float window”?

    1. thatjeffsmith Post
      Author

      Sorry Sergey, the results panel is locked to the bottom portion of the worksheet window. You can minimize it, and click/drag it up and down, but you can’t move it to the ‘right’ or undock/float it from the Worksheet itself.

        1. thatjeffsmith Post
          Author
  162. Trying very hard to get all developers onto one tool viz SQL Developer however we also want to start managing our database as code. I cannot find an equivalent to Devart’s db projects?
    We have 4.15 and 4.2 installed. Does this functionality exist?

    1. thatjeffsmith Post
      Author
  163. I have a SQL which I use within SQL*Plus

    select * from &owner..emp;

    Using the same SQL in SQLDeveloper, I replaced the “&” with “:”
    select * from :owner..emp;
    I tried various combinations and I can get it to work.
    For eg: select * from :owner.emp;
    select * from “:owner”.emp;
    select * from ‘:owner’.emp;

    Is it even possible?

  164. Hi Jeff,

    I have a question regards IOTs (Index Organized Tables) and the “Database Export Wizard”:

    My IOTs are not shown after lookup under “Specify Objects”. Just all “normal” heap organized tables.

    Exporting single SQL by selecting IOT in schema browser tree works fine. But I would like to do a FULL schema-ddl-export including IOTs with the export wizard.

    Anything I am missing?

    Version 4.2.0.16.356
    Build 16.356.1154

    All the best,
    Bernd

    1. Oh now I now what I am “missing” -> They will be created with the creation of the index-type CTXSYS.CONTEXT on the Base-Tables. Correct?

  165. Eye see you’ve reformatted your web page – and from the looks of your picture you haven’t reached the age yet where grey on white is hard to see. Here’s a wish we can get grey replaced with black for older eyes. The contrast really helps. Love your site and your sense of humor!

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
  166. Hi Jeff. I love the way you do search and replace in SQL Developer and wish every program worked the same way, entering the sought text into the replace box and highlighting it. That saves time! 🙂
    Keep up the good work.

    1. thatjeffsmith Post
      Author

      I love this too – so much that I about lost my mind when someone re-mapped Ctrl+R to ‘refresh’ instead of replace in 4.2. I logged a bug and that SHOULD be fixed for when we go prod.

  167. I use Code Blocks for C++ programming and there is great shortcut: “Copy line”.
    Without selecting a text just copy current line. In default shortcut is CTRL+Shift+T,
    but i changed it to CTRL+D, because I use it very often.

    I read your blog, looked into SQL Developer and googled a lot, but didn’t found that option.
    Could you tell me if there is a way to define such action and assign shortcut for it in SQL Developer?

    Note: using version 4.1.
    Maybe it is a good idea to introduce such feature in next release? 🙂

    1. thatjeffsmith Post
      Author
      1. Thank you for your answer – ‘select line’ shortcut is helpful.

        In fact that shortcut, which I described (and desire in SQL Developer) do a bit more: copy current line, create new line down there and paste this line.
        So 1 shortcut for 4 operations, nice deal.

        I will be happy to see that available, maybe in a future 🙂

        1. thatjeffsmith Post
          Author
          1. Thank you for that idea.
            I tested that and it works, it’s clever way to save some effort. But downside is that I can have only 1 macro, while more of them will be useful (for example as User-Defined shortcuts).

            Thank you!
            Regards,
            Adam

  168. Is there a way to force SQL Developer to load package body and specification from server upon open (double click or Edit)? Or is there a way to check for definition change on the server before save? We have run into situations where package has been update by somebody else, but other developer overwrites it.

    Thank you,
    Ilmars

    1. thatjeffsmith Post
      Author

      force both when you open just the one – no

      way to check if source has changed – it depends

      Are you using Source Control? Hopefully the answer is ‘Yes!’ – then say if you’re using Subversion, you should see incoming changes in your SVN client or in SQLDev if using it there, and then you can check-in/merge.

      1. Jeff, let me clarify the first question. Users A and B open up a connection, navigates to Packages. User A modifies package X – compiles. User B right away opens up package X by right clicking package Edit. User B does not see the changes made by user A. Most of the times, changes are reflected in a specification and not in a body. User B either needs to close the package and open it again with Edit or do a package refresh and then open with Edit.
        Does SQL developer caches any package definitions? Trying to understand if this is a database or SQL Developer issue. Using 11g XE, SQL Developer version 4.2.0.16.260.

        Thank you!

        1. thatjeffsmith Post
          Author

          View > Log > Statements.

          Open your package.

          You’ll see the queries go across the connection to get the code.

          Close the object.

          Open it again. You’ll see the queries run again.

          I don’t think we’re caching pl/sql source in the code editors, although we do cache OTHER things, like the DDL we generate on a table – I THINK.

  169. Good Day, Jeff
    A trivial question:
    I updated my SQL Developer from 4.1 to 4.2. I work with 2 separate connections and have the worksheets side-by-side vertically.
    With the previous version, i save my worksheets and close the Developer, then when i reopen it, the worksheets open in the vertical orientation.
    In 4.2 it is not the case. Am i missing a setting to always open in vertical view?
    Thank you in advance.

    1. thatjeffsmith Post
      Author
      1. I’m sorry, Jeff but I do not see where there is an option to attach a file and paste in the text window does not seem to be an option for this site.

        1. thatjeffsmith Post
          Author
          1. I think i may have found the resolution.
            I just tried this out:
            In the Preferences >> Database >> Worksheet
            Check the “Open a Worksheet on Connect”
            Now every time i open Developer, my connections tile vertically.

            Thank you for such quick replies, Jeff.
            You are amazing for having this discussion board that links the community to you!

          2. thatjeffsmith Post
            Author
  170. I have SQL Developer 4.1.3.20. When I run a script that I’ve stored under the User Defined Reports and spool the data to a csv file, the output has the query in the file. How do you omit the query from the file?

    1. thatjeffsmith Post
      Author
  171. Hello Jeff,
    I recently updated my Oracle to Version 4.1.3.20 and it lost all of my passwords for my Database connections along with all of my preferences.
    Is there an easy way to restore them?

    thank you

    1. thatjeffsmith Post
      Author