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

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

    I get error

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

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

  2. Hi Jeff,

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

    If so, how?

    Thanks,

    Bill

    1. thatjeffsmith Post
      Author
  3. Jeff,

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

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

    any suggestion is appreciable.

    1. thatjeffsmith Post
      Author

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

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

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

        Thanks Jeff.

        1. thatjeffsmith Post
          Author

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

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

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

  4. Hi

    Could this be made a feature in SQl Dev:

    Datamodel like view on a view

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

    really usefull when troubleshooting

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

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

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

        Ideal when debugging

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

    1. thatjeffsmith Post
      Author

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

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

        sdcli migration -actions=capture -conn=sqlserver

        Driver Imported to sql developer – jtds-1.2.jar

        1. thatjeffsmith Post
          Author

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

          Also, you will get better migration with jtds 1.3

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

    1. thatjeffsmith Post
      Author
    2. Hi Jeff,

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

  7. Hey Jeff,

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

    1. thatjeffsmith Post
      Author

      I took that post down, but maybe Google could still bring it up. It’s nuked from orbit now, so everything should be good to go now. If not, [email protected]

  8. Jeff,

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

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

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

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

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

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

      1. thatjeffsmith Post
        Author

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

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

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

          1. thatjeffsmith Post
            Author
          1. thatjeffsmith Post
            Author

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

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

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

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

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

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

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

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

  10. Hi there,

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

    Thanks,
    Bud.

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

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

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

    Thoughts?

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

    1. thatjeffsmith Post
      Author
  12. Hi Jeff,

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

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

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

    Thank you

    1. thatjeffsmith Post
      Author

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

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

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

    1. thatjeffsmith Post
      Author

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

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

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

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

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

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

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

    1. thatjeffsmith Post
      Author

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

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

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

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

        Signed,
        I might be a nerd.

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

    For example this

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

    with the cursor before the semicolon selects all.

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

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

    1. thatjeffsmith Post
      Author
  16. Hi Jeff,

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

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

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

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

    SQL> where e.deptno = d.deptno

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

    SQL> GROUP BY d.dname

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

    SQL>
    ORDER BY d.dname;

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

    Any inputs/workarounds to fix this?

    Regards
    Ahmed

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

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

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

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

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

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

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

            Thanks Rudkin & Smith for your support.
            Ahmed

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

    conn !my_connection_name_here

    I use this constantly on Microsoft SSMS using the syntax

    :connect server_name_here

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

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

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

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

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

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

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

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

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

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

            is there any other way to create the repository.

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

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

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

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

  21. hi Jeff
    We are using SQL Developer version 4.1.3.20

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

    TIA

    1. thatjeffsmith Post
      Author

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

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

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

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

    1. thatjeffsmith Post
      Author

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

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

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

    1. thatjeffsmith Post
      Author

      With SQL Developer CLOSED, try this

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

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

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

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

  24. Hi Jeff,

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

    Here is the support ticket:

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

    Here is the response from Oracle support:

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

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

    Thanks,
    Brian

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
  25. Hi Jeff

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

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

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

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


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

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

    $ /opt/sqldeveloper-3.2.20.09.87/sqldeveloper.sh

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

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

    Additionally (as root):

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

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

    Thanks in advance for your time

    Greg

    1. thatjeffsmith Post
      Author

      Yes, you need a JDK.

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

      1. Hi Jeff and many thanks for your answer.

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

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


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

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

        $ /opt/sqldeveloper-3.2.20.09.87/sqldeveloper.sh

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

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

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

        Thanks once more
        Greg

        1. thatjeffsmith Post
          Author

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

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

  26. Hi Jeff

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

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

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

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

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

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

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

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

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

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

    1. thatjeffsmith Post
      Author
      1. HI Jeff,

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

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

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

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

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

    1. thatjeffsmith Post
      Author

      Blank lines?

      There are options for how the DDL is shaped.

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

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

        .. is returning without them..

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

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

          SQL> ddl uselessproc;
          SQL> format buffer

          Not prefect but better the eating blank lines.

  30. Hi Jeff

    Thanks for your site. it is really useful.

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

    Thanks in advance
    Regards
    Vadi
    Bengaluru, India

  31. Hi Jeff,

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

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

    …or with TNS:

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

    In both cases we get the following error message:

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

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

    Jan

  32. Jeff,

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

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

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

    Connor

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

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

    1. thatjeffsmith Post
      Author
  34. Hi Jeff,

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

    thank you

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

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

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

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

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

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

    1. thatjeffsmith Post
      Author

      Are you writing to XLSX?

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

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

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

        1. thatjeffsmith Post
          Author

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

          Have you tried increasing the JVM max heap size?

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

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

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

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

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

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

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

            thank you for your assistance as always!

          2. thatjeffsmith Post
            Author

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

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

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

            I talk about this in detail here.

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

  36. Hi Jeff,

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

  37. Hi Jeff,

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

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

    Am I overlooking something silly?

    Thanks

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

        1. thatjeffsmith Post
          Author
  38. Hi Jeff,

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

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

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

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

    Am I missing something?

    Thanks.
    Norm

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

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

        1. thatjeffsmith Post
          Author

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

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

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

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

            SQL> select sessiontimezone from dual;

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

            SQL> select time_stamp_ltz from date_table;

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

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

            Session altered.

            SQL> select time_stamp_ltz from date_table;

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

            SQL>

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

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

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

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

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

            Session altered.

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

            VIENS @ patd >disc

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

            Session altered.

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

            VIENS @ patd >

            Note the sequence of events:

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

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

            Thanks for listening.

            Norm

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

            I believe I’m using a thick client:

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

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

            Norm

          5. thatjeffsmith Post
            Author

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

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

            I would open a SR with MOS.

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

            Norm

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

          8. thatjeffsmith Post
            Author

            I have the answer.

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

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

    1. thatjeffsmith Post
      Author

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

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

  40. Hi Sir,

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

    Can you please help me how to do this?

    Regards
    G. Srinivasa Rao

    1. thatjeffsmith Post
      Author
  41. Hi Jeff,

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

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

    Thanks a lot,
    Vlad

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

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

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

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

  43. Hi, Jeff

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

    Thnks.

    Mike R.

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

    Thanks!

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

    set sqlformat delimited ; =” ”

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

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

    returns

    =””1″;=”2″

    instead of

    =”1″;=”2″

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

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

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

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

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

    Thanks so much!
    Alex

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

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

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

    Is this a bug or a feature?

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

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

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

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

  49. Hi Jeff,

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

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

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

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

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

  54. 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
  55. 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
  56. 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
  57. 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?

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

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

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

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

  63. 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
  64. 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
  65. 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.

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

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

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

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

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

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

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

    *Specific questions*:

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

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

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

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

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

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

    [email protected]grateTaskException: 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
  77. 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.

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

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

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

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

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

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

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

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

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

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

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

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

    1. I’m having the same issue with 4.1.5 and 4.2.

      The connection setup works fine but I can’t see the schema tree and when I try “Schema Browser” I get an error.

      1. thatjeffsmith Post
        Author
        1. I got this error the first time I attempted “Schema Browser” on that connection:

          java.lang.ClassCastException: oracle.dbtools.raptor.schemabrowser.SBController cannot be cast to oracle.ide.controller.TriggerController

          Now it’s grayed-out and I can’t select it. I read in another post that the “Schema Browser” tab only works for Oracle… anyway, my main issue is not having the “tree” on the left pane.

          Do you know of a trick to bring it up? I can run a query to pg_catalog.pg_tables and get the table listing but that’s a little inconvenient.

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

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

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

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

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

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

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

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

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

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

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

  115. 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
  116. 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 (

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

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

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

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

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

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

  129. 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
  130. 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
  131. 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
  132. 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
  133. 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

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

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

  136. 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
  137. 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
  138. 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
  139. 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 [email protected]/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.

  140. 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
  141. 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
  142. 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
  143. 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?

  144. 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
  145. 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
  146. 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
  147. 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
  148. 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
  149. 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?

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

  151. 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
  152. 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
  153. 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
  154. 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
  155. 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
  156. 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

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

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

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

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

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

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