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

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

  3. 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
  4. 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!!

  5. 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
  6. 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
  7. 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
  8. 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?

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

  10. 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
  11. 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.

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

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

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

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

  18. 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
  19. 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.

  20. 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
  21. 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

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

  23. 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
  24. 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)

  25. 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
  26. 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
  27. 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)
    oracle.dbtools.migration.workbench.core.logging.LogInfo@4621971a

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

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

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

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

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

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

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

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

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

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

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

    I m using oracle sql developer 4.0.3.
    While debugging procedure or package smart data,watches window doesnt popup.
    What could be the reason for it ?

    1. thatjeffsmith Post
      Author
  45. Jeff,

    In SQL Developer, I can Right-Mouse-Button on a connections Folder and have the option “Disconnect Connections”. It would be nice to also “Connect All”. What the best way to suggest that as a feature for a future release?

    1. thatjeffsmith Post
      Author

      My Oracle Support or sqldeveloper.oracle.com and submit your idea.

      Connecting 300 connections at once, prob not a good idea, esp when you think about what could be in a login.sql script.

  46. Please provide an example for “Stock – Open-Hi-Lo-Close” in SQL Developer User Reports or Link to documentations to more than the very basic charts.

    1. Additional information…

      I keep getting the message “The data has insufficient number of columns. This graph type requires at least 3 columns of data…” Eventhough the SQL I used works for other Chart types.

    2. thatjeffsmith Post
      Author
      1. Hi Jeff,

        Thanks for your replay.

        Can you give me an idea of the format of the SQL the “Stock – Open-Hi-Lo-Close” Chart is expecting?

        For example, how many columns should the SQL returns and how will the chart determine what are the “Lo” and “Hi”?

        Thanks in advance.

  47. I upgraded from sqldeveloper64-3.2.10.09.57 to sqldeveloper-4.1.5.21.78-x64 but had to switch back. In ver 4 I found that a “select * from table_abc;” only shows a portion of the full column name whereas every other version prior to this has always shown the full column name regardless of the width of the data value.
    Is there an option in ver 4 to revert to the full column name?

    1. thatjeffsmith Post
      Author
  48. Hi Jeff,

    I successfully created a PostGreSQL connection with SQL Developer 4.1.3 but i can’t see the public schema, so i dont have any tree and can’t see my tables showing when I start my connection.

    How can I get my tree of the public schema ?

    Best regards

    Thibaut

  49. hello Jeff,
    i came across your post about Oracle SQL Developer: Code on Demand, and i have made a few on demand strings. The weird thing is when i type the ID sometimes i get the autofill and other times it doesnt show up?
    what am i missing here? are there certain circumstances where the ability to have the templates show up?

    thank you

    1. thatjeffsmith Post
      Author

      Could be a bug? But I would need to know more about when it doesn’t work – is it always the same template that doesn’t work, does it always work when the SQL Worksheet is blank?

      1. that has been the frustrating part. there has not been any specific instances when it does or does not work. i have tried different examples. blank worksheet, not blank worksheet, highlight the id, cursor after the id, ctrl+spacebar at both of them. i have been trying templates that i have made and one’s that were pre-loaded. i should have said, but i am using version 4.1.3.20

  50. Hi Jeff,

    I wonder is there’s a way in the SQL Developer CLI to export/import tests. We would like to use SQL Developer unit tests in a CI mode with Bamboo. We’d like to have the tests stored in a Git repository and have Bamboo import them into the unit test repository and then run them. Is it possible to export/import tests without the GUI and without human interaction?

    If not, we’ll need to write our own scripts which will put the test definitions directly into the DB tables. Thanks for your help. Jan

    1. thatjeffsmith Post
      Author

      YES 🙂

      Plus they’re also in db tables, you could probably move them around on your own using just SQL

      ┌─[15:32:04][wvu1999][MacBook-Air-Smith]:/Applications/SQLDeveloper 4.2.app/Contents/Resources/sqldeveloper/sqldeveloper/bin$
      └─>./sdcli unittest help
       Oracle SQL Developer
       Copyright (c) 1997, 2016, Oracle AND/OR its affiliates. ALL rights reserved.
       
      unittest ?
      unittest -run ?
      unittest -EXP ?
      unittest -imp ?
       
      Command Completed.
  51. Hi,

    I am looking at table properties. I have columns defined as NUMBER(15,2). When I switch to the Data tab, these numbers display as integers, i.e. no decimal portion. Is there a way to format numbers for the Data tab?

    Thanks!

    1. thatjeffsmith Post
      Author
      1. I see what is happening. Exactly what you wrote, i.e. zeros after the decimal are not getting displayed. Is there a way to force the decimal display for all numbers?

        1. thatjeffsmith Post
          Author
  52. Hi,

    I am using sql developer version 4.0.2.15
    After running a query i get results in “Query Results” tab when i can scroll and check all the columns or select and copy any row.
    But, Suddenly it got locked, as in i am unable to scroll or click anywhere in the Query result tab.
    I tried with fresh installation but i am facing “SAME” issue. Can you please help

    1. thatjeffsmith Post
      Author

      Is this with a particular query or table – or for any or all queries and tables?

      Can you pull down a copy of SQL Developer v4.1.5 with the embedded JDK?

      It’s going to have a much newer version of java and many many bug fixes since 4.0.2 was released.

  53. hi,

    Using Version 4.1.3.30

    Is there any way of changing the colour of the scroll bar in the Query Result pane. When you click on it the colour turns to a dark grey but otherwise it’s very difficult to see.

    Thanks
    N.

    1. thatjeffsmith Post
      Author
  54. hi Jeff,

    I’m using SQL developer version 4.1.5.21

    I’m trying to run a script which does ACCEPT to take user input with some format validations and default values. This scripts works as documented when I run in from SQLPLUS.

    Document Ref Url: https://docs.oracle.com/database/121/SQPUG/ch_twelve005.htm#SQPUG026

    I’m unable to get this to work in SQL developer however. Is this a limitation in SQL developer? If so, when can we expect this SQLPLUS like behaviour in SQL developer?

    1. thatjeffsmith Post
      Author

      It works for me in v4.2 – you didn’t give me a code sample, so I made my own.

      put in script, run with F5 – which invokes the SQL*Plus script engine we use

      get string, then prints string

      1. Hi Jeff,

        Thank you for the quick reply.
        Apologies for not being very clear in initial post.
        And I skipped the code sample because the reference URL had the examples in it that could be used.

        Here is the code sample that highlights the point I’m trying to make.


        ACCEPT date_time DATE FORMAT 'YYMMDD HH24MI' -
        PROMPT 'Enter date and time [YYMMDD HH24MI]'
        select '&date_time' from dual;

        DATE

        Makes reply a valid DATE format. If the reply is not a valid DATE format, ACCEPT gives an error message and prompts again.

        Now as per the documentation, the ACCEPT is expected to give an error message and prompt again for any input that does not conform to the mentioned format (ex. 162300 0000 or 161132 2500 or anything else that is not a date time of that format) .

        SQLPLUS honors this behavior as documented. But SQL Developer does not.
        I have not tried the version 4.2 yet, but with the version I have this has been the case so far. This is also the case with validation for other variable types

        PS: I could not figure out how I could share a screen shot like you did.

        Regards,
        Uday

        1. thatjeffsmith Post
          Author

          go get v4.2, you’ll be happy

          SQL> accept date_time date format ‘YYMMDD HH24MI’ prompt ‘Enter date and time [YYMMDD HH24MI]’
          Enter date and time [YYMMDD HH24MI] abcd

          Error starting at line : 1 in command –
          accept date_time date format ‘YYMMDD HH24MI’ prompt ‘Enter date and time [YYMMDD HH24MI]’
          Error report –
          SP2-0685: The date “abcd” is invalid or format mismatched “YYMMDD HH24MI”
          Enter date and time [YYMMDD HH24MI] 161130 1230
          SQL> select ‘&date_time’ from dual;
          old:select ‘&date_time’ from dual
          new:select ‘161130 1230’ from dual

          ‘1611301230
          ———–
          161130 1230

          SQL>

          1. Cool!.. so v4.2 is the version that does the trick!
            I just hope I can reuse all my others scripts just the way they are with new version.

            Thanks a lot Jeff!

            Cheers!

  55. Hi Jeff,

    I am using Orace 12c (Release 12.1.0.1.0) database and Oracle Sql Developer version 4.1.5.21.

    All table’s primary key columns are generated with GENERATED ALWAYS AS IDENTITY property. I understand that oracle internally created sequence (seq name assigned with random value) and assign it as default value.

    Now because of this every time i compare to database, all IDENTITY columns appears as difference between two databases (because of sequence name difference assign by Oracle randomly). I would be exclude all these columns here (tried with excluding sequence but it didn’t work).

    Please advise if there is any solution for this issue or any other alternative.

    Thanks,

    Anand

  56. In Data Modeler when “Previewing” a DDL is it possible to change the termination default semi colon to another character (i.e. slash ‘/’)?

  57. hello !

    i would like to ask you guys about the best and the most efficient method to upload million of records to one if the created tables of the database in sql developer and oracle 11g express edition

    Thank you in advance

  58. Hi Jeff,
    I am new in SQL Developer. I want to store bangla text in oracle 11g database. I have created a table with column type NVARCHAR2(50). My Database character set is AL32UTF8; Now I want to insert ঢাকা in my table and while I query on the table it should show me ঢাকা on result set.
    Could you please suggest me what to do?

    1. thatjeffsmith Post
      Author
      1. But I can not set font for Unit Test Implementation window.

        For example, I want to run a test with expected result set to ‘РФ’, but after my test is executed I receive the message: Expected: [‘????’] Received: [‘РФ’]…

        Would you please help with that?

        I can attach screenshot of this issue.

  59. Hi, when I ctrl+click on a function in another package that the one i’m currently in, it redirects me to the top of the package and not the function itself like other IDEs do.

    It’s the same case for all my team, is this a bug or a feature ?
    Thanks
    ________________________________
    Oracle SQL Developer 4.1.3.20
    Version 4.1.3.20
    Build MAIN-20.78

    IDE Version: 12.2.1.0.42.151001.0541
    Product Version: 12.2.0.20.78

    1. The same behaviour here!
      Ctrl-Click leads to the top of the package instead of going to the specific code in the body.
      SQLDev Version 4.1.5.21 build MAIN-21.78

      1. thatjeffsmith Post
        Author
        1. scenario:
          1. from navigator tree dbl click to open pck_body_a
          2. scroll trough code to find a reference to say: pck_body_b.proc_func
          3. hold down and hoover over the pck_body_b.proc_func name (it turns into a link)
          4. click on the link with down
          5. pck_b opens (not the body!) and places the cursor first position first line

  60. Hi Jeff,
    Last week i created a “create view…” script and executed it against the database from within SQLDeveloper. All went fine.
    The same script executed in SQL*Plus crashed while installing.
    My bad, i left some whitelines into the script.

    I love to work with SQLDev but it seems to execute code different from SQL*Plus.
    Is there some property/-ties i can set in SQLDev so SQLDev ran into the same errors like SQL*Plus?

    Greetz,
    Mark

    1. thatjeffsmith Post
      Author

      >>I love to work with SQLDev but it seems to execute code different from SQL*Plus.
      exactly, they’re completely different programs – if you’re going to write your script in one tool and deploy in another, you need to test-test-test.

      we emulate SQL*Plus behavior in SQL Developer when running your scripts with the F5/Execute as script button.

      What version of SQL Developer are you running?

      and please share a code example so I don’t have to guess what problem you’re running into.

      1. Wow Jeff! That(JeffSmith)’s fast! Great! 🙂

        Running all on windows with SQLDev Version 4.1.5.21 build MAIN-21.78

        The sample:
        CREATE OR REPLACE FORCE VIEW V_test
        AS
        select kol1
        , kol2

        , kol3
        , datum
        from test
        where rownum<10
        ;

        Between kol2 and kol3 there is a whiteline and this “beautifully” 🙂 compiles in SQLPlus:

        SP2-0042: unknown command “, kol3” – rest of line ignored.
        SP2-0042: unknown command “, datum” – rest of line ignored.
        SP2-0734: unknown command beginning “from test_…” – rest of line ignored.
        SP2-0734: unknown command beginning “where rown…” – rest of line ignored.
        SP2-0044: For a list of known commands enter HELP
        and to leave enter EXIT.
        1 CREATE OR REPLACE FORCE VIEW V_test
        2 AS
        3 select kol1
        4* , kol2
        No errors.
        SQL>

        With whiteline it only compiles in SQLDev, without the whiteline it compiles in both well.

        Thanks in advance.

        Greetz,
        Mark

  61. Why do can’t I set a datatype to timestamp with local timezone when I create a new table in SQLDeveloper. Sure can do it in the worksheet, but why not in the table editor?

    1. thatjeffsmith Post
      Author
          1. thatjeffsmith Post
            Author
          2. thatjeffsmith Post
            Author
  62. Hi Jeff, is there a way to export data into different workbooks of single excel file.i have multiple sql queries and i want to spool data into single excel sheet

    1. thatjeffsmith Post
      Author
  63. Hi Jeff,

    I have downloaded the SQL Developer 4.2 EA as I wanted to see the improvements to the PL[SQL] format feature. My intention is to try and integrate the formatting option into a git hook to ensure that the code is formatted before a commit to the repository. Do you have any advice or even perhaps some example code for this?

    1) Obviously this relies on SQL Developer being used in a command line mode, and in the archive there is no BIN directory. Can you please indicate how to use the command line feature in 4.2 EA?

    2) I have noticed that the format feature raises syntax errors presumably when it cannot parse the source.

    /*** Syntax Error at line 10, column 44
    Expected: identifier,
    ***/

    We use the SQL*Plus application to install the database schema. To ensure that the installation routines are flexible, we make extensive use of SQL*Plus substitution variables. The schema owner is there to ensure that objects are placed in the correct schema when following best practise of the thick database paradigm. e.g.

    CREATE OR REPLACE PACKAGE BODY &&owner..some_pkg AS

    (it was this line of code that caused the syntax error. The double period is intentional – the first period is the end delimiter of the substitution variable and the second is the schema delimiter)

    Do you consider this a bug with the format feature?

    Many thanks and well done to you and your team for a wonderful tool.
    Mark.

    1. thatjeffsmith Post
      Author
          1. thatjeffsmith Post
            Author
  64. Hi Jeff,
    Firstly a big thanks for you and your team on sqlDeveloper and sqlCl (I’m a convert).
    Question: Re Real Time SQL Monitor
    I’m presently user SqlDeveloper Version 4.2.0.16.260, build 16.260.1303.
    Can you confirm that Tools| Real Time SQL Monitor is an licence pack add on?

    Many thanks.

    1. thatjeffsmith Post
      Author

      INDEED IT IS – RSTM is part of the Tuning Pack. You buy enterprise edition of the database, then you license the tuning pack.

      When you first use this screen in SQL Developer – it warns you of this, so you can avoid licensing problems.

  65. Hi Jeff,
    I tried downloading the latest SQLcl today with the Nov 3 update and it gives me the below error. I tried in different browsers and i get the same result.

    Error
    Thank you for accessing the Oracle Software Delivery Cloud. Due to your country location, we are unable to process your request. If you have an active support contract, you may request physical media by either submitting a Service Request or calling Customer Support. If you wish to purchase or evaluate our products on a 30-day trial please contact the appropriate Sales Representative for your country.

  66. using sql developer version
    Version 4.1.5.21

    connected to a db2 database, query is returning hugh numbers (2553845.67760000000) .. some numbers are so large that display grid is showing OE-11. Query is doing a sum (SUM( openingsales ) as openingsales). Is there a preference setting that will prevent this error, or is there a better way .. ?

    1. thatjeffsmith Post
      Author
  67. 1. So for what I am gathering there seems to be a difference between a worksheet and a Code Editor.
    I know how to create an empty worksheet.
    How do I create a brand new Code Editor screen/tab?

    2. Formatting.
    I have this:
    procedure SOS_CPYRAW_1 ( p_i_fpath in varchar2
    ,p_i_fname in varchar2
    ,p_o_fpath in varchar2
    ,p_o_fname in varchar2
    ,p_err in number default 1 );

    procedure SOS_CPYRAW_1 ( p_i_fpath in varchar2
    ,p_i_fname in varchar2
    ,p_o_fpath in varchar2
    ,p_o_fname in varchar2
    ,p_err in number default 1 );

    It does not seem possible currently.
    Will it be improved to achieve that?

    1. Formatting did not work here.
      Basically I want the p_ aligned with the first p_ in the line, so that commas will be aligned with the blank space after (

  68. Hello Jeff,
    I am using Oracle version 4.1.3.20. I have a very large query that works when connected to a database, but then the same query fails when connected to a different one. As far as I am aware though, but databases house the same data/tables schema. The failed query has the following error: ORA-01792: maximum number of columns in a table or view is 1000.
    My end result is no where near 1000, but I am connecting to some very large tables but am not pulling all the fields on any of them.
    I cam across a possible solution but I am not sure how or what it is doing, so rather than potentially mess something up I wanted to see if you could provide any input. I came across the following code that would maybe prevent the error. Could you maybe provide some insight on what it is doing or why the error may be happening in the first place considering the tables/data should be the same as is the code being used?

    alter system set “_fix_control”=’17376322:OFF’;

    thank you

    1. thatjeffsmith Post
      Author
      1. that is precisely why I brought it up here. I don’t want to blow anything up. Do you have any idea on why there might be a difference between the two databases to cause this error or what I might be able to do then to avoid it?

        1. thatjeffsmith Post
          Author
  69. Jeff,

    My question related to SQL Developer Data Modeler is…

    How do I share an object (such as a table) from one relational model to the next (or from one diagram to the next) without making a copy of the object that can be separately modified and get out of sync? If I have the Companies table in model/diagram A (where it is maintained updated) and I simply want to reference it in model/diagram B for an FK reference, can I share the same version only for reference in B preventing modifications but still have updates from the object in A reflected in B??

    1. thatjeffsmith Post
      Author
  70. I am using SQL Developer Version 4.0.0.13

    When I open up a table and type in a filter to get exactly the record I want to look at… I can doubleclick any field to edit it. Works great.

    However, there is 1 field that is greyed out and no editing can happen ?? I am not understanding why this happens and how to fix it ?

    Thanks

    1. thatjeffsmith Post
      Author
      1. I can tell you that it is a VARCHAR2 (4000 byte) field that holds comments.

        I can also tell you it is frequently populated by a php form / webpage as well.

        1. thatjeffsmith Post
          Author
          1. could it be the length of characters?

            or maybe different characters such as / or >

            or maybe i just need an upgrade?

  71. Hello,

    I would like to ask if there is a way to enable the auto-complete feature of sql developer, for constants defined in a package specification, while writing plsql code.

    Regards,
    Dimitris

    1. thatjeffsmith Post
      Author
    1. Thanks for the response, but I am not trying to code JDBC.

      I am just trying to run this sql statement in SQL Developer. It works fine in SQL Plus.

      When you run it in SQL Developer you get the error described error, that I believe is coming from the JDBC driver SQL Developer is using.

      I could be very easily missing something simple. I switched to using the thick client and I get the same error which suggests that I am missing something.

      1. I take back this stackoverflow workaround ps.setString(1, “?”) — the server won’t accept syntax where question mark is bind variable inside pattern clause. It appears there no way to submit your query via JDBC. Therefore, until that JDBC bug is fixed, there is nothing SQLDeveloper can do about it.

  72. Is there a way to use the MATCH_RECOGNIZE clause with the ? character with SQLDeveloper and the JDBC driver?

    If I try to use the ? character as a reluctant qualifier token in SQLDeveloper with this example query

    with d as ( SELECT rownum true_rownum, floor(rownum/3) rn FROM dual connect by rownum <= 100 )
    SELECT * FROM d
    MATCH_RECOGNIZE (
    ORDER BY true_rownum
    MEASURES five.rn as five_rn,
    five.true_rownum as five_true_rownum,
    twelve.rn as twelve_rn,
    twelve.true_rownum as twelve_true_rownum
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN ( five anything*? twelve )
    DEFINE five AS five.rn = 5,
    twelve AS twelve.rn = 12
    ) mr;

    I get the following error "Missing IN or OUT parameter at index:: 1"

    I understand that's from the JDBC driver.

    I found this document about this and how to escape the character to let the sql engine process it
    https://docs.oracle.com/database/121/JJDBC/apxref.htm#CHECHCJH

    But when I try to do something similar
    with d as ( SELECT rownum true_rownum, floor(rownum/3) rn FROM dual connect by rownum <= 100 )
    SELECT * FROM d
    MATCH_RECOGNIZE (
    ORDER BY true_rownum
    MEASURES five.rn as five_rn,
    five.true_rownum as five_true_rownum,
    twelve.rn as twelve_rn,
    twelve.true_rownum as twelve_true_rownum
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN ( five anything{\*?\} twelve )
    DEFINE five AS five.rn = 5,
    twelve AS twelve.rn = 12
    ) mr;

    I get an ORA-00911: invalid character error.

    Is there a correct way to enter this syntax in SQLDeveloper?

    I am using verison 4.1.5.21.

    Thanks for your time. I enjoy reading your blog and using SQLDeveloper.

  73. Hi Jeff

    We are struggling with a large datamodel (3500 tables) and I am a bit desperate so I have to bring this question up again..

    In the SDDM Reporting Repository (v415), is it possible to get or derive information about the remote/linked status of a table (I have looked all over dmrs/dmrv tables/views – but nothing)?

    The intention is to use this status in a model quality report. Due to performance issues with Subversion and the complexity of many parallell Projects, the total model is splitt into 20 SDDM designs and we need to know which model is the Master when querying the Reporing schema. This is part of a model migration from ERWin (about 25 files) and we have a lot of mess to fix and the quality report is essential in this work.

    Thanks – any suggestions or WA will be appreciated
    Jo

    1. thatjeffsmith Post
      Author
  74. SDCLI64 report output – How do I control the report/column output format? I copied the Free Space report to User Defined Reports. When I run it in SQL Developer the USED column is formatted nicely with a number and % like 83%. However, when I run the report from the command line with SDCLI64.exe, the output is in a HTML file and looks like this: 0.833984375. Can I set the format for this somehow?

    Thanks

  75. HI Jeff,

    I have my SQL connections created trough ssh tunnels. Is there any way to export/import them as I do with the SQL conns?

    Thanks!!

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  76. Hello once again,
    I”ve got a problem with saving designs in SQL Developer. I’m using sqldeveloper-4.0.0.13.30-no-jre and Oracle DB11gXE.
    I ‘m working on a project (I create logical, relational and process models) and save it as .dmd file.
    When I open this .dmd file later – it doesn’t contain relational and process models I’ve just saved, only logical model is there.

    Any idea how to fix it?

    Thanks for any help

    1. thatjeffsmith Post
      Author
  77. hello,
    I’m just starting with SQL Developer
    I’ve got a problem with saving files. When I open a saved file it’s empty, there aren’t any models I’ve created and saved before.
    Any idea how to solve that issue?
    Thanks for any help

    1. thatjeffsmith Post
      Author
      1. I’m opening .dmd files, but they are empty after being saved. Actually, I can save and open logical models, but relational and process models aren’t there. When I save them and open the file, they’re empty.

        1. thatjeffsmith Post
          Author

          those are all in the same design (file) – what do you mean, ‘open the file’ – you’re not using a text editor are you?

          Data Modeler > File > Open…DMD. Everything should be there.

          1. yes, the same design, eg. if I create logical, relational and process models in one design, I save it, and then open .dmd file, only logical model is there, relational and process models are blank like I haven’t done anything with them

          2. yes, it’s the same design, e.g if I create logical, relational and process models in one design and save them, then I open .dmd file and only logical model is there

          3. thatjeffsmith Post
            Author

            something’s ‘wrong’ – you can provide more details of what you’re doing – say using sqldev with dm or standalone dm, and the version of the tool you’re using on our forums, and we can get you better response

            or if you have a db setup with My Oracle Support, you can open an SR with SQL Developer

  78. Jeff,

    I have a quick question that I have been trying to figure out for a while. I have built a new logical model in SQL developer. I have also been able to generate the relational model via ‘>>’. But i have not figured out how to specify that all these new tables should all have the same schema qualifier, i.e. ‘manuf.employee’. I see the dropdown on table properties, but their is nothing in the dropdown and I can enter it there (help panel does not even contain that dropdown). Google results talk about importing schemas and do not seem to apply to new models.

    Thnx,
    – rd

    1. thatjeffsmith Post
      Author
  79. Hi Jeff,

    is SQL Developer 4.0 also certified on Windows 10? It seems, it’s not.
    And are the certifications of Data Modeler the same as for SQL Developer itself?

    Thanks,
    Klaus

    1. thatjeffsmith Post
      Author

      yes, and it’s supported on Windows 10

      the OS support derives directly from whether or not the JDK is supported for that OS.

      Oracle JDK 8 is supported on Windows 10, so if you use JDK 8 – you should be good to go

  80. Hi Jeff,

    Is it possible to generate out of Data Modeler a script with all grants granted by a user to other user(s)? I know that they come out with the table ddls, but would like to be able to do them separately also.

    Thank you!

    1. thatjeffsmith Post
      Author
  81. Hi Jeff,
    currently i try out the sqldev E/A Version 4.2.0. yesterday during work the toolbar vanished and i could net get it back. switching the main toolbar of and back on (hope i get t he english terms correct as i use the german translation of sqldev) did not bring it back.
    when using the previous version everything seems to be ok.

    hope i could make my problem clear. is there a way to bring the toolbar back?

    Greetings
    peter

    1. thatjeffsmith Post
      Author
  82. Does SQL Developer support connections to Cloudera Hadoop – Hive/Impala with Kerberos enabled? We successfully connected prior to implementing Kerberos, cannot connect after enabling Kerberos on the cluster. Other client software like DBVisusalizer is able to connect. Thanks for the response. Not looking for support here, just an indication if this setup should be working or not.

    1. thatjeffsmith Post
      Author
  83. Jeff,

    This is a Oracle SQL Developer Data Modeler question.

    Is there a way or a dialog that will allow me to (re)define a relation between two tables, that are in my logical model, using columns that already exist in both tables and will not rename the existing columns on related tables?

    For instance

    Yesterday I made PERSON and USER table.
    Each has respective PERSON_ID and USER_ID as primary key.
    I made no relationship between PERSON and USER of any kind.
    Lastly I used USER_ID twice as foreign key on many tables (for ex TABLE_A.CREATED_USER_ID and TABLE_A.UPDATED_USER_ID)

    Today, I would like to define the relationship between PERSON and USER as Identifying (i.e. Person_ID 5 is User_ID 5) without impacting the existing column names on the USER table or USER child tables.

    Thanks,

    Shawn

    1. thatjeffsmith Post
      Author
  84. sqlcl question
    How can I get DDL to replace existing files in stead of appending to it?

    SQL> help save
    SAVE
    —-

    Saves the contents of the SQL buffer in a script. The
    buffer has no command history list and does not record SQLcl commands.

    SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]

    SQL> help ddl
    DDL

    DDL generates the code to reconstruct the object listed. Use the type option
    for materialized views. Use the save options to save the DDL to a file.

    DDL [ [] [SAVE ]]

    Would have been nice to be able to get the same options for DDL as well.

    1. And another strange behaviour I just noticed, is when I try to use DDL and SAVE for partitioned tables.
      The SAVE option doesn’t seem to work for partitioned tables (or indexes)

      SQL> help DDL
      DDL

      DDL generates the code to reconstruct the object listed. Use the type option for materialized views. Use the save options to save the DDL to a file.

      DDL [ [] [SAVE ]]

      Ok, so I try this: DDL TAB_NAME TABLE SAVE tab_name.sql

      SQL> DDL TAB_NAME TABLE SAVE tab_name.sql
      Object TABLE SAVE TAB_NAME.SQL TAB_NAME not found

      And, without the Object type, like this:

      SQL> DDL TAB_NAME SAVE tab_name.sql
      Multiple objects exist with the name TAB_NAME. Please specify one of the following types: TABLE,TABLE SUBPARTITION,TABLE SUBPARTITION,TABLE SUBPARTITION,… (x the number of subpartitions this table has)

      But if I try without SAVE it Works like a dream:

      SQL> DDL TAB_NAME TABLE
      CREATE TABLE “TEST”.”TAB_NAME”
      ( “ID” NUMBER(15,0) NOT NULL ENABLE,

      ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
      STORAGE(
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE “TEST_DATA”
      PARTITION BY RANGE (“ID”)
      SUBPARTITION BY LIST (“SUBLIST”)

      1. thatjeffsmith Post
        Author
        1. Thanks, Jeff.

          I have a couple of other observations for you as well.
          The first is that when creating scripts for all tables in a schema it seems to run out of open cursors. I had to increase this in order to get it to run without errors. Perhaps the cursors are not closed until the end?

          The other has to do with passing arguments to .js-script when running from command line in windows. These arguments does not seem to be available within the script when accessing them like this:

          for(var arg in args) {
          ctx.write(arg + “:” + args[arg]);
          ctx.write(“\n”);
          }

          1. thatjeffsmith Post
            Author
          2. The scripts are made in .js-files.
            A little shortened, but the essence is like this:

            script
            sqlcl.setStmt(“SET DDEL PRETTY ON”)
            sqlcl.run();

            sqlcl.setStmt(“SET DDL TABLESPACE ON”);
            sqlcl.run()

            var instance_project =util.executeReturnOneCol(“SELECT SUBSTR(UPPER(INSTANCE_NAME,5,4) AS INSTANCE_NA;E FROM V$INSTANCE”);
            var user = util.executeReturnOneCol(“SELECT user from DUAL”);

            var binds = {};
            binds.owner = user;
            binds.project = instance_project;

            var sql = “SELECT o.owner, o.object_name, o.object_type, o.created, …. “+
            ” FROM DBA_OBJECTS o WHERE … “;

            var ret = util.executeReturnList(sql,binds);

            for (i=0; i<ret.length; i++) {
            var row = ret[i];
            sqlcl.setStmt("DDL "+row.OWNER+"."row.OBJECT_NAME+" SAVE "+row.OBJECT_NAME+".sql");
            sqlcl.run();
            }
            /
            exit

          3. thatjeffsmith Post
            Author

            is this the actual code? it has a few typos

            sqlcl.setStmt(“SET DDEL PRETTY ON”) — should be
            sqlcl.setStmt(“SET DDL PRETTY ON”)

            var instance_project =util.executeReturnOneCol(“SELECT SUBSTR(UPPER(INSTANCE_NAME,5,4) AS INSTANCE_NA;E FROM V$INSTANCE”); — should be
            var instance_project =util.executeReturnOneCol(“SELECT SUBSTR(UPPER(INSTANCE_NAME,5,4) AS INSTANCE_NAME FROM V$INSTANCE”);

            the UTIL stuff is setup to close cursors as it goes, auto-magically. What version of SQLcl are you running?

          4. Sorry, no – I just typed it in from a different computer.
            The code runs ok, but uses all open curors so I had to increase the value quite a bit.

          5. Here is the actual code. It basically generates DDL for all tables changed after 01.01.2016.

            script

            var ddl_path = “C:/GIT/dvh/db-repo”;

            sqlcl.setStmt(“SET DDL PRETTY ON”);
            sqlcl.run();
            sqlcl.setStmt(“SET DDL STORAGE OFF”);
            sqlcl.run();
            sqlcl.setStmt(“SET DDL REF_CONTSTRAINTS OFF”);
            sqlcl.run();
            sqlcl.setStmt(“SET DDL CONSTRAINTS_AS_ALTER ON”);
            sqlcl.run();
            sqlcl.setStmt(“SET DDL SEGMENT_ATTIBUTES OFF”);
            sqlcl.run();
            sqlcl.setStmt(“SET DDL TABLESPACE ON”);
            sqlcl.run();

            var instance_project = util.executeReturnOneCol(“SELECT SUBSTR(UPPER(INSTANCE_NAME),5,4) as INSTANCE_NAME FROM V$INSTANCE”);
            var user = util.executeReturnOneCol(“SELECT user FROM DUAL”);
            var avgrensing_dato = util.executeReturnOneCol(“SELECT ‘01012016’ as dato FROM DUAL”);

            var binds = {};
            binds.owner = user;
            binds.project = instance_project;
            binds.object_type = ‘TABLE’;
            binds.deploy_date = avgrensing_dato;

            var sql = “SELECT o.owner, o.object_name, o.object_type, o.created, o.last_ddl_time, o.timestamp, :project AS project “+
            ” FROM dba_objects o WHERE o.object_type = :object_type “+
            ” AND o.owner = :owner “+
            ” AND o.last_ddl_time > to_date(:deploy_date,’DDMMYYYY’) “+
            ” ORDER BY o.object_name”;

            var ret = util.executeReturnList(sql,binds);
            ctx.write(“Tables…\n”);

            // loop the results
            for (i = 0; i < ret.length; i++) {
            var row = ret[i];
            var v_path = "/"+row.PROJECT+"/"+row.OWNER+"/"+row.OBJECT_TYPE+"/"+row.OBJECT_NAME+".sql ";
            ctx.write("DDL "+row.OWNER+"."+row.OBJECT_NAME+" "+row.OBJECT_TYPE+" SAVE "+ddl_path+"/"+row.PROJECT+"/"+row.OWNER+"/"+row.OBJECT_TYPE+"/"+row.OBJECT_NAME+".sql\n");
            sqlcl.setStmt("DDL "+row.OWNER+"."+row.OBJECT_NAME+" SAVE "+ddl_path+"/"+row.PROJECT+"/"+row.OWNER+"/"+row.OBJECT_TYPE+"/"+row.OBJECT_NAME+".sql ");
            sqlcl.run();
            }
            /
            exit

  85. I have a couple of subtypes in my logical model. They are supposed to inherit the attributes of the supertype, but they are not in the list. Attributes from other types of relationships (foreign keys) show up. Is there a way to see these attributes within the logical model or are they not added until the relational model?

  86. Using SQL Developer 4.2 I get an Error encountered popup. With the text

    An error was encountered performing the requested action:
    Closed Connection
    Vendor code 17008

    Doing the same action in 4.1.5 I do not get any errors.
    I am using Thick Driver with a client of 11.2. I have only reproduced in Reports. I get the popup if I have a report open something other the sqldeveloper has focus and return to sqldev the popup will be there. Can also reproduce locking screen, Windows 7 Alt-L, and returning to sqldev in a couple of minutes.

  87. I am using Oracle SQL developer 4.2 and found an issue with the copy of large records

    select sql_fulltext from v$sql;

    In other versions you can copy the entire statement from the Query Result. With 4.2 just the text which you can see. Seems like a bug or a setting?

    Other then that great release.

    1. thatjeffsmith Post
      Author
  88. I am using SQL Developer 4.1.5.21 with Mac OS Sierra 10.12 (16A323). It was working fine before Sierra. However, now, when I click on model or constraint or SQL tab in table view, it hangs forever. It shows window stating the it is trying to load module SQL data modeler. Is this a known issue? Also, I see following in logging window:
    Sequence 97 — Error in RequestProcessor oracle.ideimpl.usages.UsagesTrackerImpl$3

    1. thatjeffsmith Post
      Author

      export your connections (with passwords), and then rename or delete the system4.1.5 directory in your $HOME/.sqldeveloper directory.

      run again, restore your connections, and see if that fixes things up

      1. Thanks Jeff,

        Tried that. It worked. I still see “loading data modeler” but it comes back within 45 seconds. That is much better than more than 10 minutes it used before. 🙂

        1. thatjeffsmith Post
          Author
  89. I use SQLDeveloper on a daily basis – sometimes even more often than that.

    I tend to open some files, tables and reports together based on the task I am working on. I suspect that the “Document Groups” feature (Window -> Document Groups) would be great help, but I haven’t been able to figure out how to use it – maybe it’s just the wrong idea.

    I searched SQLDeveloper’s help for info regarding this, but haven’t find anything useful there.

    Meanwhile, I have to keep closing all files and manually opening and pinning each table, view, report and script file I need when working on a given task. Could you help me?

    What I want is to quickly and automatically recreate the environment I had when working on a given task – script files, tables, reports; all reopened together.

    Thanks in advance and keep up the great work Jeff!

    1. thatjeffsmith Post
      Author

      no way to do what you want today – but it’s something we’ve thought about, building a sort of project that would let you organize db connections, objects, and files.

      I’ll take this comment as another vote for “yes, please build this!”

      1. Somehow similar is this other situation: while working I am called to attend a meeting or some other distraction. When I come back, the session has timed out. I must close and reopen the connection to the database, therefore losing all the tables I had open. Big pain.

        1. thatjeffsmith Post
          Author
          1. I know! It rarely works. Also, I don’t see any error messages. Just right-click, ‘reconnect’, and still not connected.

            Is it OK to talk about this here, or should be filing a bug somewhere else ? I don’t want to bother you…

            TIA

          2. thatjeffsmith Post
            Author
  90. I am using SQL Developer Version 4.1.5.21 on Mac OS Sierra,
    java version “1.8.0_102”
    Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
    Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)

    and when trying to configure OCI using instantclient_12_1 I get an error:

    Testing the Instant Client located at /Users//instantclient_12_1
    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 … Failed:
    Error loading the native OCI library
    The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable DYLD_LIBRARY_PATH. Check it to verify that
    the expected native library directory /Users//instantclient_12_1 is present and precedes any other client installations.
    java.library.path = /Users//Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.

    Any idea how to workaround this?

    1. thatjeffsmith Post
      Author

      First – do you really need an instant client? If it’s just to use a TNSNames file – that’s not required.

      Otherwise, the error message tells you where to start.

      “Check it to verify that
      the expected native library directory /Users//instantclient_12_1 is present and precedes any other client installations.
      java.library.path = /Users//Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.”

      Also, configuring Instant Client on a Mac is not fun now b/c of the OS changes Apple made

      See this

      Installing Instant Client 12.1 on OS X

      Instant Client 12.1.0.2 supports OS X El Capitan, Yosemite and Mavericks.

      1. Download the desired Instant Client ZIP files. All installations require the Basic or Basic Lite package.

      2. Unzip the packages into a single directory such as “~/instantclient_12_1”. For example, to use SQL*Plus:

      cd ~
      unzip instantclient-basic-macos.x64-12.1.0.2.0.zip
      unzip instantclient-sqlplus-macos.x64-12.1.0.2.0.zip

      3. Create the appropriate libclntsh.dylib link for the version of Instant Client. For example:

      cd ~/instantclient_12_1
      ln -s libclntsh.dylib.12.1 libclntsh.dylib

      Note: OCCI programs will additionally need:

      ln -s libocci.dylib.12.1 libocci.dylib

      4. To be able to run SQL*Plus, update PATH. For example:

      export PATH=~/instantclient_12_1:$PATH

      5. Run SQL*Plus and connect using your database credentials and connection string:

      sqlplus hr/welcome@localhost/orcl

      Note: Custom applications should link with -rpath set to the directory containing Instant Client 12.1.

      1. Hi Jeff,

        Thank you for your reply, it was helpful, I was able to waorkaround this by adding:

        export DYLD_LIBRARY_PATH=/Users/username/instantclient_12_1

        to:

        /Applications/SQLDeveloper.app/Contents/MacOS/sqldeveloper.sh

        Now java.library.path is set correctly to:
        /Users/username/instantclient_12_1:/Users/username/Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.

        and it works:

        Testing the Instant Client located at /Users/username/instantclient_12_1
        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!

        SQLDeveloper and instantclient are the only tools available on Mac OS to connect to an Oracle database.

        Thank You,
        Codrut

        1. thatjeffsmith Post
          Author

          There are lots of java based solutions out there that support Oracle Database. From Oracle, you have SQL Developer & SQLcl, plus of course the Instant Client like you said. But there are more than a few 3rd party ones.

          I’m glad you’re using SQL Developer though.

  91. When I compare a model to my database, my model only cvers say 5 tables, but my database is huge – maybe 900 tables, and it takes forever to come back with a diff, even though I’m only comparing one bject. Does SDDM get metadata on the entire database, or filter it to thos in my model?

    1. thatjeffsmith Post
      Author

      i’m confused your model has 5 tables but you asked about comparing 1?

      we look at everything so you can see what db objects your design is missing…i think after you do a RE into a model, we assume you don’t care about the other objects going forward that you did not import and only look at the objects in the database that have been added since you did that RE

      1. Hi Jeff. I’ve just started using SDDM, I used to use ERWin. So does the compare only compare my model with the DB objects I have REd into my model, or to all objects in the database?

        1. thatjeffsmith Post
          Author

          It depends on how you built the model I THINK. If you begin your model by doing a RE, and you don’t grab a table, that table isn’t considered for the compare going forward. I THINK.

          1. thatjeffsmith Post
            Author
  92. Jeff,

    I believe my Oracle ID/Schema is missing nevcessary privs to use SQLdev effectively. I own no objects, but have been granted a custom role giving me access to the objects in another schema.

    I can see the tables (via synonyms) and data from the SQL Worksheet, but when trying to run pl/sql, I get ‘table or view not found’ errors.

    What do I need to tell/ask my DBA?

    1. I should add, anonymous blocks work fine, but create procedure or function fails to compile with the above error.

      1. thatjeffsmith Post
        Author
          1. thatjeffsmith Post
            Author
          2. From an SQL Worksheet (the same one, actually, where I am executing the CREATE proc or func statements), I can see, describe, and select data from all the objects referenced in the PL/SQL, but I am still getting the compile error “table or view does not exist”.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  93. Hello Jeff

    Atm. when i want to know the data type from an Column i have to go through all tables manually.

    Is it possible to get the data type info of the column also , if i execute an query in sql developer ?

    regards john

    1. thatjeffsmith Post
      Author
  94. Hi Jeff,

    is it possible to connect SQLDeveloper and DataModeler to GitLab (not GitHub!)? I tried, but when I enter my credentials and click Next, get Validation Failed message: invalid advertisement of !

    What I’m doing wrong?

  95. Hi Jeff
    is it possible to access in sql developer nosql avro schemas ?
    When connecting to a nosql db i can only see in the browser a empty folder “Tables”.
    Regards Günter

    1. thatjeffsmith Post
      Author
  96. Scripts containing packages.
    sqldeveloper doesn’t contain any type specific file endings (e.g. xxx.fnc for a function or xxx.prc for procedure), right? (Would be helpfull to distinguish file content by file extension)
    But my main “problem” is when loading a package from a script. I don’t have a navigation tree related to that script. The navigation tree is always related to the database!
    Compiling the script from file successfully results in marking it as having “no change” (italic label is gone). Sometimes I forget to press “save” reflecting the changes in the file system!
    When I accidentially click on same package in navigation tree I now have 2 tabs open with the same name only with different window title. Sometimes having lots of tabs open you don’t register the new tab. ctrl+s doesn’t throw an error. This results in having different package versions in db and in script file!!
    So it would be very very helpfull to
    – e.g. having different background colors in tabs created from files and from database source
    – having an own tree for packages within and only related to the tab
    – get a warning when opening a database object when same object is already open in an existing tab
    – mark tab from file as changed (e.g. by *) until it’s really saved to file system

    1. thatjeffsmith Post
      Author
  97. Jeff, I left a post about trouble building 1:1 identifying relationships in SQL developer a couple of weeks ago and then ended up traveling for business (sorry for the delay). I did some more troubleshooting to narrow down the problem and also have a model you can recreate the problem with. Also I want to make sure it is not a misunderstanding between definitions. So here goes:

    I am trying to build a 1:1 identifying relationship between two entities (lets call them parent and child). An example could the parent = a problem log, the child = resolution data about the problem. It is an identifying relationship, i.e. I want the parent key to become the child’s PK as well.

    The problem seems to be centered around SQL developer indicators on the relationship (identifying, source optional, target optional). There will always be a parent (i.e. source is mandatory). There may not be a child, the problem has not been ‘worked’ yet (i.e. target is optional).

    In SQL developer, if the relationship has identifying checked, then I cannot select target optional (target optional is greyed out)

    If I check identifying and then uncheck source optional, the model freezes.

    I suspect that I may be misinterpreting what SQL Dev means by those indicators, so I look forward to your help.

    I also have a model with two entities (named parent and child). To produce the freeze all you need to do is go into the relationship and uncheck source optional. Let me know where you want me to email it.

    This 4.1.3.20 on a Linux 64-bit CentOS (6.8) install.

    1. thatjeffsmith Post
      Author
  98. 4.0.3.16 When using SQL Worksheet and using the drop down list of connections, the list does not remain in the order as they are on the Connections panel. This behavior also occurs in the small connections box to the far right. We have well over 50 connections and it’s not fun having to hunt down the one we want. This must be a preference because my old SQL Developer version on my old computer maintained the order to be as on the Connection panel to the left.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          nope

          what order do you want it – alphabetical always? we had that forever and folks were always asking for an easier way to select connections that were already open when running a report or doing a db export…hence the change.

          1. Sorry, only now saw your response – stupid spam filter on my email. Mine does not seem to operate as you describe. It seems to list recently opened connections on top, even if they’re no longer active. So on any given day I might have over a dozen connections listed on top with the rest in alpha order.

          2. thatjeffsmith Post
            Author
  99. How Do I turn the following table:

    CASE TYPE AMOUNT
    1 civ 30
    1 tr 20
    1 crim 50
    2 civ 30
    2 crim 40
    3 civ 50
    4 tr 60

    INTO the following result:

    CASE CIV CRIM TR SUBTOTAL

    1 30 50 20 100
    2 30 40 70
    3 50 50
    4 60 60

    TOTAL 110 90 80 280

    Please let me know, my DBA gave me this question and I have been stumped!!!

    1. thatjeffsmith Post
      Author
  100. Hi Jeff, I’m working on a Java stored procedure. Every example I see uses System.err.println to display exception information. Is there a way to configure SQL Developer to display the output of these calls?

  101. Jeff, I am using SQL Developer and 12C for Intro to DB class I am teaching. I am bumping into a bug I can’t seem to solve or find a solution to. When I setup a 1: 1 identifying relationship between two entities, the application hangs. Some times prior to hanging, I see that a large set of FKs have been established on one of the entities. It appears to be some type of FK loop that is resulting. I am not sure which parameter on the relationship or the entities may be driving this behavior. I am using the Data Modeler from within SQL developer (vs the standalone version). Any insight would be greatly appreciated.

    Roland DePratti
    Eastern Ct State University

    1. thatjeffsmith Post
      Author

      Can you get me the DDL for the two tables causing the problem? Then I can re-create the issue here and see what’s what.

      I’m assuming you’ve got a schema you’ve engineered into a Logical Design? Or if you’ve created it from scratch need to get the design itself from you.

  102. Unable to connect using 4.1.3.

    I am trying connecting using Basic connection – that is using host, port and SERVICE_NAME.

    I get Network Error ( vendor code 17002 ).

    When I try the same using version 3.0.2, I have no problem.

    The symptom is closest to the thread below.

    https://community.oracle.com/thread/3881596

    Any ideas what could be wrong here ?

    Also – where do I find “TUNNELS” settings ?

    abhay

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  103. Hi
    I have captured the Schema in different versions of a product using the ‘Generate DB Doc’feature in Oracle SQL Developer. However, I now want to be able to get difference of these schemas with one another. Is it possible to use the output of ‘Generate DB Doc’ as an input to Diff function. Right now, I am only able to find an existing connection as allowed input to Diff function.

    1. thatjeffsmith Post
      Author
  104. Hey, Jeff

    I have Excel (2016) files to import. I am having SQLDev create a script for the import. as one column has over 4000 characters in it, so I need to change things like VARCHAR2(7342) to CLOB.

    (Its odd,though. In the generated INSERT statements, SQLDEV seems to recognize that the data is too long, as it splits the column into multiple concatenated TO_CLOB calls of 500 characters each. If it knows to do this, why does it still create column definitions like VARCHAR2(7342)??)

    The problem, though, is that TO_CLOB(‘yada yada yada;) || TO_CLOB(‘bada bing bada boom’) appears to be done in VARCHAR2 mode. Once this goes over 4000 characters, it generates “ORA-12899: value too large for column” errors. I don’t see how this made it out of testing before being released, so I am wondering (hoping) that I simply have a setting or two misconfigured.

    So, is this a bug or what?

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

        Same problem here.
        The ANSI expected output is ok but, after that the command appears either.

        I capture the “bug” to explain better (see on the above link):
        https://s17.postimg.io/g5q1wirr3/Cropper_Capture_4.png

        My sqlcl version is the latest ( sqlcl-4.2.0.16.260.1205 ).
        I’m running it inside ConEmu on an Window 7 machine.
        Update: running in DOS prompt results in the same problem.

        Sorry about my English!

        1. thatjeffsmith Post
          Author
  105. Hi Jeff,

    How do we go about housekeeping the unit test repository tables?

    ie. tables likes ut_suite_results, ut_suite_item_results etc.

    Is there any built it process for this?

    Thanks.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  106. Hi Jeff!

    How can i see xmltype value in debugger? I recompiled sys.xmltype for debug and type in watches changed to xmltype from opaque but value still empty.

    Also if i right click variable in watches there is “Object Display Preferences” line and inside are options “Invoke toString Method” and “Evaluate Expression”. If i could invoke getClobVal method then i get value of xmltype, is it possible? Also how this “Evaluate Expression” works? May be documentation exists?

    Thanks in advance.

    1. thatjeffsmith Post
      Author
  107. Hi Jeff

    In the SDDM Reporting Repository, is it possible to get information about the Remote design status of a table (same as in the Summary property of the table)? At least as a flag telling that this table is a Remote design table.

    Thanks
    Jo

    1. Hi Jeff,

      Let me add some details.

      We’ve got a pretty large data Warehouse datamodell – about 3500 tables. After migration from single user ERwin we now have about 30 different Designs in SDDM (v412) and are trying to consolidate/get overview by using the Reporting repository/schema. In our environment, large Designs run too slow against Subversion and in addition we run many Projects in parallell. To help out both these issues our plan is to have something between 10 and 20 designs in the future (logically separated to avoid Projects influencing each other to much).

      But some tables are common to multiple designs and we would like to use the Remote design functionality in combination with the Reporting repository to ensure that every table definition is maintained in one and only one Design. But so far, we have not been able to locate “Remote design” Properties in the Reporting repository (and the “Used as remote object” in the Reports module of SDDM only gives false).

      Have you got any hint of how/where to get the Remote design status of a table in Reporting repository?

      Another thing – with multiple designs – it would be extremly useful to have some kind of batch update of the Reporting repository (check out + export: for a given list of designs). The same applies to generating reports. Can this be solved now or are there any future plans?

      Greetings (and thanks in advance)!
      Jo

  108. Hi Jeff,

    I’ve been testing SQLCl and I issued a DESC dba_tables command, there weren’t answer from the client, I mean, my ssh client was locked 😮 .

    I had to close the client and re-enter again.

    Of course, with SQL*Plus I hadn’t any problem.

    My host is Linux x86-64.

    Greetings!!

    1. thatjeffsmith Post
      Author
  109. Hi,
    i connected to my sybase database in Oracle Sql Developer 4.1. Connection successful. but when i select File->Data Modeler-> Import -> Data Dictionary am getting the following error in compiler logs.

    Error during handleEvent action Data Dictionary(id=176).None of the attached controllers handle….

    i want to create a ER Diagram from the connected database schema.
    Please help on this as i am blocked on accessing the Data Dictionary.

    1. thatjeffsmith Post
      Author
  110. Hi Jeff,

    I’m having a small issue with using SPOOL to the delimited format. I specified my delimiter in Preferences as pipe (“|”), however when I spool to a csv file using the /*delimited*/ hint, commas are forced every time. I also tried using the SET colsep “|” command, but that also does not change anything. The only workaround that I’ve found so far is to run a SQL statement, than right click the output window and save it with pipe as delimiter – after doing that, running scripts using SPOOL results in pipe delimited files.

    Is there any better way to achieve this?

    I’m using SQL Developer Version 4.0.2.15

  111. We installed ORDS3.0 and now trying to connect to the tomcat from SQL Developer. We get the error: Error occurred retrieving Privilege. The target server failed to respond.

    Do you know what this could be?

  112. Hi Jeff,

    How do I escape a special character in the password?

    FANT @ //sa-dba-3-24.losrios.edu:1526/tstclone >bridge table_same as “jdbc:oracle:thin:fant/””myp#ssword””
    2* @hrdevdb3-24.losrios.edu:1526/hrtst.losrios.edu”(select * from table_same)
    ORA-01017: invalid username/password; logon denied

    ORA-01017: invalid username/password; logon denied

    Sep 08, 2016 5:46:50 PM oracle.dbtools.db.DBUtil handleException
    SEVERE: Warning, unhandled exception: ORA-00942: table or view does not exist

    Sep 08, 2016 5:46:50 PM oracle.dbtools.raptor.newscriptrunner.commands.BridgeTableDef execute
    SEVERE: Issue running BRIDGE command

    I tried putting the quotes around the \ before and after the password as well and it still didn’t work. see above.

    Thanks
    Tao.

  113. Hi!

    Have 2 quetions if someone can help out with them.

    1., Can I set the double quote to highligh string as single quote ones? As ‘test string’ will be marked as string, but not “another test string”, which is used for column namings.

    2., After a Ctrl+R Replace all, can I set in config to close both 2 rows with ESC or other hotkey rather than with mouse? Can I dismiss somehwo (would be better with shorcut key) the Highlighted replaced strings after checked them and looked ok? Right now I can only end the highlight with another CTRL+R replace invoke and close in with mouse. This is quite a long precedure with a replace all. I know I can disable highlight the result, but it is a good feature, just would like to dehighlight after it.

    Thank you,
    Tibor

    1. thatjeffsmith Post
      Author
  114. Hi Jeff, you gave my company a webex demo of SQL Developer Data Modeler and the SVN integration several months back. Lots of good information. I believe you said there was a configuration setting to make SQL Developer save models in LOTS of little files or FEWER bigger files? But I cannot locate that setting in build 4.1.1.888

    Thank you,
    –Tyler

    1. thatjeffsmith Post
      Author
  115. How do you add multiple constraints on one table column? I have an existing constraint defined in my logical model that uppercases. I also need a unique constraint. How would I accomplish that in SQL Data Modeler?

    1. thatjeffsmith Post
      Author

      two different questions, there’s a unique property you can set on any column, that will handle that

      you can define one check constraint per column in the modeler if you’re looking at the column preferences, but if you look at the table level constraints, you can add as many column CHECK constraints as you want there

        1. thatjeffsmith Post
          Author
  116. A quick question regarding viewing the database package body in sql developer. We’ve a procedure and within it a list of special characters that need to be removed. After we compile the package the db dictionary shows a different special character than I coded. Example. I’ve compiled the package that had a trade mark symbol and it stored as a diamond symbol. Not sure why it stores differently.

    Appreciate all the help.

    1. thatjeffsmith Post
      Author

      it’s possible that the character is stored just fine and your editor font just doesn’t support the display of the trade mark symbol. Check your editor display font in the preferences.

      it’s also possible you sent up a character to the database to be stored that’s not supported by the database’s character set.

  117. I have somehow caused my SQL Developer to open previous queries in tabs next to my connections. They used to open right of the start page. Any ideas on how to return them to their former place? Thank you.

    1. thatjeffsmith Post
      Author
  118. I review the Database Status from DBA Panel (very nice feature) but working with RAC view that SESSIONS graph doesn’t work with GV$…
    Any ideas about how to view GV$… information ?
    Do you know if there is any option about working with alarms from Database Status ?

    Regards

  119. I upgraded from SQL Dev 3.x to 4.1.3. When I run some existing queries with lines that have been commented out but still have an ampersand substitution variable (e.g. — a.org = ‘&p_org_code), the run of the query is now prompting to fill in the substitution. In 3.x (and someone here has 4.0), it did not do that. In those older versions, a commented-out line is commented out and no prompting occurred. I looked for a preference, but could not find anything related (at least that I could recognize). Any thoughts on how to get 4.1.3 to stop prompting for a commented-out line’s substitution variable?

    1. thatjeffsmith Post
      Author
      1. Thx for the reply! Although “set scan off” does indeed stop the prompting for “&” variables in conditions that are commented out, it also stops the prompting for “&” variables in conditions that are NOT commented out, but exist in the same query. Sometimes I have queries that I write for support purposes and depending on the information I have been given, I may uncomment/re-commment a condition line before running the query. Wondering what changed from 3.x/4.0 to 4.1, to cause it to look at a commented-out line differently.

        1. thatjeffsmith Post
          Author
  120. Jeff – while logging into sqlcl, I get the below error. How to suppress it?
    os: SUSE Linux Enterprise Server 11 (x86_64)

    sql user/pwd@oradb

    [INFO] Unable to bind key for unsupported operation: backward-delete-word
    [INFO] Unable to bind key for unsupported operation: backward-delete-word
    [INFO] Unable to bind key for unsupported operation: down-history
    [INFO] Unable to bind key for unsupported operation: up-history
    [INFO] Unable to bind key for unsupported operation: up-history
    [INFO] Unable to bind key for unsupported operation: down-history
    [INFO] Unable to bind key for unsupported operation: up-history
    [INFO] Unable to bind key for unsupported operation: down-history
    [INFO] Unable to bind key for unsupported operation: up-history
    [INFO] Unable to bind key for unsupported operation: down-history
    [INFO] Unable to bind key for unsupported operation: up-history
    [INFO] Unable to bind key for unsupported operation: down-history

    SQLcl: Release 4.2.0.16.175.1027 RC on Tue Aug 30 10:25:07 2016

    Copyright (c) 1982, 2016, Oracle. All rights reserved.

    1. thatjeffsmith Post
      Author
      1. Hi,
        I used sqlcl-4.2.0.16.308.0750-no-jre.zip download file.

        SQLcl: Release 4.2.0 Production on Mon Nov 07 14:20:33 2016

        The problem is still present !

  121. Hi Jeff,
    I have create a sequence and put it in the cart, but moving it to schema cloud fails with
    Error starting at line 5 in command:
    CREATE SEQUENCE “FUX_SEQ” MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 3 NOCACHE NOORDER NOCYCLE NOPARTITION
    Error at Command Line:5 Column:132
    Error report:
    SQL Error: ORA-00933: SQL command not properly ended
    00933. 00000 – “SQL command not properly ended”
    *Cause:
    *Action:

    1. thatjeffsmith Post
      Author
  122. I am using SQLDev V4.1.3.20.78 Windows 64-bit with JDK 8 included, is SQLdev independent of the java installations on my machine. The reason I am asking is keep getting Java errors Example
    Export connections give java.lang.NullPointerException
    Export data set java.lang.reflect.InvocationTargetException

    And I would like to know if this a SQLDev “bug” and I need to raise this with My Oracle Support, or something my desktop support team have done to my machine.

    Thanks

  123. Hey Jeff, I am using Version 4.1.3.20.

    I would like to do a diff between two tables, located in the same database. EG…

    Source:
    Database: Production10
    Schema: PointOfSale
    Table: OrdersBKUP

    Target:
    Database: Production10
    Schema: PointOfSale
    Table: Orders

    Is this possible in SQL developer?

    1. thatjeffsmith Post
      Author
  124. Hi Jeff –

    It will be better , if SQL developer handle the ref cursor output from package similar to PL/SQL developer. ( ie , I can scroll thru the result set , export into CSV , it is much more friendly) .

    My 2 cents ( feedback).

    Thanks

  125. The path you entered, is too long. Enter a shorter path
    File Name could not be found. Check the spelling of the filename,
    and verify that the file location is correct.

  126. In my SQL Developer (4.0.2.15 build 15.21), when I tried to import a PIPE separated text file (.txt) through the import utility, getting error message as “There are no readers registered for the txt type.”. With this error message, the operation is aborted and could not proceed further.

    What does this mean? I could open the “.txt” file with default editor Notepad.

    could you help me on this!

    1. thatjeffsmith Post
      Author
  127. Ok – I get it and understand – you are not support – but where can I log a potential bug with SQL Developer 4.1.3.20 Build MAIN-20.78?

    Just upgraded the Oracle DB to 12C. Now when modifying Materialized view – get an error about a wrong clause and it blows away the MV. If I take the DML and paste to a worksheet – and remove the no in memory clause – all works good. If I know where to log the bug – I will.

    thx in advance.

    1. thatjeffsmith Post
      Author
      1. CREATE MATERIALIZED VIEW TEST1 AS
        SELECT
        SYSDATE
        FROM
        DUAL;

        Modify the SQL Query to:

        SELECT
        SYSDATE AS DT
        FROM
        DUAL

        go to DDL tab – get the following:

        DROP MATERIALIZED VIEW TEST1;

        CREATE MATERIALIZED VIEW TEST1
        LOGGING
        TABLESPACE BAR
        PCTFREE 10
        INITRANS 1
        STORAGE
        (
        INITIAL 65536
        NEXT 1048576
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        BUFFER_POOL DEFAULT
        )
        NOCOMPRESS
        NO INMEMORY
        NOCACHE
        NOPARALLEL
        USING INDEX
        REFRESH ON DEMAND
        FORCE
        USING DEFAULT LOCAL ROLLBACK SEGMENT
        DISABLE QUERY REWRITE AS
        SELECT
        SYSDATE AS DT
        FROM
        DUAL;

        COMMENT ON MATERIALIZED VIEW TEST1 IS ‘snapshot table for snapshot FOO.TEST1’;

        an ORA-00922: missing or invalid option.

        If I copy the dml to a worksheet, and then remove the NO INMEMORY clause – it works. If you just click the OK button – one gets an ORA-00922: missing or invalid option message and the MV is dropped.

        You then have to grab the dml from the Edit MV window (which is now a simple create statement) and paste into a worksheet – and remove the NO INMEMORY clause – and it all works.

        1. thatjeffsmith Post
          Author

          i’m on 12.1.0.2 and this works – are you on 12.1.0.2 or 12.1.0.1?

          CREATE MATERIALIZED VIEW TEST1
          LOGGING 
          NOCOMPRESS
          NO INMEMORY
          NOCACHE
          NOPARALLEL
          USING INDEX
          REFRESH ON DEMAND
          FORCE
          USING DEFAULT LOCAL ROLLBACK SEGMENT
          DISABLE QUERY REWRITE AS
          SELECT
          SYSDATE AS DT
          FROM
          DUAL;
          1. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

            Working with our DBA team and we now have an SR open as well….

  128. Do not worry if you want to remove the blocked files or too long path files from your system, here I suggest a smooth way. Use “Long path tool” software and keep yourself cool.

  129. Hi,
    ever thought about a script manager? Don’t know the TOAD feature, but I know, we have added sql*plus script execution feature via ANT into eclipse, for the scripts you use so many times during the day. E.g. 1) create an application user (with the defined privs) 2) create the data objects in an app schema 3) fill the app schema with test-data 4) drop the test-data 5) drop the data objects 6) drop the user 7) analyze etc 8) create indexes on fks 9)…

    These are just some examples, why this makes sense. You should be able to choose (by checking) some of them and execute them in the order you can choose. The sql*plus output should be directed to the script output windows of the sqldev.

    Could perhaps be also developed as an extension…

  130. Hi,
    I installed sql developer on my new mac, but cannot find the folder location to move all of my snippets to. I looked in userhome\Library\Application Support but do not see a file for sql developer.. am I looking in the wrong place?
    Thanks!

    1. thatjeffsmith Post
      Author

      └─>pwd
      /Users/wvu1999/.sqldeveloper/system4.1.3.20.78

      I’m guessing you’re not ‘wvu1999’ on your machine – but that’s where you want to put stuff. I’d just zip up the entire directory and move it over.

  131. I am facing the following issue while opening SQL worksheet in SQL developer

    Popup with Error Message –> “An error occurred while opening ide.worksheet.file:/C:/users/%USERNAME%/Appdata/Local/Temp/worksheet…..”

    More information on error:

    java.io.IOException exception loading

    Some where down the line in error log :

    Caused by : java.lang.NoClassDefFoundError: oracle/ide/model/TextNode$gaurdedDocProvider

    After clicking on OK I am able to continue work as usual.

    I am not able to determine what is causing this problem. I do not want this pop up coming up every time I start a new worksheet.

    What I have tried:

    1. Reinstalling SQL Developer

    Any help is highly appreciated.

    Regards.

    1. thatjeffsmith Post
      Author
  132. In SQL-Developer if I format my PL/SQL-code which contains a variable declaration for a PLS_INTEGER-Variable, the keyword PLS_INTEGER is not formatted to UPPERCASE.
    A similar VARCHAR2-declaration is formatted to uppercase.

    Is there a possibility to define the keywords to be formatted?

    Thanks for your answer.

    Kind regards,
    Marc

    1. thatjeffsmith Post
      Author

      PLS_INTEGER isn’t a keyword, it’s an IDENTIFIER. That’s because it’s a type/subtype in the STANDARD package.

      If you set Preferences > Code Editor > Completion Insight > Chase case as you type to ‘ON’ and the dropdown to ‘lower keywords, Upper Identifiers’ – you’ll see pls_integer go to PLS_INTEGER as you type.

      In v4.2 the formatter let you set case for keywords and identifiers independently, and you can set identifiers to UPPER, which will cause pls_integer to go to PLS_INTEGER on a format

      Also, no, you can’t add your own keywords to the list. We maintain the list.

  133. Is there a particular XML DB Repository path under which a Resource must exist in order for it to appear under the SQL Developer connections pane object “XML DB Repository”?

    When an XML schema is registered using DBMS_XMLSCHEMA.registerSchema, it is automatically assigned the path:
    /sys/schemas/user_name
    and that schema file is then visible in SQL Developer in the connections pane object “XML Schemas”.

    Following that pattern, I’ve created user-named directories in the Repository, trying both upper and lowercase, placed under the directories:
    /
    /public
    /home
    and nothing is appearing in SQL Developer.

    Each directory is populated with a file to provide something to display.
    Both SQL Developer versions 3.1 and 4.0 have been used.
    The objects were created as the same user who is logged into SQL Developer, who has the XDBADMIN role.
    The ACL properties of a Repository object have been compared with a Schema object, and there is not a permissions/privilege difference.

    Thanks for any help.

    1. thatjeffsmith Post
      Author
  134. First, thank you for this useful blog and the information it contains!

    My question is about the panes in SQL Developer. I have a report written that I use mainly as shortcuts to things I access frequently. When I start SQL Developer, I generally run this report and then pull it as a tab next to the Connections tab on the left side of my window. Is there a way to ‘stick’ it there, so that it will appear there when I open Developer again?

    Thanks for any info you can provide!

  135. Hi Jeff,

    Thanks for the very informative blog! I return often for tips about using SQL Developer.

    Could you write a post on the Advance Format (ctrl-shift-F7) feature? Having just discovered the feature, I just spent the last hour trying to figure out how to output a quoted string for use in Java code. I did finally figure it out but I think your readers could save themselves an hour if you were to post it – perhaps in your “Shortcuts” entry.

    What confused me was the “Enclosed In” option – namely, I kept choosing the double-quote from the drop-down list which blew away my code. When I accepted the default, “Not Enclosed” it worked as expected.

    The other thing that I found confusing was the Output Destination “Clipboard” option; I assumed that selecting clipboard would result in SQL Developer copying my code, formatted as specified, to the clipboard when in reality, I learned from the Help page that I had to copy the code to the clipboard first and then the Formatter would format it.

  136. Would you know, in the SQL Worksheet, how to toggle the viewing the line feed characters on and off? I accidentally hit some combination of keys with my left hand (Ctrl+something near that) and now the <<P hidden character is displayed at the end of every line. I have searched Google and your website, and tried various combinations, but cannot figure out how to hide these characters. Any suggestions would be immensely appreciated. Thank you!

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

    Just wanted to drop you a quick note (not really a question) to say that I like your blog… a lot! I came across it looking for information on how to do certain things in Oracle Developer (what are sub views, what is a display, etc) and even clicked on the “About” button and read your brief bio 🙂

    I like the way you write and it even got me excited about database design again, even though it’s not my prior task at work (I’m in integration, SOA and that sort of stuff).

    Thanks and keep up the blogging!
    J

  138. Hi Jeff:

    I noticed that if I directly grant ‘execute’ on a procedure to another account, then log in to that other account, open the ‘other users’ node, then navigate to the account that owns the procedure, I will see that procedure listed.
    But when I grant the same privilege to a role that has been granted to the other account, and then log in to that other account and navigate to the account that owns the procedure, I do NOT see that procedure listed. Why is this happening? I want the other user to be able to see all the procedures I’ve given him ‘execute’ to through a view. Thank You.

    1. thatjeffsmith Post
      Author
  139. This might not be a SQL Developer question per se, so bear with me.

    I was able to get an HTML report from SQL Monitor output in SQL Developer 4.1.3, however, when I try to view it in any browser, I get a blank page! Initially, I thought perhaps I don’t have Flash installed on my VM (Windows 7), but even after fresh installation from Adobe (and restarting my browser), I was still getting a blank page. Then I assumed it must be something about Chrome that is not displaying the HTML report. I switched to IE (11) and the latest Firefox — still the same.

    Then I thought maybe there is something missing from my VM environment. So, I copied the report to my host machine (Windows 10), and I still cannot view the reports in either Firefox, IE or MS Edge! I can view Flash videos on all these browsers in both environments.

    One last ditch attempt I made to exclude possible issues. I used SQL Developer 3.2 to generate a report. Same thing. For both environments, in all the browsers.

    Funny thing is, I can view reports generated via SQL Developer 4.0 on my work machine (both the VM, Windows 7 and the Host, Windows 10).

    What are the requirements to view SQL Developer Monitor reports?

    1. And I just tried to load the HTML reports, which was generated on my home machine, on my work machine, and it still gives me a blank white page!

      But I can view the generated HTML reports on my work machine just fine. What’s going on?

      1. thatjeffsmith Post
        Author

        there’s an OEM bug that’s breaking the report. if you open the source of the HTML, you’ll see a reference to 12c or 12.1.0.1 (or maybe 2). if you change that to 11.2, the report will work

        we’re building a completely brand new real time sql monitor interface for v4.2 that doesn’t rely on any OEM code or even an internet connection

        1. Excellent. There were several places I had to replace “12.1.0.2.0” and “12.1.0.2” with “11.2”, and it did the job. Thanks.

          P.S. Do you guys have a release date for v4.2?

          P.S.S. Apologize for omitting the database version in my original post.

          1. thatjeffsmith Post
            Author
  140. I have a large folder of .sql files on my Windows 7 64 bit machine. When I double click on 1 .sql file the script will open in sql developer. Any subsequent .sql file will not open from within the folder. The file types are associated with sql developer on the windows and application level. It is as if there is an instruction set that states that only 1 .sql file will be opened by double clicking from within a folder. After this no others can be opened in this manner. I can’t find a registry key that would dictate this behavior. Getting to the hair pulling stage and just had to ask That Jeff Smith.

    1. thatjeffsmith Post
      Author

      there’s something up with our app, or with java on windows – for now you can drag and drop the file from explorer into sqldev, or use the Files dialog in SQLDev to get easier access to your files

      i’m working to have this ‘fixed’ in v4.2 if it’s something we have control of or access to when it comes to Windows

  141. Hi,

    I am exporting a table to a CSV file using SQL Developer version 4.1.2.20.
    I need ALL columns to be enclosed in double quotes ” (or any other quotes). However, only strings and NULLs are enclosed, but not numbers/dates, e.g:

    65,1,””,28-FEB-14 00.00.00,552.85,0,”OPEN0000000124″,12027,81361,1,1213,8366,1019,1,1061,,,4345584,4345633,””,””,92,4107039,””,4345633,8361,”B”,””,65,,,,195,,,,,,,,,

    Is it possible to have quotes for all columns?

    Thanks a lot
    Pawel

    1. thatjeffsmith Post
      Author
  142. Hi Jeff,
    When we run following code on different versions of SQL developer, it returns different results. Would you please shed some light, why this ?

    —————————-
    /*Enable the SERVEROUTPUT to display block results*/
    SET SERVEROUTPUT ON
    /*Start the PL/SQL block*/
    DECLARE
    L_STR VARCHAR2(10);
    L_COUNT NUMBER :=0;
    BEGIN
    /*Capture the system time before loop*/
    L_COUNT := DBMS_UTILITY.GET_TIME;
    /*Start a loop which assigns fixed numeric value to a local string
    variable*/
    FOR I IN 1..1000000
    LOOP
    L_STR := 1;
    END LOOP;
    /*Print the time consumed in the operations*/
    DBMS_OUTPUT.PUT_LINE(‘Time Consumed:’||TO_CHAR(DBMS_UTILITY.GET_TIME
    – L_COUNT));
    END;
    /
    ———————————————

    RESULTS:
    SQL DEVELOPER Version 4.0.2.15 – Time Consumed : 0
    SQL DEVELOPER Version 3.2.20.10 – Time Consumed : 12

    Thanks in advance.

    1. thatjeffsmith Post
      Author

      what does sql*plus show, what does v4.1.3 of SQL Developer show?

      which result do you consider to be the ‘correct’ one?

      there are probably environmental differences between those 2 different SQLDevs, specifically the JDBC driver and your NLS parameters in Tools > Preferences

          1. thatjeffsmith Post
            Author
          2. In below code also we don’t have any side effects of loop but it gives same result on both the version.
            SQL DEVELOPER Version 4.0.2.15 – Time Consumed : 4
            SQL DEVELOPER Version 3.2.20.10 – Time Consumed : 4

            /*Enable the SERVEROUTPUT to display block results*/
            SET SERVEROUTPUT ON
            /*Start the PL/SQL block*/
            DECLARE
            L_STR VARCHAR2(10);
            L_COUNT NUMBER :=0;
            BEGIN
            /*Capture the system time before loop*/
            L_COUNT := DBMS_UTILITY.GET_TIME;
            /*Start a loop which assigns fixed numeric value to a local string
            variable*/
            FOR I IN 1..1000000
            LOOP
            L_STR := ‘A’;
            END LOOP;
            /*Print the time consumed in the operations*/
            DBMS_OUTPUT.PUT_LINE(‘Time Consumed:’||TO_CHAR(DBMS_UTILITY.GET_TIME
            – L_COUNT));
            END;
            /

          3. thatjeffsmith Post
            Author

            there’s no…optimization. We just submit the code, and Oracle runs it.

            You could use DBMS_PROFILER to see the exec time of each line of PL/SQL being ran in your anon block

            main changes between those versions are the jdbc driver being used and possibly your NLS parameters are different – which can cause oracle to choose different execution plans

      1. Thanks for the quick response.
        SQL DEVELOPER Version 3.2.20.10 shows correct result.
        I will check with sql plus and SQL developer v4.1.3 and get back to you.

    1. thatjeffsmith Post
      Author

      I would use a reporting solution, Oracle offers several. SQL Developer doesn’t do email, but you could use it to write a store procedure to do it – since the Database DOES do email. It’s pretty straightforward with jobs.

      And i’m pretty sure someone has a stored proc that can create Excel files. Or you could just write out CSV.

  143. Howdy Jeff,

    Really appreciate the good work in developing SQLDeveloper into a nice-to-work-with tool – it makes my life much easier!

    Currently, we are attempting to reconcile a number of non-connected databases to a common standard (they are standalone databases that cannot be connected to anything else).

    Doing this will help us conform to the DoD DISA STIG requirements for these environments, enabling us to verify all objects and configurations conform to a specific baseline.

    At this point, we are using a combination of SQLDeveloper (Database Diff tool) and hand-wrapped processes, and have experienced a couple issues in the SQLDeveloper arena.

    What would be the best way to approach resolving these issues from your perspective (on ThatJeffSmith.com or OTN or Oracle MetaLink)?

    Recently we have experienced three issues while using SQLDeveloper 4.1.3.

    1. Add tablespace generates bad DDL for temporary tablespaces. The generated SQL is ‘add data file’ instead of ‘add temp file’.
    2. Where are the Policies on Tables when using SQLDeveloper? We can locate them with manual SQL, but they do not seem to be visible within SQLDeveloper.
    3. Constraint differences are corrected via ‘Create Table’ instead of ‘Alter Table’. We are moving all the non-named constraints (SYS_####) to named constraints. Using the generated correction SQL of ‘Create Table’ will be very difficult to implement. It would be much easier to implement with ‘Alter Table’ instead. We are manually making these modifications by using the dbms_metadata.get_ddl routines.

    Signed … YetAnotherJeff

    1. thatjeffsmith Post
      Author

      I would open an SR with MOS since you most likely have an account – and we’ll have a ‘written record’ to make sure nothing slips through the cracks.

      #1 would def be a bug – and an easy one to fix. There’s like 3 different ways to add a datafile to a tablespace, so make sure you tell us exactly how you’re doing it in the UI.
      #2 we just don’t show them – but you could add it yourself. have you see this?
      #3 weird, we’ll need a kind of before and after to know for sure, but adding like a check or not null constraint should already be doing an alter, or even just a Create Constraint…

      1. Many Thanks for the quick response!

        #1 We will document it and send it to Oracle Support.
        #2 Thanks for the reference link. I have not yet added the policies, but this looks VERY useful.
        #3 You mentioned ‘WEIRD’. Is there a parameter we can change to get SQLDeveloper to produce the ‘ALTER’ or ‘CREATE CONSTRAINT’ commands instead of the ‘CREATE TABLE’ with the constraints listed in-line?

        1. thatjeffsmith Post
          Author
  144. Are there any issues with SQLDev v4.1.13 running on Windows 10? When I startup it goes into a long process of loading and then it seems not all options work in Load Data method. I am using a no-jre and also a embedded jre versions. Both have the same behaviour.

    1. thatjeffsmith Post
      Author

      no known issues – sounds like an ‘install’ issue – maybe you put it down into an existing copy of sqldev? or maybe a file got corrupted.

      try installing to a fresh directory

      could also be an OS user privs thing – you need full read/write access to your appdata, roaming profiles folder for sql developer

  145. Hi Jeff,
    in my projects i intensely utilize carts to save source of Database Objects in our subversion scm.
    I collect all changed objects in a single cart, and when reaching certain milestones i export the whole cart to update the svn repository.
    No matter which export i use (separate-files, type-files or separate directorys) none of them overrides existing files. each export generates a new set of files adding a number to the filename. to get a version history in svn i manually have to delete the existing files before exporting. is there a way to instruct the cart export to override existing files?

    and one more thing:
    when exporting in multiple files a a collection of calls for each of them is generated in a single sql file. in my opinion using the complete path to call the subsequent sql-files is a bit cumbersome. for example “@C:\Compile\p.ettinger\kundenanpassungen\Schmoll\Sonstiges\APEX\SQLDEV\TAUTHTYPEROW_2.sql”
    when deploying the set of scripts to the customers system, i have to edit all the paths or build up the same environment on the customers machine that i use on my dev box. is it possible to generate relative paths like .\TAUTHTYPEROW.sql instead?

    Greetings
    Peter

      1. Thanks for your reply. But your solution does not realy fullfill my needs. But i will store your approach for later use.
        maybe Jeff could comment on my question and our request for relative paths

          1. Sounds like a good idea for an enhancement to SQL Developer.

            However, I will suggest the alternative approach (which I use) which is only to generate the initial set of DDL scripts once, check them into source control, then from then on do your editing on your local copy of those scripts – i.e. don’t edit the objects directly on your database.

            Cheers 🙂
            Jeff (but not That one)

  146. My client has version 4.0.1.14 running in her VM. For many months now she runs a simple SQL scripts that spool several output files to her local drive. In SQL*Developer she just types
    @C:\’ and clicks the ‘run’ arrow. Starting last month the script now runs for a short time (minutes) and then seems to halt. I’ve looked at the process and it’s now in an INACTIVE state and generating zero (0) I/O’s. The script does nothing more than a series of ‘spool , select * from , spool off’.

    I also tried it from my laptop and got the same results.

    There is plenty of disk space in both our environments.

    We have made no changes to the SQL*Developer setup.

    Any ideas why this has just now started happening?

    Is there a setting somewhere that limits the number of output lines?

    Thanks very much for your help!!!

    -gary

    1. thatjeffsmith Post
      Author

      script output is limited in that version of sqldev ‘Max rows to print in a script’ on Worksheet preferences

      4.0.1 had a few bad bugs, can you upgrade to 4.0.3 or even 4.1.3?

  147. Hi Jeff,
    We are adopting Data Modeler as a replacement for Oracle Designer. But we are facing some issues. We used to generate our changes in DDL against one or more existing databases.
    We have exported our Designer Workarea and got a logical and relational model. Is there a possibility to generate form this model against the database. There seems to be no link to any database in the model and no way to establish one. It looks like the redirect option connects to the database but does not recognize our existing tables.
    I also tried to import the existing database schema. This works but to generate our changes we would have to apply every change manually to (domains, etc) to this relational model, which comes with a lot of extra work.
    Is there a way to work with Data Modeler in a similar way as we did with Oracle Designer?

    1. thatjeffsmith Post
      Author

      apply every change manually (to domains, etc) – can you elaborate more here?

      As you’ve observed, the compare to db/synch works best when the source objects come from a database reverse engineer.

      1. The relational model that we exported from designer (R1) differs significantly from that exported from the data dictionary (R2). The latter contains no domains for example.
        I tested with a simple change in a domain. Our analyst changed the domain in R1. As R2 was extracted from the database the datatype was varchar2 and it had a List of Value containing the domain values. I had to change the datatype and make sure the domain was used to be able to generate a simple alter table.
        If I have to go through all of this for every domain, we will have a lot of work to synchronize our relational models. Not to speak of other changes…
        If necessary I can provide you with some screen shots of the situation we are facing and what we are trying to accomplish.

  148. Hi Jeff,
    what about (possibly planned) user defined foldings within SQL-Scripts. Often scripts spans much more than 1 editor “page” and supplied standard foldings e.g. of a WITH-Block are not enough to focus on actually developed script lines …

    kind regards
    Edward

  149. Hi Jeff,

    I’m a new user to SQL Data Modeler, but am working my way through it. So far, so good. My question is: we use ClearCase for source control. I’m saving my design with the multi-file option. Exactly what files should go into source control? The product has generated almost 2000 individual files.

  150. Hi Jeff,
    Just love the productive gains SQL Developer has given to me and the team!.

    I have made some custom reports and have using the tip from http://www.thatjeffsmith.com/archive/2012/07/using-html-to-mark-up-your-data-in-oracle-sql-developer/ to mark up the output. This is great for on screen display but form time to time I need to export to xls or pdf and it shows the HTML markup. Is there a way to export with out going to a non-markedup vesion? I was hoping I could use Variants, one for screen and one for export.

    Thanks for all you support!
    TR

  151. Can exports run simultaneously in SQL Developer? I started an export in one session, which seems to be running fine, then opened another session and started another export. The second export “started”, but it’s not exporting any rows.

  152. hii..
    recently install the oracle developer 4.0.1 ver on my windows 10 64 bit.
    how would i unistall that database..??

    1. thatjeffsmith Post
      Author
  153. Great tool, without a doubt superior to Toad. I move around a lot and prefer SQLDeveloper to TOAD for the simple reason that they can never find the TOAD software key to activate it. Thanks for all your hard work.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  154. Hi Jeff, I want to export the “views” that I see when I open up sql. Is this as simple as migrating over the sql app data folder in roaming?

    1. thatjeffsmith Post
      Author
  155. Hi Jeff,
    In data modeler, I prefere to create the foregin key column when creating the relation between paraent and child.
    When creating the relation, data modeler creates also the column in the child table and generates a name for it, and here is my issue.
    the generated column name is formatted like that ( ParentTableName_ParentPrimaryKeyColumnName ).
    I want the foreign key column name to be only the same as its referenced column in the parent table like that ( ParentPrimaryKeyColumnName )
    every time I modify it manually in the child table after creating the relation.
    is there any configurations or preferences in data modeler to achive that?
    thank you

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  156. A question about navigation the the PL/SQL editor:
    It’s possible to ctrl-click a procedure and SQL Developer will take you to that procedure. So you might do this a few times to drill deep into code to follow the flow. However, there doesn’t appear to be a way to navigate from whence you came. Is this possible in SQL Developer ? If not, how can I put a request in to get in icluded!!! 🙂

    1. thatjeffsmith Post
      Author

      There’s a way. Each doc opens up on your desktop. The previous object is still there, and if you navigate back or ‘down’, the curpos is preserved from where you did the click-through. So just use the kb to navigate to the previous document/window.

  157. Hi, Jeff.
    I am new to SQL Developer and (since i am TOAD person) i have a simple Q.
    Is it possible to open more then one Schema Browser in the left pane? This would be a great help if one needs to work with multiple schema objects.

    Thnks in advance.

    Mike R.

    1. thatjeffsmith Post
      Author

      SQL Developer doesn’t have a Schema Browser like Toad has. In Toad, the Schema Browser has the left and right hand side – and there’s no way to get to the object editor w/o using the left hand side…unless you use their DESC feature.

      So, in SQL Developer, you can open as many objects as you want, using only one navigational item – be it our schema browser dock-able panel, or the connections panel.

      I talk about how to see two tables at once here – note this tip applies to working with ANY database object.

  158. I’m getting below error while connecting database on SQL Developer Version 4.1.3.20

    Failure – test failed : oracle/jdbc/proxy/ProxyFactory

    Can you please help with this?

    Thanks!

    1. thatjeffsmith Post
      Author
  159. Hi Jeff,

    I am trying to query the below string.I would like to be able to pull out either Skipped or Completed Items. I currently do it using excel (painstakingly). I’m wondering if there is code I can use to pull out only what I want after either completed or skipped. The string is not always the same length, so I am not sure how to do it.

    “It beings 14:58:59
    Completed: TASK 1
    Completed: SOMETHING 2
    Skipped: ANYTHING 3
    Skipped: WHO 4
    Completed: WHAT 5
    Auto Play stopped automatically 15:00:59 in 120 seconds”

    If I wanted all completed I would like the final result to be:

    TASK 1
    SOMETHING 2
    WHAT 5

    Or if I wanted all Skipped I would like the final result to be:

    ANYTHING 3
    WHO 4

    Is there a way to do this, or am I stuck doing it in Excel?

  160. Hi Jeff,

    I have installed oracle 11g express edition and also sql developer.
    I used sql developer and restarted my laptop. Later, i tried to open sql developer it is directing to oracle web page. I want to use it back please help me with the solution.

    1. thatjeffsmith Post
      Author
  161. Hi Jeff,
    using sqldeveloper 4.1.3.20 formatter destroys format when assigning multiline text to variables.
    Sample code:
    DECLARE
    v_SQL VARCHAR2(4000) := ‘
    SELECT name
    FROM person
    WHERE ID = :ID’;
    BEGIN

    END;

    results in:
    DECLARE
    v_SQL VARCHAR2(4000) := ‘
    SELECT name
    FROM person
    WHERE ID = :ID’;
    BEGIN

    END;
    after formatting. All lines are left aligned now.
    Is there any option to tell the formatter NOT to left align text constants?
    OR when will the new formatter be available?
    Thx,
    Dirk

    1. thatjeffsmith Post
      Author
  162. Hi Jeff, thanks for this gread blog!

    I have two databases, one for dev and one for production. We use the SQL DM to create the model for our 4GL Environment.
    However, we had a major change in development organisation and now the schemas are now called differently. For instance dev.table@dev equals product.table@prod, where it used to be product.table@dev to product.table@prod. I hope you get the gist.
    If I try to import a new table from dev to prod, the DM tries to recreate all the objects anew, since he does not know the dev schema and starts with dev.table1, dev.table2 etc.

    Is there a way to translate (like with imp-/expdp) schemas with the import? Or when the only way to import one single table (uncheck all the other) to reassign the schema after import?

    Cheers,
    Marc

    1. thatjeffsmith Post
      Author

      You’re doing a data dictionary import in the modeler, and you want the modeler to treat dev.stuff = product.stuff? Just making sure I’m on the same page before I go to figure this out 🙂

    1. thatjeffsmith Post
      Author
  163. I’ve created a master/child report to monitor certain ODI sessions (master) and tasks within sessions (child). I would like to draw the users attention to tasks of a certain status, for example to display the child row in red for a task with status “E” (one of the columns in the child query). Is it possible to do this (in code perhaps).

    Very nice blog btw!

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  164. Jeff,

    In SQL Developer 4.1.3, when I open a package to edit, a task starts running “Comparing XXX Body subprograms” which is slowing things down, sometimes locking up the session. This wasn’t done automatically in 3.2. Is there a setting to turn this off? I didn’t see one while digging thru the preferences.

    Thanks,

    David

    1. thatjeffsmith Post
      Author
  165. Hi.

    Is it possible to call formal parameter list of function or procedure?
    ctrl+space give some advice but not always and look like in your topic “SQLDev can ‘remember’ for you”.
    Last version.

  166. hi
    I’m having same problem when i’m trying to open sql developer its showing error-
    “Unable to open the following configuration file:[c:\app\Pavilion\product\11.2.0\dbhome_1\sqldeveloper\ide\bin\product.conf]”
    I’m using windows 8.1 os and oracle 11g 32 bit product .

    Thanks in advance.

    1. thatjeffsmith Post
      Author
  167. Hi Jeff,
    I’m an Oracle DBA and I’ve been using SQL Developer for many tasks inside our Oracle 12c Database, in our development environment. Sometimes SQL Developer gets too much time to connect using DBA role users (minutes). Or even to run a very simple query returning a dozen of records. Only with DBA users, not with an unprivileged user. Only in SQL Developer, not in SQL*Plus, for instance.

    Should it be loading additional data (from dictionary?) and/or experiencing some kind of contention?
    Have you ever seen something like that?

    Best regards,
    APMeire

    1. thatjeffsmith Post
      Author

      are there db wait events? have you captured a trace?

      there shouldn’t be any issues native to SQLDev that would cause what you’re seeing…you can use the View > Log > Statements panel to see the queries that get sent over jdbc along with the timings in case it’s a SQL issue

  168. Hi,

    Have been using the data modeller extensively as a standalone on a local drive. Now a new person has joined the team and he is taking over the modelling. He has successfully installed the software on his machine and had a play and it all seems fine. How do I get the model to him? Will copying the directory to his machine give him access? I am also thinking that putting it on a shared drive means that he could update it and I could access it to review (but not update).

    I checked with our tech group and they asked the following “Which file within the files on the C: drive would need to move onto our shared network file space (is there a specific db file to move) and is there a corresponding setting with the modeller to indicate the new path for the db ?”

    Unfortunately, we don’t have Oracle.

    regards

    jsa

    1. thatjeffsmith Post
      Author

      Your design will have a .dmd file. There will also be a directory under that, similar name. Zip that up, send it over. Have your colleague open the dmd file in the modeler.

  169. I am new to running the Database Diff Report utility. I am a developer and I have not been able to run this without errors.

    I have cajoled my (so far) cooperative DBA to run it for me, but who knows if I can count on that. Besides, I am supposed to be able to run it I thought!

    Question: Is there simple documentation ANYWHERE that will help me get the rights I need to run Database Diff? Why is this so hard?

    I have looked and searched your blog and with google, but I cannot find the answer.

    Thank you,
    john3

    1. thatjeffsmith Post
      Author
  170. I would like to export data as SQL Inserts. However, my text fields include line feeds and other special characters. Can SQL Developer be configured to use the quote “q” operator in the export.

    For instance consider:

    select q'(hello
    this is complicated ‘
    text that I would like to
    see handled
    right )’ txt
    from dual;

    How can this data be exported (understanding that we would never import into dual) in a way that might automatically work by specifying that the quote operator should be used for textual columns?

  171. Hi Jeff – I have a question about using Editions in SQLDev. We’re an EBS 12.2 environment so the application leverages edition based redefinition (EBR) for patching. Sometimes it is useful to be able to look into the new, unimplemented edition to see what version of code is delivered, for example. When I expand editions in SQLDev, I can right-click and I see options to “Open”, “Set Current”, “Create” and “Drop”. I know what the last two do, and open just expands the tree. Set Current scares me; what I really want to do is have it effectively issue, for example, “ALTER SESSION SET EDITION = V_20141127_2225;” so I can browse the alternate edition schemas. The documentation is less than clear to me, any insight?

    1. thatjeffsmith Post
      Author
      1. Good call. I mustered up the courage to click the menu item in a DEV instance and got the prompts/sql window. It shows ALTER SESSION SET EDITION=”V_20141127_2225″ which is exactly what I was hoping for. Thanks!

        1. thatjeffsmith Post
          Author
  172. Hi Jeff
    I install and done all the set-up for Oracle Database 12c Release 1 and are able to login using //localhost:5501/em with my Id and password but why when I use Oracle SQL Developer to connect Oracle Database 12c but fail with error message (Status : Failure -Test failed: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor). Am I miss anything?

    1. thatjeffsmith Post
      Author

      probably b/c you can’t use SID – if it’s a pluggable db, you have to use the Service – unless you’re actually trying to connect to the container database

      also i’m not sure if em express goes into the db via the listener, so it’s not guaranteed your listener is configured correctly, although it’d be really weird if it wasn’t

      a better test would be to use the same connect string in SQL*Plus to login

    1. thatjeffsmith Post
      Author
  173. Is it possible to connect to a SQLite DB with SQL Developer? If so, how? (On Win 10 Home, 64-bit), and latest production SQL Developer).

    1. thatjeffsmith Post
      Author
  174. Hello Jeff, I just recently upgraded our environment from SQL Dev 3.2 to 4.1.3. Ever since I have had a couple of users having an issue where if they run a script to get their data (without exporting it). After they have their data they will then right click and select the Export function (to a csv). On the old version of SQL Developer it would generally take a couple minutes. On the newer version this is now taking the 20+ minutes to export 581 rows.

    Any ideas?

    1. thatjeffsmith Post
      Author
  175. Hi Jeff,

    I am using /*csv*/ in my select statements to run a script with CSV output. I find that when I do this, I then cannot comment out sections that have /*csv*/ in it. For instance the below select statement is not commented out as it would be if not for the /*csv*/. I am not using SQL plus so cannot use the set sqlformat. I want to get away from using — on each line as there is a lot to maintain in this script. Any thoughts on how it can be done?

    /*
    Select /*csv*/ field_name
    from table_name
    */

    1. Hi Steve,

      Checking in to see if you have a response to this. If there is no good solution, no problem. Thanks for reviewing!

      1. in SD highlight the code and tap ctrl-/
        This will toggle single line comments.

        –select /*csv*/
        –list,of_cols
        –from dual ;

        TR

  176. I am running scripts in SQL Developer and they are completing successfully, but Task Progress continues to show them as running until I kill the process. Can you point me in the right direction to solve this?

    Thanks!

    1. thatjeffsmith Post
      Author

      have you confirmed they’re still running on the server?

      what are the scripts doing? anything that would require a ton of undo/temp type work?

      have you tried a thick connection?

      1. Confirmed that the scripts are no longer running on the server. Haven’t tried the thick connection yet. I don’t have the thick client installed on my pc yet. These scripts are doing large selects from one table and inserting into another. We have a few billion rows of data and are trying to purge a lot of it so we’re doing CTAS. I’m committing every few million rows to keep temp down to a few gig. My DBA has confirmed that there aren’t any timeouts on the db. We had networking to a trace between the pc and db server but they didn’t see anything.

  177. Hi Jeff,
    Great site!

    I’m using SqlDeveloper to copy tables from Sybase to Oracle(Copy To Oracle).
    Working fine – but are there any logs created during the copy and where can I find these?

    Thanks!

    /jio

    1. thatjeffsmith Post
      Author
  178. It seems like breakpoints with conditions can only be used with:

    numbers e.g. l_val = 5
    nulls e.g. l_val is not null

    However, strings do not seem to work nor do some other conditions that I would normally use in a where statement, like:

    l_cmd = ‘xyz’ – there are no error messages when this is used but it does not match
    l_cmd = “xyz” – there is an error message when I use this syntax.
    l_cmd in (‘xyz’,’zyx’) – also error message complaining about syntax

    The documentation in SQL developer says: “Condition: A SQL condition (WHERE clause without the WHERE keyword) restricting when the breakpoint occurs.”, which I read to be, anything that you can use in a where would work.

    What exactly is supported?:
    1) Are strings supported?
    2) Are tables supported? my_table.some_val = ‘bar’?
    3) What else is/is not?

    and for the previous question what syntax must be used?

    I’m using this today, but this seems to be a problem since at least a year or 2 ago (I haven’t experimented with an old release)

    About
    —–

    Oracle SQL Developer 4.1.0.19
    Version 4.1.0.19
    Build MAIN-19.07

    IDE Version: 12.2.1.0.42.150416.1320
    Product ID: oracle.sqldeveloper
    Product Version: 12.2.0.19.07

    Version
    ——-

    Component Version
    ========= =======
    Oracle IDE 4.1.0.19.07
    Java(TM) Platform 1.8.0_11
    Versioning Support 4.1.0.19.07

    1. thatjeffsmith Post
      Author
      1. Thanks for the reply. I looked at the jdwp, dbms_debug etc. Could you give me a hint what to search for?

        A very observant person has discovered that for string conditional breaks the variable must be UPPER case, but for integers it does not matter: L_STR = ‘xyz’ , l_num = 22 , L_NUM = 22. l_str = ‘xyz’ is not valid.

        I have not been able to discover how to do conditional breaks for tables.

  179. Is it possible to run in SQL Developer a script like in SQLPlus in which I connect in different Schemas to perform some action like:

    connect system/blabla@test
    CREATE USER testuser PROFILE DEFAULT IDENTIFIED BY testpsw
    DEFAULT TABLESPACE PODATKI TEMPORARY TABLESPACE TEMP1 ACCOUNT UNLOCK;
    GRANT CONNECT TO testuser;
    GRANT RESOURCE TO testuser;

    connect sys/blublu@test as sysdba
    Grant execute on utl_mail to testuser;

    connect katas/blibli@test
    grant select, update on parc_del to testuser;
    grant execute on pg_vks to testuser;

    connect stavbe/bbleble@test
    grant select on stavbe_gr to testuser;
    grant select on stavbe.STA_PARCELE to testuser;

    connect testuser/testpsw@test
    create table pdmid_seznam (pd_mid number(12), sifko number(4), parcela varchar2(10), opis varchar2(200));
    create table gu_parcele (pc_mid number(12), sifko number(4), parcela varchar2(10), gu number(3));
    create table temp (vsebina varchar2(200));

    1. thatjeffsmith Post
      Author
  180. I have the following code, and using sql developer Version 4.1.3.20,

    set linesize 180
    set pagesize 999

    col course_fee for 999,990.90 heading “Fee”
    col course_id for a35
    col course_institution_name for a5 heading “Type”
    col display_title for a10 heading “Lesson”
    col instructor for a45
    col student for a22
    col title for a10
    col user_student_id for a15 heading “PID”

    break on instructor skip page on course_institution_name on course_id on student
    compute sum of course_fee on instructor

    select
    instructor_lastname || ‘, ‘ || instructor_firstname || ‘(‘ || instructor_email || ‘)’ instructor
    , course_id
    , student_lastname || ‘, ‘ || student_firstname student
    , user_student_id
    , title, display_title
    , attempt_date
    , course_fee
    , course_institution_name

    But “break on instructor skip page on course_institution_name on course_id on student
    compute sum of course_fee on instructor” does not work. It does not break on instructor.

    How do I format break on …….

    Thanks for your help in advance

  181. Hi Jeff

    I’ve started using Data Modeler with Subversion option. I think it’s really nice tool, but I have some difficulties with using Pending Changes tab. I’ve seen in your posts that changes you make to model are grouped nicely in a model/table in which it was made. In my instance I can only see XML files, so basically I have to go into each of them and have a look at all XML property level (?). Do you maybe know what I’m doing wrong?

  182. How to add more KEYWORDS to the lexer? For example “NVL”. I think that I’ve been thru all the option and do use the “PL/SQL Syntax Colors” and other “Code Editor” options, but I cannot find where to add additional keywords.

    1. thatjeffsmith Post
      Author
      1. Yes, you’re right. At a customer site, when using SQL Developer on their host, I could have sworn that NVL() wasn’t highlighted and so I posted here to ask. Yet right now on my host, it is highlighted. I think both installs are using the same version 4.1.3.20. Hmmm … I’ll have to double check when I’m back on their site in a couple of days. Sorry to bother you.

        1. So I’m at the customer site today, using their install of 4.1.3.20 and NVL() isn’t highlighted as it is in my other install. Both are 64-bit installs. Nor is the functions NVL2() for that matter. Yet other functions such as MIN and MAX are highlighted — meaning, they’re blue.

          Any suggestions on where I could look to identify the change in configuration?

          Thank you.

          1. thatjeffsmith Post
            Author
  183. I’ve noticed the last two versions of SQL*Developer (current version 4.1.3.20) display a Logging message (in a logging page tab) upon startup: org.openide.util.RequestProcessor$Processor, Error in RequestProcessor oracle.ideimpl.usages.UsagesTrackerImpl$3. I get similar messages simply by clicking in an editor window (if connected to a database). While these don’t affect my work, is there any way to suppress these? If I can’t fix the issue that causes this and there’s no mechanism to “report the problem to Oracle”, it seems as though these shouldn’t even be displayed.

    1. thatjeffsmith Post
      Author

      close the log panel? also assuming you’re not running in debug mode

      looks like it’s coming from where we send up usage info, where you have agreed to share that so we can see which features are getting touched more than others to determine where to invest R&D in the tool

      1. Thanks for the quick reply! Yes, I did check the “Allow automated usage reporting to Oracle” message. And while I do close the log panel (and I’m not running in debug mode), it just keeps re-appearing with new messages. If someone at Oracle is actually analyzing this info, then I’m happy to hear that.

        1. thatjeffsmith Post
          Author
  184. Jeff,

    Enjoy the blog and getting a lot of productivity out of SQL Developer. I am using the cart feature and it really helps moving code for Dev to QA to Prod. It does have one feature that I’d like to have more control over. When I export a cart a second or more times the file name adds a version number. It would be nice to have an option to overwrite the file instead of adding a version number to the name.

    Could this be possible, or maybe it exists and I don’t know where to look.

    Cheers
    TR

    1. On that topic, I’d like the option to also overwrite when using the database export wizard also, which appears to be the same engine that the cart exports are using. Would also be useful if a file name and path could be saved with the cart also so the next time we just hit the cart export button and forget about it. If different locations are required, a user could overwrite the cart ‘default’ or define additional carts.

    1. thatjeffsmith Post
      Author
  185. I am an old CASE user and want to use the same rules to transform logical to physical models. i.e. pluralize entity for tables, use the short name for keys (fk,uk,pk). Is there a place to do this (or even maybe a library). The docs are very weak on this.

    1. thatjeffsmith Post
      Author

      I’m not an old CASE user, so I apologize in advance.

      For plurals, you you want to use the glossary feature, and then look at the transformation scripting.

      Heli and Kent both have blog posts on how to do this I think.

      Here’s a tutorial on the Glossary feature.

    1. thatjeffsmith Post
      Author

      yeah, that’s bogus. bug.

      unless i’m smoking crack, I’m talking to the dev now to confirm one way or the other.

      as a workaround you can end the anon block with a ‘.’ and then execute the buffer with a ‘/’

    2. thatjeffsmith Post
      Author