Ask A Question

4.5+ million Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!

If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!

Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.

Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.

  1. I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
  2. I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
  3. I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.

Go!

Comments 4,099

  1. Not a question, but a comment. I had SQL*Developer in Windows 7 open with a lot of unsaved work. The UI stopped responding. I could move and resize the window. I could drag other windows across it. Each time, SQL*Developer updated its window without a flaw. However, I couldn’t click anything. Unresponsive. I right-clicked on the title bar and selected “close”. I was amazed to see SQL*Developer ask me if I wanted to save each item of unsaved work. Wow! When I reopened SQL*Developer, everything opened with no issues. Nice job!

  2. Hi Jeff,
    I was trying to send some data from inside my form to Excel and I tried what you said in the community forum:
    SET SQLFORMAT CSV
    SPOOL c:\users\user\data.csv
    SELECT * FROM EMPLOYEES;
    SPOOL OFF

    Problem is the command Set sqlformat csv gave me an error. I tried it in SQLPLus to see if it works there and it didn’t either. Would you please help me with this? Thank you.

    1. thatjeffsmith Post
      Author
  3. v18.1.0
    How do I get from Sessions SQL_ID to details of the Real Time SQL Monitor?
    The SQL_ID from Session does not show in the Real Time Window

    By the way, is there any way to increase the Font Size in the various dialog boxes? I am using the Oracle Look and Feel. Tried Windows and that is just ugly

    1. thatjeffsmith Post
      Author
  4. Hi Jeff,

    In the leftmost pane you can place a filter on a schema’s tables by right clicking on “Tables”.
    But in the filter expression, how do you protect wildcards?
    e.g., I want to search for the literal ‘_RR_’. How do I protect ‘_’ from being interpreted as a wild card?

    1. thatjeffsmith Post
      Author

      AFAIK, you can’t. I’ve tried and failed. I’m out on vacation for a week, so you might get a better answer if you post to our official forums.

      Workaround is to use the Schema Browser and the client-side filter, that should work just fine.

      If you open the View > Log > Statements panel, you’ll see how we’re constructing the SQL to filter the tree objects.

  5. v18.1.0
    I am trying to find dependencies on a particular package A
    On the Package Body — the dependency tab shows Package B
    The Find Database Objects does not show anything to do with Package B

    How do I find the dependency of A on B

    Code in A uses Views which call Functions in B — but should this not show someplace in Find Objects?

    1. thatjeffsmith Post
      Author
  6. Hi Jeff,

    When using SQL Developer 18c, I am performing a “create like” on a user. Everything seems to work perfectly with the exception that the “profile” of the user being copied is not put in the “create like” sql. Is there some type of environment setting that I don’t have enabled?

    Thanks,

    Dave

  7. I noticed very strange behavior

    When I change the ojdbc8.jar stored in the jdbc\lib folder, it does not pickup the modified or patched version, unless I edit the sqldeveloper.conf file. This makes me thinking is that it is somewhere caching this file and does not lookup the newer version

    SQL Developer version : 17.4.0
    OS : Windows 10 1607
    Java 8

    Is there a way to clear the cache or force SQL Developer to use updated jar files. I could not find any documentation.

    ojdbc8.jar has various bugs and I download the patches from Oracle and manually patch the ojdbc8.jar file. I want to make sure that SQL Developer picks up the new file.

    I use the below batch file to start my SQL Developer

    @echo off
    set IDE_USER_DIR=”C:\abc\%USERNAME%”
    start /d C:\abc\local\sqldeveloper\sqldeveloper\bin\ C:\abc\local\sqldeveloper\sqldeveloper.exe

    1. thatjeffsmith Post
      Author
      1. In that case SQL Developer will fail to connect to Oracle 11.2.0.4 database that uses the oid entry with TRANSPORT_CONNECT_TIMEOUT=3. As in 12c Oracle supports millisecond for this parameter, there is a known bug in the delivered ojdbc8.jar which reads this parameter as millisecond and fails to connect and gives the error :

        An error was encountered performing the requested operation:

        Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of server requested in connect descriptor

        Oracle support has provided a patch
        Patch 25977056: AC: UNITS FOR TRANSPORT_CONNECT_TIMEOUT CHANGED WITH NO QUALIFIER

        To fix this issue and below document specifies how to patch the standalone ojdbc8.jar

        How to Manually Apply a One-Off Patch to the JDBC Thin Driver or to the Universal Connection Pool When Oracle Client or Database Home Is Not Available To Apply the Patch via OPatch (Doc ID 431463.1)

        My Question is if we are not suppose to update the ojdbc8.jar (I thought we can use SQL Developer with appropriate user provided jdbc driver), How are we going to get fix for SQL Developer fails to connect to Database that uses oid entry with TRANSPORT_CONNECT_TIMEOUT=3 parameter.

        Please respond. I can open a support case with Oracle if needed for SQL Developer. I am surprised that we are not allowed to update the ojdbc8.jar with the oracle provided patches for a oracle supported product. Please clarify as jdbc patches are posted all the time.

      2. thatjeffsmith Post
        Author

        Those jar’s are provided for folks and their java applications.

        But SQL Developer is different. It’s our app, if you change it, you’re on your own, and you’re no longer supported.

        If you want us to pick up a new jar, you need to open a SR.

        We generally pick up the latest drivers/jars as each release comes about. It’s possible that’s already in there for 18.1 – available now on OTN. I’ll check with the devs and if not, will see about getting it included for 18.2.

      3. Thanks for the update. I understand it is not supported to update the ojdbc8.jar. However sometimes the delivered ojdbc8.jar breaks and Oracle support has fixes that we can manually apply. If you can explain how the caching of jar files work, it will be easier for us to use it till we get the official fix in newer versions.

        As I have said earlier, only workaround I found is to update the sqldeveloper.conf file and restarting the SQL Developer and it picks up the updated jar file. However not sure if this is the best way.

        I fully understand that using in this manner, it will be out of support from Oracle.

        I will test 18.1 and see if the fix is there or not. If not will wait for 18.2

      4. thatjeffsmith Post
        Author

        that patch fix was delivered for the 12.2.0.2 driver, which hasn’t officially been released yet. We’re running 12.2.0.1. When that comes out, or if it comes out as an 18 jar, we’ll pick it up.

        What you’re doing isn’t supported by us, but it would work.

  8. Hi Jeff,

    Could you please explain the difference between code generated when using:
    1. Synchronize with Data Dictionary
    2. DDL File Editor ?

    I understand that the first approach creates diff statements, but if we are creating new table shouldn’t these two generate the same output?

    Automatic indices and Table DDL transformation are missing although they have been set up.

    1. thatjeffsmith Post
      Author
      1. Another quick question regarding custum transformations.
        I know that it is possible to apply the transformation to the whole relational model by right clicking on the model, but is it possible to apply the transformation in similar manner to a subview or an object?

      2. thatjeffsmith Post
        Author
  9. When using SQL Developer to do a Database Export, a snippet of the resulting DDL is:

    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    BUFFER_POOL DEFAULT
    )
    NOPARALLELCREATE TABLE MRDP01_OWNER.ADDRESS_R
    (
    ADDRESS_ID NUMBER(6, 0) NOT NULL
    , REV_DT DATE DEFAULT SYSDATE NOT NULL
    , REV_BY VARCHAR2(8 BYTE) NOT NULL
    , ADDR_LINE1 VARCHAR2(100 BYTE) NOT NULL

    “NOPARALLELCREATE TABLE” is not right, it should be “NOPARALLEL CREATE TABLE” and fails when I try to import the exported ddl.

    What am I doing wrong?

    Thanks,
    Dan

    1. thatjeffsmith Post
      Author
      1. Jeff,

        Thanks for your quick reply. I am using SQL Developer 18.1.0.095.

        The DDL for 3 tables which have the bad syntax is 266 lines long. Might not be so good to paste here. Can I get it to you another way?

        Thanks,
        Dan

      2. thatjeffsmith Post
        Author
  10. Hi jeff,
    Greetings of the day!

    I want genarate excel files(.xlsx) from sql developer ,Here is the scenario.
    I had 10 sql queries .each produce one table.I want to put each qurey result in one work sheet and all work sheet’s in one excel file .
    Above 10 sql queries run multiple times and produce excel file same as above mentioned .
    Example: query1 –>worksheet1
    query2 –>worksheet2
    ………………………………………….
    ………………………………………….
    query10 –>worksheet10
    ________________
    excel file1
    like this way i want produce more excel files

    1. thatjeffsmith Post
      Author

      We can generate 10 spreadsheets for 10 queries. Or, you can create a VIEW for each query, and we can export the 10 VIEWs (which would be your query), to separate sheets in one Excel file.

  11. Hi Jeff,

    I’m able to use SQL Developer 4.1.5 (yes, we’re trying to get to 18.1 soon) to schedule a nightly RMAN backup in DBMS_SCHEDULER on a 12c database very quickly and easily. Is there a way to do the same thing on an 11g database using SQL Developer? Thanks,

    Mark

    1. thatjeffsmith Post
      Author
      1. I created a “Script” job type with a “Backup” script type with a “connect target /” followed immediately by an RMAN run block in each of my 12c databases. It runs like a champ every night straight out of DBMS_SCHEDULER. However, the Script job type doesn’t exist in 11g so, I’ve tried numerous times to create an “Executable” job type and associate a valid credential with it. But I always run into “ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted”.

        My executable (bash shell script on Linux with permissions wide open) exports the ORACLE_SID and ORACLE_HOME and then does:
        $ORACLE_HOME/bin/rman target / nocatalog << EOF
        run {
        backup database;
        }
        exit;
        EOF

        Is there a better way to do this against an 11g database? Thanks,

        Mark

      2. thatjeffsmith Post
        Author
      3. Yep, tried that very thing yesterday numerous times and it always looks like it’s going to work. However, the job goes into “RUNNING” status but never actually does anything, throws an error, or finishes.

      4. thatjeffsmith Post
        Author

        I know just enough to be dangerous with rman an data pump – i suggest you share your code with the AskTom folks or post a note to the plsql or scheduler forums.

      5. Sounds right. I was just wondering if you or any of your readers knew of any way to setup an 11g backup job through SQL Developer somewhat like a 12c db. It’s extremely slick on a 12c db using an RMAN Script in SQL Developer. So much so that we’ve moved all of our 12c nightly backups to DBMS_SCHEDULER jobs using SQL Developer. However, our 11g backups remain defined as OEMCC jobs until we can find a better method like SQL Developer has for 12c. Thanks,

        Mark

  12. Hi Jeff –

    Using sqlcl distributed with 12.2 (SQLcl : Release 12.2.0.1.0 RC). Linux x86.

    If I run sqlcl as the oracle user, root user, other users, everything seems to be fine.

    For some reason, my personal user, as soon as I touch any key after connecting and sitting at the SQL> prompt, I get:

    SEVERE: null
    java.lang.NullPointerException
    at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2725)
    at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2590)
    at oracle .dbtools.raptor.scriptrunner.cmdLine.SqlCli.startSQLPlus(SqlCli.java:861)
    at oracle .dbtools.raptor.scriptrunner.cmdLine.SqlCli.main(SqlCli.java:399)

    I can connect to the databases just fine, I get to the SQL> prompt, and get the exception as soon as I press any key.

    I’m using bash and have stripped my environment down to nothing but the oracle variables and JAVA_HOME, identical to other users that work, but can’t seem to fix or find the issue.

    Thanks.

  13. v18.1.0
    When looking at a table data or Sessions (Monitor Sessions)

    Is there anyway to set the Font of the headers. Currently the Line Number starts getting the …. when I reach to a 10000

    1. thatjeffsmith Post
      Author

      I can’t reproduce this for any report, the line numbers show ok for me, even to say 11,250…can you send me a screenshot? Post it to say imgur and send the link here as a comment.

      1. The font for the headers in v17.4 is fixed Courier size 10 while in 18.1 it seems to be Arial size 14 or 16

      2. thatjeffsmith Post
        Author
      3. Removed the old installation and re-installed and this time did not opy setting from v17.4. Seems to have solved the problem

  14. Is it possible in SQL Developer to run a query against an Oracle database connection and a Teradata database connection at the same time? From what I can tell you can only run a query against one database connection.

    1. thatjeffsmith Post
      Author
  15. Jeff,

    Just installed SQL Developer 18.1.0.095.

    In the Worksheet tab if I enter the command:

    set sqlformat

    and execute the result in the Script Output is:

    SQL Format Cleared

    In the Worksheet tab if i enter the command:

    set sqlformat csv

    execute the command (nothing in the Script Output) and then execute a SQL query results are not in CSV format. Works in sqlcl. Isn’t it supposed to work in SQL Developer?

    1. thatjeffsmith Post
      Author

      Absolutely that should work. Try an app restart?

      SQL> SET sqlformat csv
      SQL> SELECT * FROM employees;
      "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID"
      100,"50","King","SKING","515.123.4567",17-JUN-87,"AD_PRES",24000,,,90
      101,"Neena","Kochhar","NKOCHHAR","515.123.4568",21-SEP-89,"AD_VP",17000,,100,90
      102,"Lex","De Haan","LDEHAAN","515.123.4569",13-JAN-93,"AD_VP",17000,,100,90
      103,"Alexander","Hunold","AHUNOLD","590.423.4567",03-JAN-90,"IT_PROG",9000,,102,60
      104,"Bruce","Ernst","BERNST","590.423.4568",21-MAY-91,"IT_PROG",6000,,103,60
      105,"David","Austin","DAUSTIN","590.423.4569",25-JUN-97,"IT_PROG",4800,,103,60
      106,"Valli","Pataballa","VPATABAL","590.423.4560",05-FEB-98,"IT_PROG",4800,,103,60
      107,"Diana","Lorentz","DLORENTZ","590.423.5567",07-FEB-99,"IT_PROG",4200,,103,60
      108,"Nancy","Greenberg","NGREENBE","515.124.4569",17-AUG-94,"FI_MGR",12000,,101,100
      109,"Daniel","Faviet","DFAVIET","515.124.4169",16-AUG-94,"FI_ACCOUNT",9000,,108,100
      110,"John","Chen","JCHEN","515.124.4269",28-SEP-97,"FI_ACCOUNT",8200,,108,100
      111,"Ismael","Sciarra","ISCIARRA","515.124.4369",30-SEP-97,"FI_ACCOUNT",7700,,108,100
      112,"Jose Manuel","Urman","JMURMAN","515.124.4469",07-MAR-98,"FI_ACCOUNT",7800,,108,100
      113,"Luis","Popp","LPOPP","515.124.4567",07-DEC-99,"FI_ACCOUNT",6900,,108,100
      114,"Den","Raphaely","DRAPHEAL","515.127.4561",07-DEC-94,"PU_MAN",11000,,100,30
      115,"Alexander","Khoo","AKHOO","515.127.4562",18-MAY-95,"PU_CLERK",3100,,114,30
      116,"Shelli","Baida","SBAIDA","515.127.4563",24-DEC-97,"PU_CLERK",2900,,114,30
      117,"Sigal","Tobias","STOBIAS","515.127.4564",24-JUL-97,"PU_CLERK",2800,,114,30
      118,"Guy","Himuro","GHIMURO","515.127.4565",15-NOV-98,"PU_CLERK",2600,,114,30
      119,"Karen","Colmenares","KCOLMENA","515.127.4566",10-AUG-99,"PU_CLERK",2500,,114,30
      120,"Matthew","Weiss","MWEISS","650.123.1234",18-JUL-96,"ST_MAN",8000,,100,50
      121,"50","Fripp","AFRIPP","650.123.2234",10-APR-97,"ST_MAN",8200,,100,50
      122,"Payam","Kaufling","PKAUFLIN","650.123.3234",01-MAY-95,"ST_MAN",7900,,100,50
      123,"Shanta","Vollman","SVOLLMAN","650.123.4234",10-OCT-97,"ST_MAN",6500,,100,50
      124,"Kevin","Mourgos","KMOURGOS","650.123.5234",16-NOV-99,"ST_MAN",5800,,100,50
      125,"Julia","Nayer","JNAYER","650.124.1214",16-JUL-97,"ST_CLERK",3200,,120,50
      126,"Irene","Mikkilineni","IMIKKILI","650.124.1224",28-SEP-98,"ST_CLERK",2700,,120,50
      127,"James","Landry","JLANDRY","650.124.1334",14-JAN-99,"ST_CLERK",2400,,120,50
      128,"Steven","Markle","SMARKLE","650.124.1434",08-MAR-00,"ST_CLERK",2200,,120,50
      129,"Laura","Bissot","LBISSOT","650.124.5234",20-AUG-97,"ST_CLERK",3300,,121,50
      130,"Mozhe","Atkinson","MATKINSO","650.124.6234",30-OCT-97,"ST_CLERK",2800,,121,50
      131,"James","Marlow","JAMRLOW","650.124.7234",16-FEB-97,"ST_CLERK",2500,,121,50
      132,"TJ","Olson","TJOLSON","650.124.8234",10-APR-99,"ST_CLERK",2100,,121,50
      133,"Jason","Mallin","JMALLIN","650.127.1934",14-JUN-96,"ST_CLERK",3300,,122,50
      134,"Michael","Rogers","MROGERS","650.127.1834",26-AUG-98,"ST_CLERK",2900,,122,50
      135,"Ki","Gee","KGEE","650.127.1734",12-DEC-99,"ST_CLERK",2400,,122,50
      136,"Hazel","Philtanker","HPHILTAN","650.127.1634",06-FEB-00,"ST_CLERK",2200,,122,50
      137,"Renske","Ladwig","RLADWIG","650.121.1234",14-JUL-95,"ST_CLERK",3600,,123,50
      138,"Stephen","Stiles","SSTILES","650.121.2034",26-OCT-97,"ST_CLERK",3200,,123,50
      139,"John","Seo","JSEO","650.121.2019",12-FEB-98,"ST_CLERK",2700,,123,50
      140,"Joshua","Patel","JPATEL","650.121.1834",06-APR-98,"ST_CLERK",2500,,123,50
      141,"Trenna","Rajs","TRAJS","650.121.8009",17-OCT-95,"ST_CLERK",3500,,124,50
      142,"Curtis","Davies","CDAVIES","650.121.2994",29-JAN-97,"ST_CLERK",3100,,124,50
      143,"Randall","Matos","RMATOS","650.121.2874",15-MAR-98,"ST_CLERK",2600,,124,50
      144,"Peter","Vargas","PVARGAS","650.121.2004",09-JUL-98,"ST_CLERK",2500,,124,50
      145,"John","Russell","JRUSSEL","011.44.1344.429268",01-OCT-96,"SA_MAN",14000,0.4,100,80
      146,"Karen","Partners","KPARTNER","011.44.1344.467268",05-JAN-97,"SA_MAN",13500,0.3,100,80
      147,"Alberto","Errazuriz","AERRAZUR","011.44.1344.429278",10-MAR-97,"SA_MAN",12000,0.3,100,80
      148,"Gerald","Cambrault","GCAMBRAU","011.44.1344.619268",15-OCT-99,"SA_MAN",11000,0.3,100,80
      149,"Eleni","Zlotkey","EZLOTKEY","011.44.1344.429018",29-JAN-00,"SA_MAN",10500,0.2,100,80
      150,"Peter","Tucker","PTUCKER","011.44.1344.129268",30-JAN-97,"SA_REP",10000,0.3,145,80
      151,"David","Bernstein","DBERNSTE","011.44.1344.345268",24-MAR-97,"SA_REP",9500,0.25,145,80
      152,"Peter","Hall","PHALL","011.44.1344.478968",20-AUG-97,"SA_REP",9000,0.25,145,80
      153,"Christopher","Olsen","COLSEN","011.44.1344.498718",30-MAR-98,"SA_REP",8000,0.2,145,80
      154,"Nanette","Cambrault","NCAMBRAU","011.44.1344.987668",09-DEC-98,"SA_REP",7500,0.2,145,80
      155,"Oliver","Tuvault","OTUVAULT","011.44.1344.486508",23-NOV-99,"SA_REP",7000,0.15,145,80
      156,"Janette","King","JKING","011.44.1345.429268",30-JAN-96,"SA_REP",10000,0.35,146,80
      157,"Patrick","Sully","PSULLY","011.44.1345.929268",04-MAR-96,"SA_REP",9500,0.35,146,80
      158,"Allan","McEwen","AMCEWEN","011.44.1345.829268",01-AUG-96,"SA_REP",9000,0.35,146,80
      159,"Lindsey","Smith","LSMITH","011.44.1345.729268",10-MAR-97,"SA_REP",8500,0.4,146,80
      160,"Louise","Doran","LDORAN","011.44.1345.629268",15-DEC-97,"SA_REP",7500,0.3,146,80
      161,"Sarath","Sewall","SSEWALL","011.44.1345.529268",03-NOV-98,"SA_REP",7000,0.25,146,80
      162,"Clara","Vishney","CVISHNEY","011.44.1346.129268",11-NOV-97,"SA_REP",10500,0.25,147,80
      163,"Danielle","Greene","DGREENE","011.44.1346.229268",19-MAR-99,"SA_REP",9500,0.15,147,80
      164,"Mattea","Marvins","MMARVINS","011.44.1346.329268",24-JAN-00,"SA_REP",7200,0.1,147,80
      165,"David","Lee","DLEE","011.44.1346.529268",23-FEB-00,"SA_REP",6800,0.1,147,80
      166,"Sundar","Ande","SANDE","011.44.1346.629268",24-MAR-00,"SA_REP",6400,0.1,147,80
      167,"Amit","Banda","ABANDA","011.44.1346.729268",21-APR-00,"SA_REP",6200,0.1,147,80
      168,"Lisa","Ozer","LOZER","011.44.1343.929268",11-MAR-97,"SA_REP",11500,0.25,148,80
      169,"Harrison","Bloom","HBLOOM","011.44.1343.829268",23-MAR-98,"SA_REP",10000,0.2,148,80
      170,"Tayler","Fox","TFOX","011.44.1343.729268",24-JAN-98,"SA_REP",9600,0.2,148,80
      171,"William","Smith","WSMITH","011.44.1343.629268",23-FEB-99,"SA_REP",7400,0.15,148,80
      172,"Elizabeth","Bates","EBATES","011.44.1343.529268",24-MAR-99,"SA_REP",7300,0.15,148,80
      173,"Sundita","Kumar","SKUMAR","011.44.1343.329268",21-APR-00,"SA_REP",6100,0.1,148,80
      174,"Ellen","Abel","EABEL","011.44.1644.429267",11-MAY-96,"SA_REP",11000,0.3,149,80
      175,"Alyssa","Hutton","AHUTTON","011.44.1644.429266",19-MAR-97,"SA_REP",8800,0.25,149,80
      176,"Jonathon","Taylor","JTAYLOR","011.44.1644.429265",24-MAR-98,"SA_REP",8600,0.2,149,80
      177,"Jack","Livingston","JLIVINGS","011.44.1644.429264",23-APR-98,"SA_REP",8400,0.2,149,80
      178,"Kimberely","Grant","KGRANT","011.44.1644.429263",24-MAY-99,"SA_REP",7000,0.15,149,
      179,"Charles","Johnson","CJOHNSON","011.44.1644.429262",04-JAN-00,"SA_REP",6200,0.1,149,80
      180,"Winston","Taylor","WTAYLOR","650.507.9876",24-JAN-98,"SH_CLERK",3200,,120,50
      181,"Jean","Fleaur","JFLEAUR","650.507.9877",23-FEB-98,"SH_CLERK",3100,,120,50
      182,"Martha","Sullivan","MSULLIVA","650.507.9878",21-JUN-99,"SH_CLERK",2500,,120,50
      183,"Girard","Geoni","GGEONI","650.507.9879",03-FEB-00,"SH_CLERK",2800,,120,50
      184,"Nandita","Sarchand","NSARCHAN","650.509.1876",27-JAN-96,"SH_CLERK",4200,,121,50
      185,"Alexis","Bull","ABULL","650.509.2876",20-FEB-97,"SH_CLERK",4100,,121,50
      186,"Julia","Dellinger","JDELLING","650.509.3876",24-JUN-98,"SH_CLERK",3400,,121,50
      187,"Anthony","Cabrio","ACABRIO","650.509.4876",07-FEB-99,"SH_CLERK",3000,,121,50
      188,"Kelly","Chung","KCHUNG","650.505.1876",14-JUN-97,"SH_CLERK",3800,,122,50
      189,"Jennifer","Dilly","JDILLY","650.505.2876",13-AUG-97,"SH_CLERK",3600,,122,50
      190,"Timothy","Gates","TGATES","650.505.3876",11-JUL-98,"SH_CLERK",2900,,122,50
      191,"Randall","Perkins","RPERKINS","650.505.4876",19-DEC-99,"SH_CLERK",2500,,122,50
      192,"Sarah","Bell","SBELL","650.501.1876",04-FEB-96,"SH_CLERK",4000,,123,50
      193,"Britney","Everett","BEVERETT","650.501.2876",03-MAR-97,"SH_CLERK",3900,,123,50
      194,"Samuel","McCain","SMCCAIN","650.501.3876",01-JUL-98,"SH_CLERK",3200,,123,50
      195,"Vance","Jones","VJONES","650.501.4876",17-MAR-99,"SH_CLERK",2800,,123,50
      196,"Alana","Walsh","AWALSH","650.507.9811",24-APR-98,"SH_CLERK",3100,,124,50
      197,"Kevin","Feeney","KFEENEY","650.507.9822",23-MAY-98,"SH_CLERK",3000,,124,50
      198,"Donald","OConnell","DOCONNEL","650.507.9833",21-JUN-99,"SH_CLERK",2600,,124,50
      199,"Douglas","Grant","DGRANT","650.507.9844",13-JAN-00,"SH_CLERK",2600,,124,50
      200,"Jennifer","Whalen","JWHALEN","515.123.4444",17-SEP-87,"AD_ASST",4000,,101,10
      201,"Michael","Hartstein","MHARTSTE","515.123.5555",17-FEB-96,"MK_MAN",13000,,100,20
      202,"Pat","Fay","PFAY","603.123.6666",17-AUG-97,"MK_REP",6000,,201,20
      203,"Susan","Mavris","SMAVRIS","515.123.7777",07-JUN-94,"HR_REP",6500,,101,40
      204,"Hermann","Baer","HBAER","515.123.8888",07-JUN-94,"PR_REP",10000,,101,70
      205,"Shelley","Higgins","SHIGGINS","515.123.8080",07-JUN-94,"AC_MGR",12000,,101,110
      206,"William","Gietz","WGIETZ","515.123.8181",07-JUN-94,"AC_ACCOUNT",8300,,205,110
       
      107 ROWS selected.
  16. Hello,
    I just upgraded to 18.1 and find that the auto-format to UPPER Case for Keywords is no longer working. Is this a bug that might be fixed, or is there a setting beyond Tools –> Preferences –> Code Editor –> Format: Keywords Case: UPPER ?

    Thank you for your feedback!

    1. thatjeffsmith Post
      Author

      It was removed as the formatter already covers this use case. You’ll just use it on demand via Ctrl+F7. Make sure you set the case preferences appropriately first, of course.

  17. Hi Jeff,

    Is there a setting to make the highlighted occurrences stay highlighted even though one clicks with the mouse in the SQL editing window? Or even if one starts typing? I am asking this because sometimes it is extremely useful to have a term highlighted while coding.

    Thank you in advance and congratulations for a great product!
    Peter
    — Athens, Greece

    1. thatjeffsmith Post
      Author

      Thanks Peter!

      Not super obvious, but once you have your code highlighted, hit the tab button to get your cursor back to the editor, then you can type. Your highlights will remain. If you mouse-click, they’ll go away though.

      1. No, thank you! I would not have found this in a million years. Hope you fix this soon though!

        Thanks again for the work-arround!

        Peter
        — Athens, Greece

  18. Hi, Jeff.

    Is there a way in sql developer that will automatically convert to UPPERCASE the data during import of CSV files?

    1. thatjeffsmith Post
      Author

      No…but several thoughts:

      • why not use a text editor and uppercase the data in the csv before the import
      • you could import the data and then convert it in the db via SQL
      • you could use an INSERT trigger and upper() the data as it’s inserted – probably slow/expensive
  19. v18.1.0
    I run a script which calls other scripts and the output goes to a spool file. The script stops running part of the way.

    In 17.4 the prior, the script completes and I also get a complete spool file.

    1. thatjeffsmith Post
      Author
  20. Hi Jeff
    We are using ORDS 17.4 [without APEX , without PLSQL Gateway].

    is ther any way to get requested URL (or my template path or my template ID) and remote IP from my GET handler (source type : query or collection) ?

    regrading to owa_util.get_cgi_env it is working only source type ‘plsql’.

    Thanks

  21. Hi,

    I’m probably overlooking something obvious to do with formatting SQL. I can get SQL Developer to format it nicely using Ctrl-F7, but is there a way to format it as a single line, e.g. going from

    SELECT
    wm_concat(column_name)
    FROM
    all_tab_columns
    WHERE
    table_name = ‘&TABLENAME’
    GROUP BY
    table_name;

    to

    SELECT wm_concat(column_name) FROM all_tab_columns WHERE table_name = ‘&TABLENAME’ GROUP BY table_name;

    I currently do it by copying it into Notepad 2 and using its Align Lines command, but was wondering if there was something in SQL Developer.

    Thanks

    David

    1. thatjeffsmith Post
      Author
  22. What are the benefits of using Oracle Instant Client with SQL*Developer? The SQL Developer 17.4 User Guide references the ‘User Oracle Client’ to configure SQL*Developer to use it, but not why one would want to.

    1. thatjeffsmith Post
      Author
  23. What is the most recent version of Sql Developer? I have 4.0.3.16, and I see references to 4.2 as of Sept 2017. But what is this new numbering system 17.4, 18.1? Has the numbering system changed, or this is a different ‘flavour’ of Sql Developer?

    1. thatjeffsmith Post
      Author
  24. Hi Jeff,

    I gather that there’s been a delay of some sort on 18.1. Will it possibly be out this week? Thanks,

    Mark

    1. thatjeffsmith Post
      Author
  25. Can we change the definition of a word in SQL Dev, so that Control+right arrow skips over underscores? It’s really a pain to stop at each underscore and makes navigation slower.

    1. thatjeffsmith Post
      Author
  26. Hi Jeff,

    A SDDM question (v174):

    Are there restrictions when importing a ddl file for updating comments (on RDBMS) on view columns from a ddl file into sddm?

    Got a ddl file with two “comment on” statements – one for the view and one for a view column. Only the view comment is updated in the model. This is not expected – bug?

    Importing a complete ddl file with the “create view” statement into a new model will also set the comment on the view column. So this works fine.

    Thanks
    Jo

    1. thatjeffsmith Post
      Author
  27. Hi Jeff,

    I would like to export all DDL (Tables, Packages,Triggers etc) using SQL Developer (17.4.1) from Tools > Database Export menu.

    I would like to export all DDL from a few schema (not my schema)

    Is there the way that SQL Developer NOT also export my schema objects.

    Thanks,
    Binh Le – Greensboro NC

    1. thatjeffsmith Post
      Author

      It by default will pick all objects of the said type you have checked on the wizard for the logon schema.

      Easiest thing to do would be to logon as one of those 2 schemas vs using a 3rd schema.

      If that’s not possible, make sure to pick at least one object of each type in schema 1 or 2 on the object picker.

      If you don’t pick any views, but you have VIEWS enabled, it will export every view from the logon schema.

  28. Jeff,

    Was trying to navigate the Connections tree via keyboard per your ‘SQL Developer Trick: DropDown Vs Tree (Feb.27, 2012) and ran into an issue. The version of SQL Developer is 17.3.1.279 (Build 279.0537).

    Cursor was on the database instance and pressed ‘O’. Cursor move to ‘Operators’ (yes, some of these could be hidden, but aren’t). Pressed ‘O’ again expecting the cursor to move to ‘OLAP Options’. Didn’t move anyplace. Pressed the ‘Right Arrow’ to expand ‘Operator’. Since there was nothing under it just the little ‘+’ sign disappeared. Pressed ‘O’ again and cursor moved to ‘OLAP Option’. Press ‘O’ again and cursor moved to ‘Other Users’. So why was the ‘Right Arrow’ required prior to the ‘O’ to go from ‘Operators’ to ‘OLAP Option’?

    1. Jeff,

      Were you able to determine if this is an error? I believe it should have gone from ‘Operators’ to ‘OLAP Options’ without requiring the ‘Right Arrow’ in between.

  29. Hi Jeff,

    Is there any way to import old SQL Developer settings into a new instance after the installation has been completed?

    I installed a new version of SQL Developer over my old (‘saved’ in the trash) and during the installation process was asked by the installer if I would like to import my settings. Unfortunately other issues I was having with my desktop forced me to cancel that installation (strange problem unrelated problem with Windows updates where UI didn’t recognize my clicks). I deleted the installation and tried again after rebooting, hoping to see the import settings option again but was disappointed when I didn’t.

    Justin

    1. thatjeffsmith Post
      Author
  30. Hello,

    Is it possible to open from SQL Developer (Files view) a Windows Explorer directly on that folder? Like we have in IntelliJ menu Show in Explorer?

    Thank you,

    1. thatjeffsmith Post
      Author
  31. Hi,

    Is it expected for SQLD 17.4.1 to run the “IndexPreferencesTask” in the lower right corner on every startup?

    Thanks, Tim…

    1. thatjeffsmith Post
      Author
  32. HI
    I’ve been using SQL dev. for many years but still i havn’t find out in what way the “connection selector” dropdown list in the upper right corner of the SQL Work is sorted, is it sorted at all?
    Thanks
    Gunnar

    1. thatjeffsmith Post
      Author
  33. We have a huge database with data of different categories of people and many procedures that manipulate the data. We need to provide an api for a mobile application- and other applications in the future- to retrieve data and execute procedures. We need to be able to validate that the request is allowed to be executed on the data it’s manipulating. We have our SSO system on which we’re going to rely for authentication.

    1. thatjeffsmith Post
      Author

      >> We need to be able to validate that the request is allowed to be executed on the data it’s manipulating
      The right place to do that would be in your PL/SQL code. Either in the POST handler code, or in the stored procedure it ultimately executes.

      1. We thought it might be easier and more performant if it was to be called by ORDS automatically before each request.

        One more question, what is the best way to return the response if the request passes the validation with minimal code?

    1. thatjeffsmith Post
      Author

      security.requestValidationFunction is there to make sure the RIGHT people are allowed to use the pl/sql gateway to execute stored procedures.

      What are you trying to do with ORDS? Can you not just secure the resource? ORDS would then require authorization to be in place for every call to it.

  34. Hi Jeff,

    We’re using ORDS 17.4 without APEX. We need to do some validations for every request. How can we do that.
    security.requestValidationFunction doesn’t seem to work. If it not available without APEX, are there any alternatives?

    Your help would be much appreciated.

    1. thatjeffsmith Post
      Author
  35. Jeff,

    I’ve never used Toad. But a couple users who have used Toad, but have lost that access, claim that they could create a ‘multi-database connection’ in Toad and write a query that selected data from multiple instances. Can SQL Developer do the same thing?

    1. thatjeffsmith Post
      Author

      From one of the toads, Toad for Data Analysis or some thing, whatever they’re calling it now…

      To do this in SQL Developer, one would use DB_LINKs.

      We don’t offer client-side joining of data across jdbc connections.

  36. Do you have any suggested processes, strategies or best practices for management of custom domains and types in SQL Developer Data Modeler that will be shared between several modelers?

    1. thatjeffsmith Post
      Author

      Heli suggests keeping them in Source Control as well as your designs themselves.

      She’s written an entire book on how best to use the Modeler, feel free to drop her a comment on further elaboration.

  37. Hi Jeff,

    I know SQL Developer 17.4.1 released on 3/7. Will there still be an 18.1 released later this month? Thanks,

    Mark

    1. thatjeffsmith Post
      Author
      1. Thanks Jeff, but are there still issues with ‘Check for Updates’?

        I’m on 17.3.1.279, I see that 17.4.1.054.0712 was released on 7/3/18, and yet when I do a check for updates it doesn’t show me any available updates?

        I’m just checking the ‘Oracle SQL Developer’ Center at ‘http://apex.oracle.com/pls/apex/dbtools/usage/cfu’ that it is defaulted to.

        I’ve tried with and without proxy, as std user and admin.

        As an admin, it started up and told me there were updates, but now that icon (bottom right hand corner of screen) has gone away, and ‘check for updates’ is not returning anything.

      2. Right, I’ve just found another post where you say the check for updates is for extensions, not the product – may be worth adding ‘for extensions’ to the menu option as it’s obviously confusing a few people?

  38. Hi, Jeff,

    This version 17.4.0.3555 has a bug.

    When you right-click on an external table “edit”, the pop-up shows incorrect “table type”, it shows “normal”, all previous versions show “External”, the problem is: user cannot edit, because “external table properties” is not available.

    Please take a look.

    Thanks,

    Dave

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

    Thank You for all your blogs they are really helpful. I have small question I would like to know how to check if my database is properly normalized currently at present we have lot of performance issues. I think it is because of poor database design how to verify the database design if it is tunned for optimal performance.

    1. thatjeffsmith Post
      Author

      There’s no ‘is my model, right?’ test you can have the tool apply to your database. But there are rules, or best practices. But you need to look at if your db is OLTP, a DW, or mixed-use. Also, why do you think the perf is bad b/c of the design? If you have evidence showing that, the lack of a foreign key or a denormalized table would be easy to spot.

  40. Thought you’d like this slightly amusing bug. Downloaded the latest sqlcl, as you can see. But…

    C:\sqlcl\bin>sql

    SQLcl: Release 17.4.0 Production on Thu Mar 08 09:15:34 2018
    Copyright (c) 1982, 2018, Oracle. All rights reserved.

    New version: 4.1.0 available to download

    Username? (”?)

    1. thatjeffsmith Post
      Author
  41. I’ve created a logical model & engineered it to a relational model (using standalone Oracle Datamodeler, not as part of Sqldeveloper; version is 4.0.3.853). My destination db is Postgresql. How do I set up Datamodeler to generate Postgres-syntax scripts?

    1. thatjeffsmith Post
      Author
  42. How to enable TNS encryption and data Integrity in SQL-Developer connection. I did the required changes in sqlnet.ora on client and server but the sessions to database using SQL-Developer are not showing as encrypted one while SQL* Plus and TOAD one are shown in v$session_connect_info. Are there any special settings required?

    1. thatjeffsmith Post
      Author

      SQL Dev is not using sqlnet.ora – we’re jdbc. So you can setup SSL for JDBC or you can tell SQL Developer to use a THICK connection to go via OCI, which will then pick up your sqlnet.ora settings.

      1. Thanks it works using THICK connection to go via OCI. Do you have steps to setup SSL for JDBC?

  43. Hi Jeff, does SQL Developer needs user admin rights to function properly under windows 10?
    At some point in time program started reporting “…Could not load connection type:Oracle null ”
    (connection preferences, export sql etc…).
    This was 4.1.3 but tested with 17.2 and 17.4. versions with same results
    All above versions work when running as user with admin rights.
    thanks

    1. thatjeffsmith Post
      Author
      1. thanks for answering, the problem was solved by not importing the settings from the previous installation (running as admin was working because importing was not done as well, it looks like admin privs had nothing to do with the problem but something with the application user settings…
        it works now 🙂
        thank you again

      2. thatjeffsmith Post
        Author
      3. Hi Jeff, I am still having the issue in my sql developer showing error while launching “http://apex.oracle.com/pls/apex/dbtools/usage/cfu”.
        Since I have configured it for PosgreSql as well some how I am able to run queries but when I wanted to export output the Export Wizard is failing with error “java.lang.NullPointerException”
        Would you please help me here to get this corrected.

      4. thatjeffsmith Post
        Author
  44. Here is my insert querry:
    insert into nametable
    (id, last_name, first_name)
    values
    (12345, ‘LeBlanc’, ‘Sarah’);

    select * from nametable where id = 12345;
    Here is the return:
    12345, Leblanc, Sarah

    What should I do so that the field displays what I inserted. I want to see
    12345, LeBlanc, Sarah

    Advice, please. Thanks.
    Xiaoling

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
      1. Here are the last name from a source table:
        LeBlanc
        McFarland
        de Kok

        After inserting query, the last name from the designation table become:
        Leblanc
        Mcfarland
        De Kok

        I wish I could paste my picture here, but this form does accept my pictures. I think it must have something to do with the designation table (setting? trigger? etc. )
        What do you think?

      2. thatjeffsmith Post
        Author
      3. There are several triggers attached to the designation table, but nothing looks like formatting name (such as first letter has to be an upper case and the rest lower case).
        For instance, the following trigger is the only trigger which has “last_name” involved.
        create or replace TRIGGER “SATURN”.”ST_SPRIDEN_NAME_COMPRESS”
        BEFORE INSERT OR UPDATE OF SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME, SPRIDEN_MI
        ON SPRIDEN
        FOR EACH ROW

        BEGIN
        :new.spriden_search_last_name :=
        gukcmpr.f_compress_name(:new.spriden_last_name);
        :new.spriden_search_first_name :=
        gukcmpr.f_compress_name(:new.spriden_first_name);
        :new.spriden_search_mi :=
        gukcmpr.f_compress_name(:new.spriden_mi);
        :new.spriden_soundex_last_name := soundex(:new.spriden_last_name);
        :new.spriden_soundex_first_name := soundex(:new.spriden_first_name);
        END;

        Do you see anything I missed here?
        Thanks.

      4. thatjeffsmith Post
        Author
  45. Hi,

    We have downloaded the following versions of SQLDeveloper and SQLcl
    – sqldeveloper-17.4.0.355.2349-x64.zip
    – sqlcl-17.4.0.354.2224-no-jre.zip

    We have a secured environment and need to supply a PGP Signature or SHA-1 hash or MD5 hash for any files to be imported into the environment. Do you know where I can find one of these signatures for the above files?

    1. thatjeffsmith Post
      Author

Leave a Reply

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