Oracle SQL Developer 4 and the Oracle Client

thatjeffsmith SQL Developer 67 Comments

Tell Others About This Story:

You don’t need to have an Oracle Client installed on your machine to use Oracle SQL Developer. Everything you need to connect to Oracle Database is provided by the JDBC driver included with the download. So you can be up and running in usually less than 5 minutes.

But if you DO happen to have a Client on your machine, you’ll be happier.

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

So, if you’re on-board with the idea of connecting up your Oracle Client with SQL Developer, let’s talk about how to get that going.

Prior to version 4, you would tell SQL Developer to use the ‘OCI/Thick driver’ – by the way, OCI stands for ‘Oracle Client Interface.’

Yes or No, basically

Yes or No, basically

The ‘problem’ here is that many of you have more than one Client on your machine. So not being able to explicitly tell SQL Developer WHICH client to use was problematic.

So in version 4, we’ve enhanced the preferences to allow you to tell us exactly what client to use.

Now you can say what client you want us to use

Now you can say what client you want us to use

Note the actual PATH I have here is:
“Instant Client: file:/C:/Program Files/Oracle/11203_x64_InstantClient/instantclient_11_2/”

You can put the Instant Client anywhere on your machine, but we need the directory location of where we can find these files:

We get really picky, so you need to get this RIGHT.

We get really picky, so you need to get this RIGHT.

The Preferences

Use Oracle Client
This tells SQL Developer WHERE your client is. It will then use this location to attempt to load the OCI stuff when doing things like a Password Reset on an expired connection. It won’t cause SQL Developer to use the OCI/Thick driver for connections though.

Use OCI Thick Driver
This DOES tell SQL Developer to make OCI/Thick connections to your Oracle database. It will use the client specified in the previous preference.

SQL Developer will attempt to confirm that your Client is available once you click ‘OK.’ If it’s an Instant Client, it will check the OS Path to make sure it’s there. Note we can only look at the first item in the path, so you may get a false positive, in which case you can ignore by clicking ‘Yes’ here.

This just says, we tried to find this in the Path and failed - you can ignore IF you can connect

This just says, we tried to find this in the Path and failed – you can ignore IF you can connect

How Do I Know if I Did it ‘Right?’

You can check two things.

One – on a disconnected connection, right click. If you see ‘Reset Password’ available, you’re golden. Two, you can open Help > About and check the properties a la:

SQL Developer IS able to load the drivers, you win!

SQL Developer IS able to load the drivers, you win!

One Last Thing

SQL Developer 4 ships with an 11.2.0.3 JDBC driver. That means you’ll want at least an 11.2.0.3 Client for this to be compatible with SQL Developer. 11.2.0.1 or 11.2.0.2 won’t work. At some point we’ll ship with a 12c driver, which means you’ll need a 12c client. But we’ll worry about that later 🙂

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 67

  1. Hello, I noticed that when I use the OCI/Thick option, exporting data is a lot slower. When OCI/Thick is selected the export rate is about 100 rows per second, but when OCI/Thick is not selected the rate is about 1,000 records per second. Is this expected?

    1. thatjeffsmith Post
      Author
  2. Jeff,

    For reset password option, I am trying to use the Oracle Instant Client, as mentioned in this thread, but I am still getting the following error on my Windows 64-bit machine. I already added the instant client location in the PATH variable and restarted SQL Developer, but still same error.

    Can you please help?

    Testing the Instant Client located at C:\Users\baitha\Downloads\instantclient
    Testing client directory … OK
    Testing loading Oracle JDBC driver … OK
    Testing checking Oracle JDBC driver version … OK
    Driver version: 11.2.0.3.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 C:\Users\baitha\Downloads\instantclient is present and precedes any other client installations.
    java.library.path = C:\Program Files\Java\jdk1.8.0_31\jre\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Users\baitha\Downloads\instantclient_11_2;C:\ProgramData\Oracle\Java\javapath;C:\Program Files (x86)\Teradata\Client\14.00\AXSMOD\;C:\Program Files (x86)\AMD APP\bin\x86_64;C:\Program Files (x86)\AMD APP\bin\x86;C:\oracle\product\11.2.0\client\bin;C:\Program Files (x86)\Teradata\Client\13.10\CLIv2\;C:\Program Files (x86)\Teradata\Client\13.10\Shared ICU Libraries for Teradata\lib\;C:\Program Files (x86)\Teradata\Client\13.0\CLIv2\;C:\Program Files (x86)\Teradata\Client\13.0\Shared ICU Libraries for Teradata\lib\;C:\oracle\product\10.2.0\client\bin;C:\Program Files (x86)\Teradata\client\14.00\Teradata Parallel Transporter\bin;C:\Program Files (x86)\Teradata\client\14.00\Teradata Parallel Transporter\msg;C:\Program Files (x86)\Teradata\Client\14.00\ODBC Driver for Teradata\Lib\;C:\Program Files (x86)\Teradata\Client\14.00\bin\;C:\Program Files (x86)\Teradata\Client\14.00\CLIv2\;C:\Program Files (x86)\Teradata\Client\14.00\Shared ICU Libraries for Teradata\lib\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\TortoiseSVN\bin;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Users\baitha\AppData\Local\Smartbar\Application\;C:\Program Files (x86)\QuickTime\QTSystem\;C:\Program Files (x86)\ATI Technologies\ATI.ACE\Core-Static;C:\Program Files (x86)\Sennheiser\SoftphoneSDK\;C:\Users\baitha\Downloads\instantclient;C:\Program Files (x86)\SSH Communications Security\SSH Secure Shell;.

  3. Testing client directory … OK
    Testing loading Oracle JDBC driver … OK
    Testing checking Oracle JDBC driver version … OK
    Driver version: 11.2.0.3.0
    Testing testing native OCI library load … OK
    Success!

    after restarting sql developer reset password option not getting?

    What i am missing?

    1. thatjeffsmith Post
      Author
  4. Hi Jeff,

    I would like to specify the OCI thick driver for SQL Developer Data Modeler but the preferences there do not include the ability to do that. Is there a workaround, such as using the preferences from SQL Developer?
    Why?: our developers no longer use or know the schema passwords because their priveleges on each schema are tied to proxy users who are connected to the Windows AD. Works well with the thick driver, but not without.

    Thanks for the excellent blog.

    1. thatjeffsmith Post
      Author

      I would run the modeler through SQL Developer to get the thick client connection support there, or try to form the proper jdbc url…

      jdbc:oracle:oci:@SID.WORLD

      Note: For thick client to work, the Oracle client library directory must be added to the library search path. Oracle client libraries are installed under $ORACLE_HOME/lib and ORACLE_HOME/lib32 directories. Add this directory to the library search path environment variable. The library search path environment for AIX is LIBPATH, for HP-UX is SH_LIB_PATH for Linux is LD_LIBRARY_PATH and for Windows is PATH.

      Got this off a blog from Oracle.com…I’ve not tested it.

  5. I am getting “oracle/jdbc/driver/GeneratedPhysicalConnection$1” when trying to connect.

    I am using Sql Developer 4.0.3
    I have Oracle Client 11.2.0.0 installed and trying to use Instant Client 11.2.0.3

    1. The full error message –
      java.lang.NoClassDefFoundError: oracle/jdbc/driver/GeneratedPhysicalConnection$1
      at oracle.jdbc.driver.GeneratedPhysicalConnection.getSystemProperty(GeneratedPhysicalConnection.java:82)
      at oracle.jdbc.driver.GeneratedPhysicalConnection.getSystemPropertySqlTranslationProfile(GeneratedPhysicalConnection.java:46)
      at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:549)
      at oracle.jdeveloper.db.adapter.AbstractConnectionCreator.getConnection(AbstractConnectionCreator.java:185)
      at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.openConnection(RaptorConnectionCreator.java:348)
      at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.getConnection(RaptorConnectionCreator.java:85)
      at oracle.dbtools.raptor.dialogs.conn.ConnectionPrompt.promptForPassword(ConnectionPrompt.java:67)
      at oracle.jdevimpl.db.DBConnAddin$1.promptForPassword(DBConnAddin.java:78)
      at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:366)
      at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:299)
      at oracle.jdevimpl.db.adapter.CAConnectionCreator.createConnectionImpl(CAConnectionCreator.java:66)
      at oracle.javatools.db.DatabaseFactory.createConnection(DatabaseFactory.java:838)
      at oracle.javatools.db.DatabaseFactory.createDatabase(DatabaseFactory.java:282)
      at oracle.jdeveloper.db.DatabaseConnections.getDatabase(DatabaseConnections.java:833)
      at oracle.dbtools.raptor.utils.Connections$ConnectionInfo.getDatabase(Connections.java:175)
      at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1165)
      at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1139)
      at oracle.dbtools.raptor.navigator.db.DatabaseConnection.openConnectionImpl(DatabaseConnection.java:146)
      at oracle.dbtools.raptor.navigator.db.AbstractConnectionNode.getConnection(AbstractConnectionNode.java:30)
      at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode.getObjectFactory(DatabaseTreeNode.java:83)
      at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:138)
      at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:112)
      at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:554)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      at java.lang.Thread.run(Thread.java:745)

    2. thatjeffsmith Post
      Author
  6. Like yourself my company is switching from TOAD to SQL Developer.

    I checked my PATH for Oracle Home:

    User variable ORACLE_HOME C:\Oracle\product\12.1.0\client_1
    System variable ORACLE_HOME C:\oracle\oraclient11g

    Whether I choose either one of these locations as the Oracle Home Client Type I get the following errors:

    Testing the Oracle Home located at C:\Oracle\product\12.1.0\client_1
    Testing client directory … OK
    Testing loading Oracle JDBC driver … Failed:
    C:\Oracle\product\12.1.0\client_1\jdbc\lib\ojdbc6.jar is not a valid jar file

    Testing the Oracle Home located at C:\Oracle\OraClient11g
    Testing client directory … OK
    Testing loading Oracle JDBC driver … Failed:
    C:\Oracle\OraClient11g\jdbc\lib\ojdbc6.jar is not a valid jar file

    1. thatjeffsmith Post
      Author
        1. Although that is the ORACLE_HOME path I selected, I changed the dropdown to “Instant Client” instead of “Oracle Home” and it worked… go figure.

  7. This is a great blog post! Thanks for all the details down to the files it needs to find. I figured out on one of our boxes that I have 11.2.0.1 Client so that must be our issue. Will hopefully get this sorted out soon. Thanks again for the great help!

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,
        I have same error with OCI library load Failed. I extracted the instance client and all files should be in the same folder. Not sure why it does not recognize the OCI driver. Is there environment variables that I need to set?

        Here is output:
        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 PATH. Check it to verify that
        the expected native library directory C:\Scott\Downloads\instantclient-basic-windows.x64-12.1.0.2.0\instantclient_12_1 is present and precedes any other client installations.
        java.library.path = C:\Scott\Downloads\sqldeveloper-4.1.1.19.59-x64\sqldeveloper\jdk\jre\bin;C:\windows\Sun\Java\bin;C:\windows\system32;C:\windows;C:\windows\system32;C:\windows;C:\windows\System32\Wbem;C:\windows\System32\WindowsPowerShell\v1.0\;C:\windows\idmu\common;C:\Program Files\Dell\Dell Data Protection\Encryption\;.

        1. thatjeffsmith Post
          Author
          1. Thanks for the response. I’m not very familiar with OS Path. Can you tell me where the OS Path and what is needed to put in?

          2. thatjeffsmith Post
            Author

            OS = operating system
            PATH is an environment variable that Windows uses to define directories to search when looking for exe’s, dll’s, etc

            You’ll need admin privs on your machine

            If you don’t understand this, better to get your local admin to help

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

    1. thatjeffsmith Post
      Author
  11. 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?

    1. thatjeffsmith Post
      Author

      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.

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

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

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

    1. thatjeffsmith Post
      Author

      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?

  14. 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. 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! 😀

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author

      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

      [sql]
      select *
      — doing this because I love my data
      from dual;
      [/sql]

      Do this

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

      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.

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

        1. thatjeffsmith Post
          Author

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

    1. thatjeffsmith Post
      Author
  18. thatjeffsmith Post
    Author

    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.

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

  20. thatjeffsmith Post
    Author

    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.

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

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

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

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

        1. thatjeffsmith Post
          Author

          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

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

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

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author

Leave a Reply

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