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,863

    1. thatjeffsmith Post
      Author
      1. But, If I try to execute the query, and close the sql developer, the query still works? or it’s killed. I need to execute the query in the server, not in the client…

        1. thatjeffsmith Post
          Author
  1. Hi Jeff,
    When upgrading from 17.2 to 17.4, do we need to download the version with the JDK or can we simply use the non JDK version since the JDK is already installed from a previous version install?
    Looks like the JDK version has sqldeveloper64W.exe and the non JDK has sqldeveloper.exe.
    Is there a difference. It looks like the non-64W version utilizes less memory.
    Thanks,
    David

    1. Both packages contain both versions of sqldeveloper. So my question should be, what’s the difference. We are using on Windows Server 2012.

      Thanks

    2. thatjeffsmith Post
      Author

      we don’t install any jdk. we EMBED a java home inside the zip…nothing else can use it but that copy of sqldev

      so if you only have java from the embedded jdk’s then you would ALWAYS have to grab the sqldev with the embedded jdk

      stay out of the bin directory, just run the EXE in the top level dir

  2. Hello, Im a beginner on SQL, self taught, and learning a lot from your page. That said, I need to load a lot of data from excel to my Oracle database. I read all your posts about the different methods BUT my problem is I am not able to preview the data as my excel file has 60,000 rows. I get the error message GC overhead limit exceeded. I do use xlsx format. What is the best approach? reduce the size of the file somehow?
    Thanks,

  3. Hi Jeff,

    it’s not possible to modify a tables partitions by clicking on edit table, after installing the latest version of the SQL Developer 17.4 . The entry “Partition” is not visible anymore in the menue. Is it a bug?

    Regards

    Ulli

    1. thatjeffsmith Post
      Author
      1. The previous version was 17.3.1.279. The current version is 17.4.0.355. There was an entry in the edit table menue on the left hand side, together with other entries like Columns, Constraints,…
        All other entries are still there in the new version, but no way to edit the partitions at all? We can only add or modify the partitions when creating the table.

        ——————————————————–
        — DDL for Table PARTITION_TEST
        ——————————————————–

        CREATE TABLE “ZPDB”.”PARTITION_TEST”
        ( “COLUMN1” NUMBER
        ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        STORAGE(
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “T_PUFFERDB”
        PARTITION BY RANGE (“COLUMN1”)
        (PARTITION “PARTITION1” VALUES LESS THAN (10) SEGMENT CREATION IMMEDIATE
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        NOCOMPRESS LOGGING
        STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “T_PUFFERDB” ,
        PARTITION “PARTITION2” VALUES LESS THAN (20) SEGMENT CREATION IMMEDIATE
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        NOCOMPRESS LOGGING
        STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “T_PUFFERDB” ,
        PARTITION “PARTITION3” VALUES LESS THAN (30) SEGMENT CREATION IMMEDIATE
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        NOCOMPRESS LOGGING
        STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “T_PUFFERDB” ) ;

        1. thatjeffsmith Post
          Author

          Yeah, your table works just fine for me. I’m on Oracle Database version 12.2.0.1.

          View > Log > Statements. Observe what the Edit dialog runs to build the screen. Take those queries, and run them in a SQL Worksheet. Any problems?

          1. We are using 11.2.0.4 with the latest patches.

            It seems to be a problem with the query to get the parameter “PARTITIONED”?!? There is a missing comma between “…EXTERNAL_TAB” and “‘N’ SHARDED…”!

            Please see the findings below:

            Error : “java.sql.SQLSyntaxErrorException: ORA-00923: Schlüsselwort FROM nicht an erwarteter Stelle gefunden
            ” building PARTITION_TEST, executing sql:
            SELECT /*OracleDictionaryQueries.ALL_TABLE_TYPE_QUERY*/
            A.TEMPORARY, A.DURATION, A.IOT_TYPE, A.PARTITIONED, A.NESTED,
            ( SELECT 1 FROM SYS.DBA_EXTERNAL_TABLES E WHERE E.OWNER = A.OWNER AND E.TABLE_NAME = A.TABLE_NAME ) EXTERNAL_TAB
            ‘N’ SHARDED, ‘N’ DUPLICATED
            FROM SYS.DBA_TABLES A
            WHERE A.OWNER = ? AND A.TABLE_NAME = ?

            This one works fine:

            SELECT /*OracleDictionaryQueries.ALL_TABLE_TYPE_QUERY*/
            A.TEMPORARY, A.DURATION, A.IOT_TYPE, A.PARTITIONED, A.NESTED,
            ( SELECT 1 FROM SYS.DBA_EXTERNAL_TABLES E WHERE E.OWNER = A.OWNER AND E.TABLE_NAME = A.TABLE_NAME ) EXTERNAL_TAB,
            ‘N’ SHARDED, ‘N’ DUPLICATED
            FROM SYS.DBA_TABLES A
            WHERE A.OWNER = ‘ZPDB’ AND A.TABLE_NAME = ‘PARTITION_TEST’;

            Is there any simple way to fix it?

          2. thatjeffsmith Post
            Author

            OK, so that query is wrong for a non-12c db. It’s already been logged as a bug on 17.4 and FIXED for 18.1 – which will be available later this quarter.

            In the meantime, best to work with v17.3 of the tool to get around this current limitation.

  4. In Sql 4.2.0.1 I can’t copy and paste into a script row. When I click on the row the first word in the row is highlighted. In older versions the first word wouldn’t highlightc rather, the cursor would click into a space in front of the first word and then I could add content as needed. Gosh. I

    1. thatjeffsmith Post
      Author

      In 17.4, I just ran via F5
      SELECT * FROM HR.EMPLOYEES

      In the script output, i triple-clicked to select the row.

      Ctrl+C

      And now I paste here…since you can’t see me paste into my worksheet.

                100 Steven        King          SKING      515.123.4567         17-JUN-87   AD_PRES         24000                                            90 
      
      1. I have the selected info that I want to paste but I can’t get the cursor to sit where I put it so I can copy and paste in front of it. I am adding to a row but when I click on the row the first word is highlighted and I can’t paste in front of the first word.

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  5. Hello experts,
    I cannot find out a difference between to kind of exporting data from Oracle SQL Developer.
    When I exported Query from Oracle SQL Developer manually and then import this output to MS Access, there is no problem here.

    I create BAT file with SQL PLUS commands , which execute this SELECT with this parameter :

    set termout off
    set tab on
    set colsep “;”
    set pagesize 0 embedded on
    set linesize 8000
    SET FEEDBACK OFF
    set underline off
    SET TRIMSPOOL ON
    spool C:\…..\MY_Output.txt
    SELECT
    ;
    spool off

    Then I import this output file to MS Access, system show me this message : The search key was not found in any record”
    I try to google it, but I do not find any relevant answer for my issue. Can you please help me ?

    thank you

    Tomas

    1. thatjeffsmith Post
      Author
      1. Yes, I try to import data to MS Access via CSV file or txt file. When I do it manually, everything works well, when I try to automate this proces by sql plus executed by BAT file, system show me this error message in MS Access, so I think it related with SQL PLUS command.

        1. thatjeffsmith Post
          Author
  6. Hello,

    I would like to ask how to generate e.g. table names in DDL script in lowercase/uppercase/ … ? I generate script from Data Modeler.

    An hour ago I had found such option somewhere and I changed it to lowercase and it works. There were also other options (uppercase/mixed/”keep as it is”, etc.), unfortunately I’m not able to find this setting again.

    Thanks in advance.

    Regards,
    PeterR

    1. thatjeffsmith Post
      Author
  7. Jeff,

    Any plans to allow the Query Results tab to act as a floating window? Sometimes I need to move the results to my large, portrait oriented monitor. I’d rather continue my workflow in SQL Dev than export to Excel.

    Connor

    1. thatjeffsmith Post
      Author

      No, it’s a customized editor type in the framework…which means it does what we need, but it also means it’s REALLY hard to let it do things like this, without breaking other stuff.

  8. I cannot see in the Navigator tree synonyms for other users.
    My account has grants to use the synonyms and related objects in the other schema, I can run SQL statements to use the data, and I can see the list of available synonyms when I query ALL_SYNONYMS .

    But when I expand the Navigator tree, go to “Other Users”, select the other schema, and select the node “Synonyms” the list is always empty. The node “Tables” under the other schema shows the available tables. I have filtered the tables (under the other schema) and enabled “Include synonyms” but it did not make any difference. It looks like the option to “Include synonyms” works only in the node “Tables” of the current account.

    Am I missing a setting? I see the small problem in two versions 4.1.5.21 and 17.3.1.279

    Thanks

    Witold

    1. thatjeffsmith Post
      Author

      If i go to Other Users, Synonyms tree, and expand – I see the synonyms for that schema.

      This is the SQL we run

       
      SELECT * FROM ( SELECT t.*, 
      substr(concat,1,instr(concat,'/')-1) TABLE_TYPE,
             substr(concat,instr(concat,'/')+1, LENGTH(concat)-instr(concat,'/')) TABLE_ID
      FROM (
       SELECT s.synonym_name, s.table_owner, s.table_name, 
        o1.object_id,
        o1.object_type || '/' || o1.object_id concat
       FROM Dba_synonyms s, sys.Dba_objects o1  
       WHERE s.owner = :SCHEMA
       AND s.owner = o1.owner 
       AND o1.object_type = 'SYNONYM' 
       AND s.synonym_name = o1.object_name 
      ) t )

      If you don’t have access to the DBA_ views, it’ll fall back onto ALL_ views.

      1. Thanks for the quick reply and SQL.

        I will have to work with DBAs to check permissions. I don’t have access to DBA_OBJECTS and ALL_OBJECTS does not show me the synonyms from the other schema. But ALL_SYNONYMS has the synonyms. It looks like something is filtered out in the query for ALL_OBJECTS

        Thanks
        Witold

    1. thatjeffsmith Post
      Author

      I don’t speak on behalf of Oracle.

      But.

      Is your machine secure? If it is, you’re fine. If it’s not and you think someone other than you can get access to your machine, then you shouldn’t save any passwords.

      Or let Google/Chrome save your passwords.

      1. When you say “someone other than you can get access to your machine” , do you mean with my Windows login and password?
        If they got the file that has the passwords, could they import it and use it?
        Are the passwords themselves encrypted in a manner that is very difficult to decrypt?

        1. thatjeffsmith Post
          Author
    2. thatjeffsmith Post
      Author

      If it weren’t considered safe by us, we wouldn’t offer the feature. You need to decide if the feature is safe for you.

      We do encrypt the passwords. You can’t simply copy the connections file off of someone’s machine and use it – we’ll strip out the passwords unless you use the connection export/import routine in the UI.

  9. Good afternoon from Australia

    Using:
    Java(TM) Platform 1.8.0_151
    Oracle IDE 17.4.0.355.2349

    Just updated to 17.4. All went smoothly. Connected to one of my 11GR2 databases and in the DBA section then chose Security/Users and get

    An error was encountered performing the requested operation:

    ORA-00904: “ALL_SHARD”: invalid identifier
    00904. 00000 – “%s: invalid identifier”
    *Cause:
    *Action:
    Vendor code 904

    Although I can continue every time I select the General tab of Users I get the same error.

    A bug or do I need to make a change elsewhere?

    Kind regards

    Steve

    1. Hi All

      Minor update: Installed this on Windows 10 and Widows 7 desktop machines with the same result.

      Regards

      Steve

      1. thatjeffsmith Post
        Author
    2. thatjeffsmith Post
      Author
  10. Hi Jeff,

    I am using Oracle SQL Developer 17.3.1.279 and would like to copy my query from the Developer to MS Word keeping the query ‘formatted’ when I paste it into Word. (keeping the colors/fonts). Unfortunately, when I paste the query into Word, it drops the formatting. I’ve searched the blogs for a resolution to this issue, but have been unable to find the answer.

    Can you tell me how to keep the SQL Developer format (font/colors) of the query when copying/pasting into another application such as MS Word?

    1. thatjeffsmith Post
      Author
  11. Hi Jeff,

    The enhancement request that I referred to in my first post was #45481 – the ability to catch PLSQL named exceptions in the unit tester.

    I hope this helps,
    Mark

  12. Hi Jeff,

    I am currently using Oracle SQL data modeller 17.3 and am trying to set up an Oracle 12cR2 physical model to support code based access control and invoker rights.

    I can create roles and am able to assign privileges to that role but I cannot find a way to assign the role directly to a procedure/function/package definition which is required for CBAC to work. Is there a way to do this in the tool or is it a feature request?

    Slightly off topic, is the Oracle SQL developer exchange still active? I posted some feature requests back in August that haven’t changed status (as far as I can tell anyways).

    Many thanks in advance and keep up the good work!!
    Mark.

      1. thatjeffsmith Post
        Author

        >>to assign the role directly to a procedure/function/package definition
        I read this and immediately thought, I don’t think you can assign a role to a PL/SQL object. I’m happy to admit I’m wrong though – can you show me a docs link describing what you’re looking for?

  13. Hi Jeff,

    I have an issue with the SQL DEVELOPER unit test repository. I am running a unit test against a function that returns REF CURSOR. I do not need to test the returned REF CURSOR data so have left the ‘Test Result’ box unchecked. This succeeds until I attempt to run a subsequent ‘Process Validation’ step, when I then get the error below. Please advise.

    Regards,
    Steve.

    The following procedure was run.

    Execution Call
    BEGIN
    :1 := “SETUP”.”RPT_GL_PKG”.”SPS_BALANCE_EXPORT”(P_SYSCM=>:2,
    P_GLP=>:3);
    END;

    Bind variables used
    :1 REF CURSOR OUT (null)
    :2 NUMBER IN 1
    :3 NUMBER IN 155

    Execution Results
    ERROR
    : Expected: [Any value because apply check was cleared], Received: [SYSCM GLP ACCOUNT_NUMBER SEGMENT0 SEGMENT1 SEGMENT2 SEGMENT3 SEGMENT4 SEGMENT5 SEGMENT6 SEGMENT7 SEGMENT8 SEGMENT9 ACCT_DESC ACC_TYPE TYPE_DESC PTD YTD PRIOR_PTD PRIOR_YTD
    —…
    ]
    Validation User PL/Sql Code failed: Unable to convert to REF CURSOR.

    1. thatjeffsmith Post
      Author
  14. Hi,

    Currently I’m using SQL Developer Version 4.2.0.16.260 Build 16.260.1303.
    I’ve got problem show CLOB data, not show full data.
    Its get cut.
    I’ve tried :
    set long 1000000000;
    set longchunksize 1000000000;
    Still, data get cut.

    1. thatjeffsmith Post
      Author
  15. SET Feedback OFF behaves differently than SQLplus
    SQLPlus:
    SQL> SET FEEDBACK OFF
    SQL> select * from ssss;
    select * from ssss
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    SQLcl
    SQL> SET FEEDBACK OFF
    SQL> select * from ssss;
    SQL> SET FEEDBACK ON

    SQL> select * from ssss;

    Error starting at line : 1 in command –
    select * from ssss
    Error at Command Line : 1 Column : 15
    Error report –
    SQL Error: ORA-00942: table or view does not exist
    00942. 00000 – “table or view does not exist”
    *Cause:
    *Action:

    1. Btw I am not sure if there is any official way to send feature suggestions. So I’ll just put them here.
      1. Enable the use of environment variables inside an script. This is one of the most common use case for any cmd utility. If anyone in 2017 wants to have interactive sessions they can just use SQLDeveloper or Toad.
      Example:
      #!/bin/bash

      db_schema=$1
      db_table=$2
      file_name=$3

      sql @select_all.sql
      ——
      spool ${file_name}
      select * from ${db_schema}.${db_table}
      spool off
      —–

      2. Separate the streams of information. We should be able to redirect the errors to &2 and normal debug output to the screen. I don’t see any benefit of having those in a spool file.
      We should have the option to get the spool results in &1 or a file.
      SET TERMOUT OFF doesn’t work when you want to use env variables in your script 🙁
      Example: This is considered and interactive session.
      sql user/[email protected] <<EOF

      select * from ${db_schema}.${db_table};
      EOF

      3. Now spool on the other hand side should contain the data that a command returns. (header+data)

    2. thatjeffsmith Post
      Author
  16. Is there an alternative way to change the theme in the latest versions since the theme feature has been removed? The whiteness is quite disturbing to the eyes.

    1. thatjeffsmith Post
      Author
  17. Hello,
    I used to use the Multi-Cursor Editing ( i think that is what it was called ) , used to call it column editing, anyways in version 17.3.1.279 it no longer works. I verified I have the Preferences-mouse actions set to control-shift.
    I must be missing something.
    Thank you this Blog, it is very helpful.
    Sam

    1. thatjeffsmith Post
      Author

      You’re going to need to be more specific.

      In 17.3, Windows, I just tried
      Ctrl+Shift+Click to add multiple cursors and edit => Works.
      Edit Menu, Block Selection…block select text => Works.

      What are you doing/trying?

  18. v17.x
    Open Monitor Sessions
    Top portion show all the sessions with Instance, SIDD and Serial
    The detail screen at the bottom run a query which seems to only join on SID so that the Active SQL shows multltiple sql one for each INSTANCE+SID+SERIAL. Is this a feature or a bug? Currently, I get to see an SQL that has nothing to do with my session (based on the OSUser and machine name)
    Regards

  19. Previous versions of SQL Developer had a feature that autogenerated GROUP BY clauses. I don’t see that available in v17. Do you know if this is still available and has just been moved from the Completion Insight tab in preferences?

    Thanks

    1. thatjeffsmith Post
      Author
  20. I am using Sql Developer Data Modeler, V. 17.2.0.188. I imported a schema from Erwin 7 and have successfully synced my model, which includes 40 Subviews with a “production like” schema. When trying to produce a report, via the Files –> Reports menu, I receive an error when for all options except XLSX. The error is:
    2017-11-29 09:35:26,981 [Thread-511] ERROR ReportsGenerator – TransformerException during report generationjavax.xml.transform.TransformerException: XML-22004: (Fatal Error) Error while parsing input XML document (Invalid char in text.).
    2017-11-30 13:30:34,980 [Thread-529] INFO RenderPDFReport – Using XSLT 1.0
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: Nov 30, 2017 1:30:36 PM org.apache.fop.cli.Main startFOP
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: SEVERE: Exception
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: org.apache.fop.apps.FOPException: An invalid XML character (Unicode: 0x12) was found in the element content of the document.
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: javax.xml.transform.TransformerException: An invalid XML character (Unicode: 0x12) was found in the element content of the document.
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.InputHandler.transformTo(InputHandler.java:217)
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.InputHandler.renderTo(InputHandler.java:125)
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.Main.startFOP(Main.java:166)
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.Main.main(Main.java:197)
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: Caused by: javax.xml.transform.TransformerException: An invalid XML character (Unicode: 0x12) was found in the element content of the document.
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.xalan.transformer.TransformerImpl.fatalError(TransformerImpl.java:780)
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:756)
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1284)
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1262)
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.InputHandler.transformTo(InputHandler.java:214)
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: … 3 more
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message:
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: ———
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message:
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: ; SystemID: file:/C:/Devon/DataModeler/Output/report_data.xml; Line#: 1527; Column#: 150
    2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: javax.xml.transform.TransformerException: An invalid XML character (Unicode: 0x12) was found in the element content of the document.

    Can you point me in the right direction please? I don’t know how to identify the character it doesn’t like. If I found the character, which is in the database schema, I don’t know what I would be able to do about it. Please advise. Thanks you.

  21. Is there a way to increase the size of the columns in the .csv file created from spooling?

    It appears that the columns are all being limited to 9 characters and the user has to expand the column in order to view the contents which is all there in Excel but partially being hidden.

    I have these settings and have played with them with no success:

    SET FEEDBACK OFF;
    set PAGESIZE 10000;
    SET COLSEP “,”
    SET HEADING OFF;
    SET NEWPAGE NONE;
    SET TRIMSPOOL OFF;
    SET LINESIZE 500;
    SET WRAP OFF;

    1. thatjeffsmith Post
      Author
  22. Jeff-

    Is there a way, in SQL Developer, to change the currently viewed database edition in the object navigation tree?

    I can see the editions in that tree, but I’m trying to understand how to “switch” between editions in the GUI and compare, for example, contents of a table across 2 editions?

  23. Jeff,
    Where, in the SQLDeveloper IDE, is the “Export” feature for SQLDeveloper connections?
    I’m using version 4.1.3.20, main build 20-78. I highlight the connection/connections that I’d like to export, and left-click, right-click, double-click, search the menu bar, but I don’t see the option to export the connection to xml.
    I admit, it might be right in front of me and I’m simply being dumb, but I’d very much like to save my team from having to re-invent flat wheels.

    1. thatjeffsmith Post
      Author
  24. Hello Jeff,

    I have a question on sdcli.exe using the cart function. I am planning to use it export the lookup data for a periodic backup.

    sdcli.exe cart export -cart C:\Users\u0051292\Cart_1.xml -cfg C:\Users\u0051292\export_tool.xml

    With above command i am able to make it work and i see data getting exported. But I what to schedule it on the server via a batch job.

    The problem i face is above statement asks for Password for the Connection at the time of execution. Is there a way to hard code the password so that it runs silent on a batch?

    I know there will be security concerns but i don’t care as this will be scheduled in a server which i only have access.

  25. Hello Jeff,

    I am currently doing a project in a company using Oracle.
    They want to implement their forecasts into Oracle.
    I have found that this is done by setting up forecast sets, which works just fine and it also shows the consumption when new sales orders come in.
    But i have seen that you can make the forecasts appear as “Forecasts MDS” when looking up the part in e.g. Supply/demand. And this could then generate a planned order for the forecast?

    Is it possible that you have any guidance or mby know a post explaining about how this could be done?

    1. thatjeffsmith Post
      Author
  26. Hi Jeff,

    I was trying to modify the package body which was left open from yesterday. I believe the session might have got disconnected and so i did a reconnect. However when i try to compile the package body, the sql developer just hangs. This has happened to me the second time. I copied the code, just in case and i am saved! as expected the sql developer hung. I’ve had to kill the sql developer session. Is this a known issue with a resolution?

    1. thatjeffsmith Post
      Author
  27. I’m using SQL Developer to query DB2. I have “auto-commit” turned off in the Tools->Preferences->Databases settings, but it doesn’t seem to have any affect. It does automatically commit. I ran a single UPDATE statement and then saw it had an error. I typed ROLLBACK but it was too late.

    Is there a way that I can actually make sure AUTO-COMMIT is off?

    1. thatjeffsmith Post
      Author
      1. Thanks for the reply. Unfortunately, this UPDATE did not have an error. It was syntactically correct. It was just missing some of the WHERE clause details to narrow down the affected data. When I ran it, it said I successfully updated X rows (and I was expecting 1). So, oops, I tried to do a ROLLBACK but it was already committed to the dB

  28. Jeff,
    I could create a process model without creating any object for Logical Model or Relational Model.
    But I am not able to generate documentation from that process model in SQL developer Data Modeler.

    -Kriti

  29. Hi Jeff,

    I am very new to SQL developer Data Modeler, My objective is to generate documentation for my existing database.
    Issue I am facing is that existing database doesnt have any key constraints defined in database.

    What do you suggest should I follow Top Down Approach for creating Design or Bottom Up Approach.

    Thanks
    Kriti

    1. thatjeffsmith Post
      Author
      1. Yes but not in all the tables and relations in other tables are usually not based on primary Key.

        Eg: Table 1: PK (Column1) , Column2 , Column3
        Table 2: Column2 , Column3

        So relation between the tables is based on Column2 and Column3 which dont have any constraint defined.

        1. thatjeffsmith Post
          Author
  30. 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.

      1. thatjeffsmith Post
        Author
        1. Do you have any plans to include git as versioning tool for oracle data modeler just like you did with oracle sql developer?

          1. thatjeffsmith Post
            Author
  31. 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?

    1. Yes, sir!

      In the “Connections” pane, right click on label, “Connections”. Observe the resulting drop down menu. Select “Export” and follow the prompts to export the connection(s) that you select. When the export is complete, ftp the resulting XML file to your target system. Use the same technique to “Import” the XML file. It works a treat!

  32. 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
      1. Hi Jeff,

        Ok, I have registered an SR for this, resulting in Bug 27143851. Hopefully the development team will look into this some time in the future – there is another enhancement request (Bug 26383429) that relates to handling large data models (4000+ tables in multiple designs) that I rather give a priority… (sorry to bring this up here but I am a bit frustrated as I am given no information – the bug says nothing as far as I can see..). My natural reaction to this is to ask if it is useful to the development team to have enhancement requests registered?

        Regards
        Jo

        1. thatjeffsmith Post
          Author
          1. Thanks Jeff for your investigation!

            I guess all I can hope for is that the Bug 26383429 will be considered some time in the future. Today I have a lot of manual work updating the Reporting repository and running out reports. Together with the lack of functionality for mass-updates, using SDDM for large data models (enterprise models – multiple designs) may not be the best option althought I really, really want it to be.

            Best regards
            Jo

          2. thatjeffsmith Post
            Author

            >> Together with the lack of functionality for mass-updates
            I’m not sure exactly what you’re doing, but both the transformation scripts and the Search feature both allow for updates to multiple properties on multiple design objects.

          3. Hi Jeff,

            There is a story behind our situation as we run multiple parallell projects (this is a data warehouse mirroring about 40 source systems – each with projects of their own) so to be able to handle this volume of project concurrency we have split our model into multiple designs covering different domains (I am sorry to say that we have about 50 designs with use of remote tables). So “mass-updates” is accross designs in our case. The multiuser functionality based on use of Subversion seemed to unstable to put this model into one or a few designs (our Citrix clients also ran out of memory – SDDM getting slow/unstable). So what I really want is a scalable repository to run api/sql against or as a second best option – scripting functionality – command line based – outside SDDM.

            Best regards
            Jo

          4. thatjeffsmith Post
            Author

            Ok. I hope you will understand that you are probably a 1%’er. This is a good and bad thing. Good that you are pushing the limits, and doing awesome things for clients. But bad because your needs exceed what most of the other users require.

            If you see something we can do better with the reporting repositories, that would probably be an easier change to make.

          5. Yes – I know our situation is beyond most use. One of our source systems use SDDM for their 1200 tables model in one design and that works fine.

            We also use the RR a lot for consistency checks accross designs. That works very nice! But it takes about one hour to export all designs manually. To have a scripting option would really help. Then I could schedule the export to ensure that the documentation we expose from RR is updated soon after the designs are checked in Subversion. Same issue relates to the Report functionality. Manual operations are bad even with only one design.

            Thanks
            Jo

  33. 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…

  34. 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.

  35. 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
  36. 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
  37. 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

  38. 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
  39. 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
  40. 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

  41. 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

  42. 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

  43. 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.

  44. 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
  45. 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
  46. 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
  47. 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
  48. 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.

  49. 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
  50. 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.

  51. 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
  52. 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

  53. 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.

  54. 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.

  55. 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
  56. 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!

  57. 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
  58. 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.

  59. 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)

  60. 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
  61. 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
  62. 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
  63. 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
  64. 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
  65. 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
  66. 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
  67. 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

  68. 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
  69. 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
  70. 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.

  71. 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
  72. 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

  73. 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
  74. 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
  75. 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
  76. 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
  77. 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
  78. 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
  79. 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.

  80. 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
  81. 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

  82. 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’

  83. 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

  84. 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

  85. 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
  86. 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
  87. 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
  88. 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?

  89. 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
  90. 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.

  91. 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
  92. 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.

  93. 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)….

  94. 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
  95. 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
  96. 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

  97. 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
  98. 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

  99. 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
  100. 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,”

  101. 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
        * /

  102. 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.

  103. 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

  104. 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,”

  105. 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
  106. 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
  107. 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
  108. 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!

  109. 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
  110. 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
  111. 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
  112. 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!

  113. 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
  114. 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
  115. 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
  116. 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.

  117. 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
  118. 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
  119. 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?

  120. 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!

  121. 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?

  122. 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.

  123. 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
  124. 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
  125. 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
  126. 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

  127. 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
  128. 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.

  129. 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

  130. 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

  131. 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.

  132. 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

  133. 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?

  134. 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
  135. 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?

  136. 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
  137. 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
  138. 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.

  139. 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

  140. 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

  141. 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.

  142. 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
  143. 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.

  144. 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.

  145. 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.

  146. 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
  147. 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
  148. 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.

  149. 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.

  150. 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
  151. 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

  152. 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?

  153. 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

  154. 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
  155. 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.

  156. 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.

  157. 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.

  158. 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!

  159. 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
  160. 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.

  161. 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
  162. 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
  163. 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
  164. 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.

  165. 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

  166. 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