47 responses

  1. Jarek
    January 7, 2014

    Hi. New version of the Oracle SQL Developer is beautiful, but it seems to me that the query execute more slowly. I ran the same query on a Oracle SQL Developer 4.0.0.13 and Oracle SQL Developer 3.2.20.09. In the first case the query is finished after 63.943, in the second case after 56.485 seconds. I repeated this exercise a few times and always older version was faster. Do you have any thoughts on this ? Thanks.

    • thatjeffsmith
      thatjeffsmith
      January 7, 2014

      What does AutoTrace show for the query in 4.0?

      How many rows does the query return?

      HOw long does it take to run in SQL*Plus?

  2. Rajesh
    January 7, 2014

    Jeff,

    Thanks for the post, but what do we gain in sql-developer switching from JDBC driver to OCI? I saw the below, but I don’t get it very clear.

    If you’re too lazy to go read the above post, here it is in a nutshell:
     JDBC doesn’t do everything that OCI can
     OCI does a better job at reliably cancelling queries
     OCI has better support for advanced data types including XML

    • thatjeffsmith
      thatjeffsmith
      January 7, 2014

      The OCI bits have more listening points in the database code for cancel query requests. When you ask to cancel a query, you ask the database. The database has to see that request and respond to it, and then get around to canceling said query. If the query is running over JDBC, the database has fewer ‘listen’ points – I’m sure I’m mucking this up completely and the database developers would have kittens if they read this, but that’s as about as simple as I can put in in layman’s terms. So if you use OCI Thick connections vs straight-up JDBC connections, canceling queries in SQL Developer is more robust.

      Additionally, JDBC has poorer support for XMLDB than OCI. And, JDBC doesn’t support the ability to reset passwords when they expire on connection requests. So, having OCI Thick available is preferable. There are a few more edge cases, but the cancel query thing is probably enough for most people to get thinking about this.

  3. Erik van Roon
    January 9, 2014

    Jeff,

    I’m having a bit of a challenge with this.
    I’m trying to set these preferences.
    But when I hit the ‘Ok’ button I do not get ‘your’ path message which you tell me I can ignore.
    However I get a ‘Validation failed’ screen with message “The client home C:\Oracle\client112_32\bin is not on the PATH”
    It just has an ‘Ok’ button, and clicking it just returns me to the preferences screen.

    I know you say “Note we can only look at the first item in the path”, so I was prepared for a message to ignore.
    But I can’t ignore it.
    Moving the entry in the path to be the first item (and rebooting just to be sure) doesn’t help.

    Any ideas?

    Same issue on 2 machines:
    SQL Developer : 4.0.0.13.80 (both)
    Oracle client : 11.2.0.1.0 / 12.1.0.1.0
    Oracle database : 11.2.0.3.0 / 12.1.0.1.0
    OS : Win 7 Enterprise SP1 64bit / Win 7 Home Premium SP1 64bit

    • thatjeffsmith
      thatjeffsmith
      January 9, 2014

      Is that an instant client…it doesn’t look like one.

      Are you running a 32 or 64 bit JDK?

      • Erik van Roon
        January 9, 2014

        Nope it’s not an instant client, it’s a standard client.
        When selecting the location I choose ‘Oracle home’ in stead of ‘Instant client’.

        I’m running 64 bit JDK 7u40

      • thatjeffsmith
        thatjeffsmith
        January 9, 2014

        $ORACLE_HOME directory isn’t the \bin directory though, it’s the parent directory of that.

      • thatjeffsmith
        thatjeffsmith
        January 9, 2014

        oh and try using uppercase BIN (in the path) – it’s a bug where we’re doing something weird with the validation check in the UI

      • Jim
        January 9, 2014

        hi Jeff,
        I’m having the same problem as Erik.

        I’m on a 32bit windows machine.

        I just downloaded Instant Client from http://www.oracle.com/technetwork/topics/winsoft-085727.html

        and set the path in SQL Developer. It’s showing:
        Instant Client: file:/C:/Oracle/product/instantclient_12_1/

        But hitting “OK” on the Preferences gives: “The client home C:\Oracle\product\instantclient_12_1 is not on the PATH”.

        I’ve added the same directory to the PATH system variable in System Properties.

        I’m able to start SQLPlus from the Instant Client no problem:
        C:\>
        C:\>cd C:\Oracle\product\instantclient_12_1
        C:\Oracle\product\instantclient_12_1>.\sqlplus
        SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 9 16:01:16 2014
        Copyright (c) 1982, 2013, Oracle. All rights reserved.
        Enter user-name:

  4. Erik van Roon
    January 9, 2014

    mig$alg.haal_verdeling: gewijzigde out parameters

    I know, but the post says it’s looking for these oci* files. And they are in the bin directory.

    As far as I know OORACLE_HOME is not any directory within the client installation tree, its the praent directory of the database installation. Right?
    So, it did surprise me there were only 2 options: ‘instant client’ which I’m not using or Oracle Home, which to my knowledge should point to the database installation, not the client.

    However, choosing either option and using either the clients bin directory or it’s parent, all give me the same result.

    I’m beginning to fear that it doesn’t work with a standard client?
    That it has to be an instant client or the database installation directory?
    Correct?

    • Brad Simmons
      January 20, 2014

      My full client install did not have the oci.sym file. (My foggy memory is suggesting there was an issue with this on some versions of the 11.2 client installs….) Anyways, I made sure the instant client directory (not including the ./bin directory) is in the PATH and then specified the instant client install & directory within SD4. OCI is alive!

      • Xenofon
        April 10, 2014

        Hi Jeff,
        I have the same issue, as Brad. I do not have the oci.sym file. What is the issue?
        My full oracle client version seems to be
        11.2.0.3.0 (From the header comment of SQL*Plus)

        Should suffice right?
        Thanks,
        Xenofon

      • thatjeffsmith
        thatjeffsmith
        April 10, 2014

        Not sure, but SQL*Plus doesn’t really come into play. We need the oci binaries/drivers/libraries in order to make the connection over JDBC.

        Those files SHOULD be in the full and instant clients for 11.2.0.3 and 11.2.0.4

  5. Erik van Roon
    January 9, 2014

    Uppercasing the bin doesn’t work.
    I hit ‘configure’ and the ‘configure oracle client’ window opens.
    I change ‘bin’ to ‘BIN’ and hit ‘OK’.
    I return to the preferences window and it’s changed back to ‘bin’

    • Erik van Roon
      January 9, 2014

      Oops. now I see that you say use uppercase in the path.
      I’ll change the path and see what happens…

      • Erik van Roon
        January 9, 2014

        no luck :-(

  6. thatjeffsmith
    thatjeffsmith
    January 9, 2014

    We look for the ‘bin’ directory UNDER the client path specified. I said we look for those files when doing an Instant Client. For Full Client installs, pick the root/home folder – not the BIN or bin.

  7. Erik van Roon
    January 9, 2014

    Sorry, hate to keep bugging you.
    But, like I said, when I use the ‘C:\Oracle\client112_32′ directory the same problem exists.
    It even complains about the same directory (including bin) not being in the path.
    I just tried if adding the parent directory ‘C:\Oracle\client112_32′ to the path would make a difference, but nope.

  8. thatjeffsmith
    thatjeffsmith
    January 9, 2014

    I think it’s time to go to the Forums or Support for help…but if you have the directory set as you say in the preferences, you should be good. The Oracle Installer updates the PATH for you, so you shouldn’t need to touch the PATH.

  9. Erik van Roon
    January 9, 2014

    ok, thanks!

  10. Arpod
    January 10, 2014

    Just a note that it works just fine under Linux distros – all I had to do is download the zipped basic instant client (http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html), unpack it and add that dir to /etc/ld.conf.d – basically, update PATH. Then it just worked. Another note is that I used 11.2.0.3.0, if that ever becomes relevant :3

    • thatjeffsmith
      thatjeffsmith
      January 10, 2014

      Thanks for sharing Arpod!

  11. Leandro
    January 10, 2014

    Hi there, Jeff.
    I know this is not the place but I did not find the answer for my question anywhere so I thought that maybe you could help me out.
    I’m using sqldeveloper there’s long time and now I’m struggling with this problem: sqldeveloper 3.2 does not statement’s send the single line comments to the server. Seems that it removes anything after the “–” before sending it to the server itself. For exemple, I issue:
    select 1
    — comment
    from dual;
    When I look in the server, the statement I see is just
    select 1 from dual

    I’m currently working in a system that logs every single DML and the comments explaining the statement must be stored too, so I’m not able to use sqldeveloper since it’s just skipping the comments.
    I looked in every single option but I couldn’t find anything related to it. Is there an option somewhere in a config file or that’s a hardcoded behavior?

    • thatjeffsmith
      thatjeffsmith
      January 10, 2014

      I see what you’re saying. Will see if we’re doing that on purpose or if it’s a bug.

    • thatjeffsmith
      thatjeffsmith
      January 10, 2014

      So we’re doing it on purpose, and it’s for a good reason – not that I expect you to care really, you need your comments!

      But, I have a decent work-around for you, give it a try.

      Instead of

      select *
      -- doing this because I love my data
      from dual;
      

      Do this

      select *
      /* doing this because I love my data */
      from dual;
      

      And if you’re super curious as to why we’re doing it, when we send the statement via jdbc to the database, the entire statement gets concatenated to a single line. The ‘–‘ serves to wipe out the rest of the query when you do that.

      • Leandro
        January 11, 2014

        Hmmm interesting, that explains why a hundred lines well indented query is so lame on enterprise manager.
        And yes, I did some tests with multi-line comments before and I was able to track them in the server, but unfortunately its not an option for me since the system I’m working with logs the dml with triggers that looks specifically for “–“; comments with /* */ are not accepted.
        Well, seems that I will have to put sqldeveloper aside, at least for production interventions.
        Just a curiosity, you said that it’s a jdbc issue, single line comments must be removed because the statement is concatenated in a single, right? So I presume that every jdbc statement has the same problem, not only in SQLDeveloper?

      • thatjeffsmith
        thatjeffsmith
        January 12, 2014

        Leandro,

        Any chance your security folks would consider amending their trigger code to accept the other commenting style?

        In the mean-time, we’re looking to see if improvements in the JDBC layer can make this SQLDev behavior, ‘go away.’

  12. Leandro
    January 12, 2014

    In fact I’m part of the security folks now, this log system was developped by the dba team before I arrive, I don’t know exactly how it’s implemented but I know it uses triggers to check for a specific comment in every single DML or DDL fired in production environment. They had several meetings with the developers in order to define a standard for this and a request for change is out of question; there’s no use to a major change in a important part of the system just because one person.
    Thinking about it blew my mind now. Why the trigger is not blocking my commands if they reach the server without the propper comment?
    I mean, if I purposely ommit the comment the trigger get me an error, like it should do. If I put the comment I’m able to modify the database but the server itself did not get the comment because it was wiped out the command before getting in the server.
    In the log table I see my commands issued and none of them have the comment. Now I’m confused when are the triggers being fired. Seems that the check it in my own machine, before SQLDeveloper removes the comment.
    This is getting pretty weird, gotta investigate this tomorrow.

    • thatjeffsmith
      thatjeffsmith
      January 13, 2014

      I’m sad to hear there’s only one person there using SQL Developer.

      I’m hoping you can continue to use it to do your development and your queries – perhaps build and test your DML there and copy to SQL*Plus for production?

      I agree that changing your rules for a single user is a bit much. I’ll try to let you know if we make an update here that will satisfy your auditing setup.

  13. Leandro
    January 16, 2014

    Hey Jeff, hi again!

    I’d like to inform you that I found a workaround for my problem. Indeed you were right, using multi-line comments helped me out, just like this:

    select *
    /* –doing this because I love my data */
    from dual;

    It’s kinda bizarre to me to comment inside a comment but turns out that the trigger accepted the inner comment in the end. Seems that I will be able to keep using SQLDeveloper.
    YAY! :D

    • thatjeffsmith
      thatjeffsmith
      January 16, 2014

      Necessity is the mother of invention :) Glad you got something working for you!

  14. Marcel
    February 19, 2014

    Hi Jeff, I just stumbled over your Site, searching for a solution for our strange “Change Password” Problem. We have used several different clients (Instant Clients, Fat Clients) in order to Change Passwords on 11.2.0.3 database in SQLDeveloper, but we had no luck, some combinations only let us change the password when we were connected, other behaviours were, that we were always getting the message “wrong username/password” while changing. Finally with Instant Client 12 we are at least able to change the password in general (when not connected to the database, what I think is the right behaviour). But nevertheless we are not able to change an expired password. Then we always get the message “Password has expired”…

  15. Nune
    March 16, 2014

    Hi,

    I am trying to disable the autocheck for updates in Oracle SQL Developer 4.0.0.13, but I am not able to find it in preferences.

    Please could you help me on where I can find this option?

    Additionally please let me know if there is any other option which will enable the quick start of Oracle SQL Developer?

    Thanks,
    Nune

    • thatjeffsmith
      thatjeffsmith
      March 16, 2014

      Help > Check for Updates

      Up top, uncheck ‘Automatically check…’

      How long is it taking you to startup the tool?

      What version of the JDK are you using?

      How large is your SQL History list?

  16. Mathias
    April 25, 2014

    I really like using OCI in SQL Developer. I however had to get it to work in Debian linux-distro today. Using instant client I ran into issues with it not working and getting the typical error message about LD_LIBRARY_PATH. After a bunch of trial and error a colleague suggested that my problem was in lacking the libaio1 library. Dropping in it via apt-get it started working like magic.

    Just leaving a note about this here for other lost souls that need some help, or for the next time I try to find this via google. Trusting my leaking memory is a bad practice these days.

    • thatjeffsmith
      thatjeffsmith
      April 25, 2014

      Thanks Mathias!

  17. BlackWoxs
    April 28, 2014

    Using the OCI/Thick driver (Client Shared Library 64-bit – 11.2.0.3.0) under OSX does not appear to support the use of XMLELEMENT. If I use XMLELEMENT in SQLDeveloepr 4.01.14 connected via OCI the return is rows of null, change back to the standard JDBC driver and correct xml elements are returned – any suggestions/comments?

    • thatjeffsmith
      thatjeffsmith
      April 28, 2014

      You could try a 11.2.0.4 or 12c client…but what’s the version of the database, and what happens if you query with SQL*Plus? I know there’s an issue or two with XML DB that was patched in 11.2.0.3/12 in the database.

      • BlackWoxs
        April 28, 2014

        Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

        If run with SQL*Plus in the same folder as the instant client which is providing the OCI driver for SQL Developer the command works fine:

        SQL> select xmlelement(“Brand”,
        2 xmlelement(“Name”, brand_name),
        3 xmlelement(“Type”, brand_type))
        4 from
        5 brand;

        XMLELEMENT(“BRAND”,XMLELEMENT(“NAME”,BRAND_NAME),XMLELEMENT(“TYPE”,BRAND_TYPE))
        ——————————————————————————–
        FORESTERS BESTVALUE
        BINDER PRIMEPREMIUM
        STUTTENFURSTCONTRACTOR
        LE MODEPREMIUM
        HOME COMFORTCONTRACTOR
        OLDE TYME QUALITYCONTRACTOR ….. etc

        In SQL Developer with the OCI driver selected:

        > select xmlelement(“Brand”,
        xmlelement(“Name”, brand_name),
        xmlelement(“Type”, brand_type))
        FROM
        brand
        XMLELEMENT(“BRAND”,XMLELEMENT(“NAME”,BRAND_NAME),XMLELEMENT(“TYPE”,BRAND_TYPE))
        ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————–

        13 rows selected

        It’s not a “biggie” just something to watch out for. When I can get a spare moment I’ll try a Instant Client update as you suggest.

      • thatjeffsmith
        thatjeffsmith
        April 29, 2014

        And you might want to open an SR as well – they can give you much better exact information on the version conflicts and patches to get the XML stuff all happy.

  18. Bill Bovard
    July 8, 2014

    Can SQL Developer support “break” statements? I have some queries that look nice in other tools but ugly, and no summation lines, in SQL Developer 3.0.
    Thanks,
    Bill

    • thatjeffsmith
      thatjeffsmith
      July 8, 2014

      we don’t support ‘Break’ today – but our goal is to support 100% of the SQL*Plus syntax

  19. Daniel
    August 11, 2014

    I got the following error:
    “Testing the Instant Client located at D:\desenvolvimento\programas\sqldeveloper\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.1.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 PATH. Check it to verify that
    the expected native library directory D:\desenvolvimento\programas\sqldeveloper\instantclient_12_1 is present and precedes any other client installations.”

    I am running on Win7 64bits on 32bits JDK.
    Oracle SQL Developer 4.0.2.15
    Instant client for Win 64bits 12.1.0

    • thatjeffsmith
      thatjeffsmith
      August 11, 2014

      Check your path to make sure the instant client drivers can be found.

  20. Job
    November 10, 2014

    Hi Jeff,
    Please help. I am try to set my Oracle Home but I get the error as shown in the below. Appreciate your help and I do apologize for this trouble. I’m kind a newbie in SQL Developer. I am using Oracle SQL Developer 4 anyway.

    Validation Failed
    The client home C:\oracle\product\11.2_64\bin is not on the PATH.

    • thatjeffsmith
      thatjeffsmith
      November 11, 2014

      Sounds like you added an instant client. For an instant client to be usable, the OS has to be able to find the binaries. This is done by adding that path to the OS Path environment variable. Go to your system preferences, find that variable, and put the client home directory FIRST in that list. Close and re-open SQL Developer and try again.

Leave a Reply

 

 

 

Back to top
mobile desktop