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

  1. 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
  2. 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.

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

  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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

  12. 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
  13. 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
  14. 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.

  15. 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
  16. 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

  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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.

  24. 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
  25. 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

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

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

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

  29. 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
  30. 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
  31. 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
  32. 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?

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

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

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

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

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

  43. 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
  44. 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,”

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

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

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

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

  49. 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
  50. 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
  51. 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
  52. 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!

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

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

  61. 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
  62. 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
  63. 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?

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

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

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

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

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

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

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

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

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

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

  78. 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
  79. 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?

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

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

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

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

  86. 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
  87. 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  111. Jeff,

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

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

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

    Connor

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

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

          2. thatjeffsmith Post
            Author

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

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

    1. thatjeffsmith Post
      Author
  113. Hi Jeff,

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

    thank you

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

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

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

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

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

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

    1. thatjeffsmith Post
      Author

      Are you writing to XLSX?

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

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

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

        1. thatjeffsmith Post
          Author

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

          Have you tried increasing the JVM max heap size?

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

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

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

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

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

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

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

            thank you for your assistance as always!

          2. thatjeffsmith Post
            Author

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

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

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

            I talk about this in detail here.

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

  115. Hi Jeff,

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

  116. Hi Jeff,

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

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

    Am I overlooking something silly?

    Thanks

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

        1. thatjeffsmith Post
          Author
  117. Hi Jeff,

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

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

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

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

    Am I missing something?

    Thanks.
    Norm

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

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

        1. thatjeffsmith Post
          Author

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

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

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

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

            SQL> select sessiontimezone from dual;

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

            SQL> select time_stamp_ltz from date_table;

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

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

            Session altered.

            SQL> select time_stamp_ltz from date_table;

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

            SQL>

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

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

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

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

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

            Session altered.

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

            VIENS @ patd >disc

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

            Session altered.

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

            VIENS @ patd >

            Note the sequence of events:

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

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

            Thanks for listening.

            Norm

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

            I believe I’m using a thick client:

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

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

            Norm

          5. thatjeffsmith Post
            Author

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

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

            I would open a SR with MOS.

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

            Norm

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

          8. thatjeffsmith Post
            Author

            I have the answer.

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

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

    1. thatjeffsmith Post
      Author

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

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

  119. Hi Sir,

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

    Can you please help me how to do this?

    Regards
    G. Srinivasa Rao

    1. thatjeffsmith Post
      Author
  120. Hi Jeff,

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

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

    Thanks a lot,
    Vlad

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

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

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

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

  122. Hi, Jeff

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

    Thnks.

    Mike R.

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

    Thanks!

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

    set sqlformat delimited ; =” ”

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

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

    returns

    =””1″;=”2″

    instead of

    =”1″;=”2″

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

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

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

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

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

    Thanks so much!
    Alex

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

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

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

    Is this a bug or a feature?

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

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

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

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

  128. Hi Jeff,

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

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

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

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

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

    Do you have any trick to make it work?

    Thanks

    1. thatjeffsmith Post
      Author

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

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

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

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

        Help?
        john3

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

  133. Jeff,

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

    WHERE app.attribute1 IN (:P_CSV)

    so that the parameter becomes

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

    Is this possible?

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

    1. thatjeffsmith Post
      Author

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

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

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

    select * from &owner..emp;

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

    Is it even possible?

  137. Hi Jeff,

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

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

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

    Anything I am missing?

    Version 4.2.0.16.356
    Build 16.356.1154

    All the best,
    Bernd

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

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

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

    1. thatjeffsmith Post
      Author

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

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

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

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

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

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

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

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

            Thank you!
            Regards,
            Adam

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

    Thank you,
    Ilmars

    1. thatjeffsmith Post
      Author

      force both when you open just the one – no

      way to check if source has changed – it depends

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

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

        Thank you!

        1. thatjeffsmith Post
          Author

          View > Log > Statements.

          Open your package.

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

          Close the object.

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

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

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

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

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

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

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

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

    thank you

    1. thatjeffsmith Post
      Author
      1. thanks, Jeff, i actually see you have an official post about this too. I’m sorry i asked it again. I will pre-empt keeping my passwords for the future.

  145. Hi Jeff,

    I am using SQL Developer 4.1.5 on Linux. When working with files (View ==> Files), is there a way for hidden files and folders to remain hidden in the SQL Developer File navigation tree? I have many hidden files and folders and they are cluttering up the navigation tree and making it hard to find the files I really want to work on.

  146. I am trying to determine a way to modify SQL Developer to read a PKI CAC and use that information (with the prompt for the PIN) for access to an Oracle Database. I know that the docs talk about Strong Authentication and storing certs on the server, but that literally disconnects the cert from the process, and what I am talking about is Multi-factor authentication. Oracle provides tools to do this for Web based applications, but it seems that MF Authentication is missing in connection to the database. Do you have any suggestions on how SQL Developer could be used to do this?

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  147. Hi Jeff, I recently upgraded to Windows 10 and downloaded the latest version of SQL Developer(4.1.5). When I launch SQL Developer it is automatically closing at different times. It does not stay open for longer than about 5 minutes and sometimes closes down in seconds. I attempted to launch from the command line and collect the error but it did not provide any information. Can you provide some additional information on what I can try to narrow down the issue?
    Thanks,
    Jon

    1. thatjeffsmith Post
      Author

      open a cmd window, go to the sql developer BIN folder. start the EXE from there

      see when it closes, if it prints any info there.

      this most closely sounds like an issue where the video driver crashes the JVM – you might just want to update your driver.

  148. In Verdion 4.2 of SQLDeveloper, there is a new behaviour in code editor. Just one click is needed to mark a word, which leads to unwanted results:
    i.e. when You mark long sections (click at the begin, scroll down hold shift and then click at the end), or when you just want to position the cursor at the end of a word (here the word is marked and you have to click a second time).
    Is there a preference to bring back the old editor-behaviour?

    1. thatjeffsmith Post
      Author
  149. Jeff, Can we join query results and a table to get new results ? for example if i ran one sql and got query result1 now i need to run another sql by using query result1 and table B…is this possible in Sql developer ?

    1. thatjeffsmith Post
      Author

      No, you’d what to join your original query to your 2nd query.

      SO if query one was
      select * from hr.employees

      And your 2nd query was
      select * from hr.departments

      Your 3rd query COULD be
      select * from hr.departments a, hr.employees b
      where b.department_id = a.department_id

      1. Thanks Jeff, In hyperion we can use results and join with a table that why i asked but any way , it could be the new feature in your new version release.
        Thanks
        Deepak

  150. This is the closest thing I’ve found to a SQL Developer community, so I thought I’d ask here. I’m trying to get SQL Developer 4.1.3.20 connected to SQL Server 10.50.2500.

    I’ve tried both the jtds-1.2 and 1.3 drivers. Due to network and SQL Server configurations outside of my control, I know from connecting other tools (e.g. Oracle Data Integrator) that I have to start any Java-based processes with the -Djsse.enableCBCProtection=false option. I’ve put this in my sqldeveloper.conf file and I see that option reflected in the command that launches SQL Developer.

    When I configure the properties in the “SQL Server” tab for a connection and test, I don’t see a database in the dropdown tab and when I test the connection anyway I receive a “DB host closed connection” message. This is the message that I would receive in other tools or test programs when the enableCBCProtection option doesn’t make it to the JVM launch.

    Is there something I’m missing here?

    Thanks.

    1. thatjeffsmith Post
      Author
      1. Thanks for the link.

        I checked my SQL Developer installation (on Linux) and don’t see a product.conf file. In any case, I verified in Help/About/Properties that the option is being used so I think the issue might be something else.

        1. thatjeffsmith Post
          Author

          ahhh, sorry. on *NIX, that would be in your $HOME/.sqldeveloper folder.

          you might also be able to edit the connections.xml file and edit the connection entry and insert a connection property to the JBCC URL

  151. Is there any way to quickly jump to a particular column in SQL Developer? I’d like to “Select *” to view the entire row as it is, but scroll over to say column 43 (Brand_Type_Code) to see what the data looks like there. I don’t believe I’ve run across this request before, but it seems like it would be a handy feature.

    Thank you!

    1. thatjeffsmith Post
      Author
  152. To Whom it may Concern :

    Having issues with SqlDeveloper keeps crashing after a few minutes with me loggin in. It just pretty much disappears. Was able to copy debugging info.

    Also include the following information:
    • The version of SQL Developer you are running
    • The version of the JDK you are using
    • The OS you are using
    • The version of Oracle you are connected to
    OS : Windows Server 2008
    Java Version 1.8.91
    Version Sql Developer : 4.1.3.20….. But really I’ve tried several different versions and I get the same issue.

    Oracle version :

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
    PL/SQL Release 11.2.0.3.0 – Production
    CORE 11.2.0.3.0 Production
    TNS for 64-bit Windows: Version 11.2.0.3.0 – Production
    NLSRTL Version 11.2.0.3.0 – Production

    Below is the debugging info I was able to get. Thanks in advance for your awesome work with sqldeveloper….Unfortunately I couldn’t paste everything because of the character limit..

    LOAD TIME : 407UsersCache.fillIn() time = 93 ret==null?: true
    2016-12-01 14:09:03
    Full thread dump Java HotSpot(TM) 64-Bit Server VM (25.91-b15 mixed mode):

    “Background Parser” #63 prio=6 os_prio=0 tid=0x000000001c72d800 nid=0x1354 waiting on condition [0x000000002282f000]
    java.lang.Thread.State: TIMED_WAITING (sleeping)
    at java.lang.Thread.sleep(Native Method)
    at oracle.dbtools.raptor.plsql.BackgroundParser$1.construct(BackgroundParser.java:126)
    at oracle.dbtools.raptor.utils.NamedSwingWorker$2.run(NamedSwingWorker.java:115)
    at java.lang.Thread.run(Thread.java:745)

    “TextBufferScavenger” #61 prio=6 os_prio=0 tid=0x000000001c72c000 nid=0xb10 in Object.wait() [0x000000001b81f000]
    java.lang.Thread.State: WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:143)
    – locked (a java.lang.ref.ReferenceQueue$Lock)
    at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:164)
    at oracle.ide.model.FacadeBufferReference$PollingThread.run(FacadeBufferReference.java:145)

    “oracle.jdbc.driver.BlockSource.ThreadedCachingBlockSource.BlockReleaser” #58 daemon prio=4 os_prio=-1 tid=0x0000000016908800 nid=0x1b78 in Object.wait() [0x000000001ecdf000]
    java.lang.Thread.State: TIMED_WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at oracle.jdbc.driver.BlockSource$ThreadedCachingBlockSource$BlockReleaser.run(BlockSource.java:327)
    – locked (a oracle.jdbc.driver.BlockSource$ThreadedCachingBlockSource$BlockReleaser)

    “Timer-2” #55 prio=6 os_prio=0 tid=0x000000001f01e800 nid=0xfcc in Object.wait() [0x000000002410f000]
    java.lang.Thread.State: WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at java.lang.Object.wait(Object.java:502)
    at java.util.TimerThread.mainLoop(Timer.java:526)
    – locked (a java.util.TaskQueue)
    at java.util.TimerThread.run(Timer.java:505)

    “status-0” #51 prio=2 os_prio=-2 tid=0x000000001f01c000 nid=0x1ae0 waiting on condition [0x0000000023c0f000]
    java.lang.Thread.State: WAITING (parking)
    at sun.misc.Unsafe.park(Native Method)
    – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
    at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
    at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
    at java.util.concurrent.DelayQueue.take(DelayQueue.java:211)
    at oracle.ide.status.StatusExecutor$StatusQueue.take(StatusExecutor.java:338)
    at oracle.ide.status.StatusExecutor$StatusQueue.take(StatusExecutor.java:300)
    at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1067)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1127)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

    “IconOverlayTracker Timer: null-jdbcNodeInfoType” #46 prio=5 os_prio=0 tid=0x000000001f018800 nid=0x1b6c in Object.wait() [0x0000000023b0f000]
    java.lang.Thread.State: WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at java.lang.Object.wait(Object.java:502)
    at java.util.TimerThread.mainLoop(Timer.java:526)
    – locked (a java.util.TaskQueue)
    at java.util.TimerThread.run(Timer.java:505)

    “Timer-0” #45 prio=6 os_prio=0 tid=0x000000001f01a800 nid=0xc18 in Object.wait() [0x000000002325f000]
    java.lang.Thread.State: WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at java.lang.Object.wait(Object.java:502)
    at java.util.TimerThread.mainLoop(Timer.java:526)
    – locked (a java.util.TaskQueue)
    at java.util.TimerThread.run(Timer.java:505)

    “WaitCursor-Timer” #44 prio=6 os_prio=0 tid=0x000000001f01a000 nid=0x1350 in Object.wait() [0x000000002315f000]
    java.lang.Thread.State: WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at java.lang.Object.wait(Object.java:502)
    at java.util.TimerThread.mainLoop(Timer.java:526)
    – locked (a java.util.TaskQueue)
    at java.util.TimerThread.run(Timer.java:505)

    “WeakDataReference polling” #43 prio=1 os_prio=-2 tid=0x000000001f019000 nid=0x1338 in Object.wait() [0x0000000022f2f000]
    java.lang.Thread.State: WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:143)
    – locked (a java.lang.ref.ReferenceQueue$Lock)
    at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:164)
    at oracle.ide.util.WeakDataReference$Cleaner.run(WeakDataReference.java:88)
    at java.lang.Thread.run(Thread.java:745)

    “RaptorTask: Usage Data” #40 prio=5 os_prio=0 tid=0x0000000016908000 nid=0xe9c waiting on condition [0x000000002272f000]
    java.lang.Thread.State: WAITING (parking)
    at sun.misc.Unsafe.park(Native Method)
    – parking to wait for (a java.util.concurrent.SynchronousQueue$TransferStack)
    at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
    at java.util.concurrent.SynchronousQueue$TransferStack.awaitFulfill(SynchronousQueue.java:458)
    at java.util.concurrent.SynchronousQueue$TransferStack.transfer(SynchronousQueue.java:362)
    at java.util.concurrent.SynchronousQueue.take(SynchronousQueue.java:924)
    at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1067)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1127)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

    “Thread-9” #39 daemon prio=5 os_prio=0 tid=0x0000000016907000 nid=0x1448 in Object.wait() [0x000000002242f000]
    java.lang.Thread.State: WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at java.lang.Object.wait(Object.java:502)
    at java.util.prefs.AbstractPreferences$EventDispatchThread.run(AbstractPreferences.java:1482)
    – locked (a java.util.LinkedList)

  153. Jeff, Wondering if it is possible to execute unittest via sdcli64.exe for a PostGres target e.g. our unit test repository is in Oracle (fine) and works for Oracle target -db (also good) and our Postgres JDBC driver to allows a SQL*developer connection to PostGres ( good/fine/great) BUT : we cannot run a unit test that is testing a PostGres database , from the command line or anywhere else. Works fine for Oracle -db but bot Postgres. We get this sort of error via sdcli64.exe from command line :

    “Could not connect to database postgres on localhost. The error encountered was: The connection subtype SDPostgreSQL is not recognized.”

    Will the “SDPostgresSQL” connection subtype be supported ever for sdcli64.exe ?? Is there a way for us to extend this ourselves via configuration ?

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

    I’ve started using the SQL Developer Data Modeler tool (4.2.0) and have created a logical model which I want to use with the business users. But some of the attributes have names longer than 30 chars which is a problem when I go to generate the DDL from the relational model. I’m wondering if there is a way (similar to the display entity synonym) to allow long names in the logical model but then have columns names for the relational and physical models.

    Also can you recommend any blogs etc (apart from your own obviously!) for new users please.

    Many Thanks
    N.

    1. hi, found it so please disregard the question.

      Can be done via the attributes properties.
      Need to go to Logical Model/Entities – expand the entity to show the attributes and then double click the attribute to show the properties.

      Regards,
      N.

      1. thatjeffsmith Post
        Author
  155. I am trying to use ‘Oracle SQL Developer’ migration assistant but it errors out without any messages been inserted into ‘MIGRLOG’ table. I have posted the question on stackoverflow where you replied asking for error details which I added but that question was put on hold by some users hence posting it here if you could advise.
    Link : http://dba.stackexchange.com/questions/160560/sybase-to-oracle-db-migration

    **What has been tried so far:**
    Tried using Oracle SQL Developer’s “Migration Assistant” without any luck. It shows errors, not successful.

    *Specific questions*:

    1. What tools can be used for such migration which converts Sybase objects to Oracle to some extent? Even if we could get partially converted scripts, it can be reviewed and modified manually?
    2. Can ‘Sql developer’ be also used or provide any data reconcillation utilities after the migration.?

    *SQL Developer Migration assistant errors*
    When I run ‘oracle sql developer’ migration assistant, I get error as:

    > “Capture failed. Refer to MIGRLOG table in the repository for details”

    When I see ‘MIGRLOG’ table, there are no messages at all.

    It creates `MigrationLog.xml` file which has below error (same as above). This is just snippet as other info in the xml shows just some class file names. Hence I am unable to research further.
    **ERROR**

    oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle
    SEVERE
    oracle.dbtools.migration.workbench.core.logging.MigrationLogUtil
    Capture
    Enterprise Capture
    Capture failed. Refer to MIGRLOG table in the repository for details
    oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doOnlineEnterpriseCapture(FullMigrateTask.java:758)
    oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doCapture(FullMigrateTask.java:601)
    oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doMaskBasedActions(FullMigrateTask.java:400)
    oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doWork(FullMigrateTask.java:314)
    oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doWork(FullMigrateTask.java:147)
    oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
    java.util.concurrent.FutureTask.run(FutureTask.java:262)
    oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:554)
    java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    java.util.concurrent.FutureTask.run(FutureTask.java:262)
    java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    java.lang.Thread.run(Thread.java:745)
    [email protected]

    oracle.dbtools.migration.workbench.core.ui.FullMigrateTask$FullMigrateTaskException: Capture
    Enterprise Capture
    Capture failed. Refer to MIGRLOG table in the repository for details

    1. thatjeffsmith Post
      Author

      what version of SQL Developer?
      what version of Sybase ASE?

      We have a forum setup JUST for migrations. Suggest you post this there, with the details I asked for.

      Also make sure your db user has the privs needed to get all the information out of the database data dictionary.

      1. Ok will post it there too.
        The info just for reference is as below :
        SQL Developer version : Version 4.0.3.16
        Syabase ASE version : 15.7
        Oracle version : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  156. When I connect SQL Developer to a SQL Server database and run a query, it appears that in the results I cannot double click on the header to sort or click on the filter icon to filter. Are these features disabled when working with SQL Server data?

    1. thatjeffsmith Post
      Author

      Maybe/probably. Many/most of the features in SQL Developer are written specifically for an Oracle (or TimesTen) database.

      The connectivity for SQL Server allows you to do many things, but it’s there primarily to help you migrate it and its data to an Oracle Database.

  157. Hi Jeff,

    Happy New Year and thank you for maintaining this site. I started using SQL Developer on Mac OSX a week ago and find your site very useful. I was a TOAD user on Windows for a long time but find the Mac version of TOAD to be sadly lacking in features. I have mostly been able to find suitable replacements for features and shortcuts that I used in TOAD in my current version of SQL Developer for Mac. But I am facing a peculiar problem. In TOAD for windows, I am able to popup describe packages in the production instance where I have read only access by just typing the package name and hitting F4 (for popup describe). In SQL Developer, however, even though I am able to view the package code using popup describe in the development instance where I have read write access, I get the “No Object Found:” error message when I try to popup describe the same object in the Production instance. Is there a setting that I need to change in SQL Developer that will allow me to popup describe objects in read only databases? This also seems to be the case with custom tables where I have read only access in Production, but I am able to see the table details if I precede it with the schema name. This approach doesn’t seem to work for packages though since the popup describe shows the details of schema user name, user ID and Creation Date instead.

    1. thatjeffsmith Post
      Author

      can you give me a scenario…such as

      login as user x with y privs to z objects
      open worksheet
      type ….
      try shift+F4
      observe code fired as shown in View > Log > Statments

      that might be enough for me to see if there’s a problem, or you might see for youself.

      You can also try to ctrl+click vs DESC

      1. I am not sure I follow your question completely. In our dev environment, I log in as ‘apps’ user with read and write access. I type in the name of a custom package and use F4 to popup describe the package contents so that I can view the code within the package. In the production environment, I log in with my individual user login to which an IS_USER role has been granted. I believe the access is read only/select for this user role. When I try to use F4 to popup describe, SQL Developer on my mac, it shows me a message that says “No Object Found: “. When I use TOAD on windows, I am able to popup describe the custom package in production. I did notice that SQL Developer automatically opens the popup describe of a package in a new worksheet in editable mode in the development instance whereas TOAD always opens it in read only mode. Could this be causing the issue?
        Note: I have mapped the F4 function key as a shortcut for Popup Describe. ctrl+click does not really do anything other than opening the context menu at the cursor from which I can again choose Popup Describe if needed, but this gives the same result. I can see an option to choose View > Log, but there is no submenu under log to choose ‘Statements’. When choosing Log, no additional information screen pops up. The logging page at the bottom of the screen does not have any additional messages either.

        Thanks for the help

      2. I tried using SQL Developer on Windows and was able to extract the log Statements from there. Same issue on Windows for Dev vs. Prod. I have the log details in a spreadsheet. Is there a way for me to share the spreadsheet with you?

        1. thatjeffsmith Post
          Author

          look at the statements yourself – you should see where we are querying the data dictionary, looking for the object you want to do an DESC on.

          run those queries for yourself in a sqlworksheet – is the object you’re trying to DESC coming back from the query? If not, there’s an issue with your security/priv levels in the data dictionary. Or maybe you found a bug.

  158. I have been having this issue with my SQL Developer for several months now. I will open the software and try to connection and it will freeze. I then have to shut the program enough times or shut of computer five plus times. The last time this happened I noticed a error message in the logging page. message “Error in RequestProcessor oracle.ideimpl.usages.UsagesTrackerImpl$3”. This error does not show when you open the program it only showed up when I happen to open the second session of it. I was not in the process of running anything. This is when I am opening the software first thing when I get to work and it starts that freezing issue.
    Thanks

    1. thatjeffsmith Post
      Author

      try unchecking
      preferences > usage reporting > allow automated usage reporting to Oracle

      Next time the application freezes, you can use JSTACK program in your JDK/bin folder to grab a stack dump. This can be read to find what the UI blocking process/operation is.

  159. Why does the latest Oracle 12c (Windows 64-bit) include SQL Developer version 3? Why not a newer version?
    This makes life very confusing since version 3 does not have the data modeler built in (and some other features like SQL Server connectivity that are discussed widely elsewhere).

    1. thatjeffsmith Post
      Author

      Please define, ‘latest.’

      But basically, we get to contribute the latest and greatest version of sql developer available when a major new revision of the database is released. when 12c came out, v3.2 was the latest and greatest.

      Most of our users don’t run sqldev out of an $ORACLE_HOME, they get a newer version from OTN

      Also, SQL Server connectivity is provided by a JDBC driver that you can download from sourceforge – we don’t ship those 3rd party drivers for licensing and other reasons

  160. Happy New Year and thanks for this tremendous service. The CIO is asking me “is there are any java license issues because we use SQLdeveloper”. He’s normally a stay out of the way kind of guy, so someone must be asking him for a response. Is there a tool to check for java lcense compliance? Thanks

    1. thatjeffsmith Post
      Author
  161. I have reverse engineered a database that has common columns in the tables: CreatedBy, CreatedDate. I want to add a RDBMS Comment to those columns without having to hit each table.

    One of the things I did in the past when using Oracle Designer is bulk updates to the metadata repository. In ERwin, this can be done by importing the definitions from a spreadsheet using a macro. ER Studio will interpret these as domains and you can update the domain and the columns will inherit the definition.

    I searched to see if anyone had found a way to do this in SDDM. I was thinking that it could be done using a table_template but this will add the columns to tables that do not have them, and it will change the properties from what was RE. I was thinking it could be done with a custom transformation. I also considered editing the XML files directly using Grep for Windows.

    Do not want to re-invent the wheel if you have a solution.

    1. thatjeffsmith Post
      Author

      I think the best way to go about this today is to do a search for those attributes/columns. Then export that to Excel. Then change the values in Excel, and then import that back to your model – it will update the comments as you’ve entered them in the spreadsheet, back to your data model.

  162. Hi Jeff,

    We have installed the Oracle SQL Developer version 4.0.3 in the Citrix Hosted Shared Desktop environment running on top of Windows Server 2008 R2. We have been receiving calls from users that their application is getting crashed less than 2 mins after they launch it. Below is the event viewer log for your investigation. Being a Citrix admin, it is bit challenging for me to fix the issue. Any help from your your end be much appreciated.

    Oracle SQL Developer Version : 4.0.3.16
    Java(TM) Platform : 1.8.0_112

    Log Name: Application
    Source: Application Error
    Date: 1/3/2017 3:58:30 PM
    Event ID: 1000
    Task Category: (100)
    Level: Error
    Keywords: Classic
    User: N/A

    Description:
    Faulting application name: sqldeveloper.exe, version: 0.0.0.0, time stamp: 0x520cfeb5
    Faulting module name: ntdll.dll, version: 6.1.7601.23572, time stamp: 0x57fd02d3
    Exception code: 0xc0000374
    Fault offset: 0x000ce8fb
    Faulting process id: 0x6b38
    Faulting application start time: 0x01d265ab80e74c27
    Faulting application path: C:\Program Files (x86)\Oracle\SQLDeveloper 4.0.3\sqldeveloper.exe
    Faulting module path: C:\Windows\SysWOW64\ntdll.dll
    Report Id: 5e9fdaf7-d19f-11e6-877d-0050569d0128

    1. thatjeffsmith Post
      Author
      1. It’s a virtual machine running on top of VMWare ESXI. However i do see only “VMWare SVGA 3D” as a driver. Also when i try to update the driver software it says the best driver software for your device is already installed. Please advise.

        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
  163. Hello Jeff,

    Great blog! I have a question on SQL Developer –> “Is there a way to add an option in the right-click menu while on SQL Worksheet?”

    Regards,
    Bibhuti

    1. thatjeffsmith Post
      Author
      1. Thanks for the suggestion !! Could you please suggest me some tutorial/ documentation that I can refer to create the extension using JAVA (considering that I have prior knowledge in JAVA programming).

        I have read your articles on how to create extension using XML and I have tried searching the internet for creating extension in JAVA but have failed to get any head-start.

        Thanks in advance 🙂

  164. I would like to modify the {child}_{parent}_FK default naming convention as in my specific data model it exceeds the 30 character name limit (Oracle 11g XE).

    I have found this 2009 white paper:
    http://www.oracle.com/technetwork/developer-tools/datamodeler/datamodelernamingstandards-167685.pdf

    It states that this template can be found in General Options, but I can’t find in my SQL Datamodeler (Version 4.1.3).

    Could you please let me know where to modify that naming convention in recent versions?

    Thanks

    1. thatjeffsmith Post
      Author
  165. Can we use sdcli to run just a simple query and spool the output to a file?
    I was hoping to do this with sdcli so I can use the connections setup in SQL Developer(rather than using SQL Plus).
    I’ve seen the blog entry about “sdcli64.exe reports generate -report”, but was hoping to append to text rather than html.
    That is, append the results for one database, then another, then another.

  166. Hopefully just a quick question.

    Using git with

    Oracle SQL Developer 4.2.0.16.260
    Version 4.2.0.16.260
    Build 16.260.1303

    Whenever I am pushing to our master repository the user name gets an appended to it. This is flagging the commit as being from an unknown user. Is there something I missed in the setup – or someplace I can correct this?

    1. thatjeffsmith Post
      Author
      1. The link you had in your reply goes to a facebook picture of a sandwhich – do you have an alternate contact link for the JDev folks?

        1. thatjeffsmith Post
          Author
  167. Hi Jeff, I’m using SQL Dev DM 4.1.5. When I foward-engineer a logical subview to a relational model, the names are not being converted from logical to physical. I exported the names from the Glossary Editor to a CSV and imported the CSV into the tool via Name Abbreviations. All of the options are checked.

    What am I missing ???

    Thank you so much for your help !!

    Sincerely, Ed.

  168. Was trying to get results from a select on dbms_sqltune.report_sql_monitor via SqlDeveloper (4.2 in this case), but only get the very beginning of the results (~73 chars). Can’t do any set commands, so what can I do to get the full sql monitor report? Am I missing something really simple?

    Example:SQL Monitoring Report

    SQL Text
    ——————————
    select * fro

    Your site has been VERY helpful over the years.
    Thanks!

    1. thatjeffsmith Post
      Author
      1. I looked at your reference, was doing something similar (queries running longer than x secs as the parent, then child reports based on the sql id for futher info). But when I tried adding a “set long 200000” at the beginning of the child query, I keep getting ORA 922. Same with any set option. What am I missing here?

        set long 200000
        select DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST(
        sql_id => :SQL_ID,
        report_level=>’ALL’) as report
        from dual

        1. thatjeffsmith Post
          Author
          1. Still having an issue with setting linesize. I can run the child statement via sqlplus and it is formatted fine. But when running via SqlDeveloper, it still chops it off at 82. Even tried setting a login.sql with linesize at 200. Is it something to do with the call to dbms_sqltune maybe and sqldeveloper isn’t able to reformat it?
            Sample code from SQL Worksheet:
            set LONG 1000000
            SET LONGCHUNKSIZE 100000
            SET LINESIZE 200
            set pages 49999
            set trimspool on
            SET ECHO OFF
            SET FEEDBACK OFF
            select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
            sql_id => ‘&SQL_ID’, type=>’TEXT’,
            report_level=>’ALL’)
            from dual;

          2. thatjeffsmith Post
            Author
          3. Ok, I got it now that I see how the CLOB output get formatted. This opens up a lot of possibilities for t/s issues with this tool.
            Thanks!

  169. Hi Sql dev version 4.2 ; this error seems to just sit there
    Your database connection has been reset. Any pending transactions or session state has been lost.
    i repeatedly cancelled it or clicked ok on the message but it stuck takes couple of minutes to go away. This issues started with new version only.

    1. thatjeffsmith Post
      Author
  170. I’m normally an Access/Excel user and the ease of switching between them, such as copy and paste, is highly useful. Is there a quick way to paste rows of data from Excel into a select statement “Where In” portion into Oracle SQL developer?

    1. thatjeffsmith Post
      Author

      if you can figure out how to copy out a comma separated value list, then yes

      otherwise you’d have to use a macro or regex search/replace to format a list of values to be comma separated and/or quoted if not numbers

  171. I recently switched to sql dev 4.2 version. Since then all of my connections are dropping very frequently. i have to reconnect every minute or so. Earlier my connections used to persist for at least 30 minutes. I checked with DBA and no change was done at their end. Any help will be highly appreciated.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author