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 🙂

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

84 Comments

  1. Using a connection type of ldap with Kerberos authentication. Seems to work just fine with OCI, but doesn’t work with thin drivers. I’m using Oracle client 12.2 and SqlDeveloper 17.3. Oracle support is stating when using a connection type of anything besides Basic, I’m required to use OCI drivers. Thoughts?

  2. Hi,
    Thank you for this blog post. I’ve set as mentioned in this article, however I am unable to get it working and have below log.
    Could you please help me out on how to fix it ?
    From the bottom, the path variable is set and every thing works except OCL driver failed to load.

    Log :

    Testing the Instant Client located at C:\Program Files\Oracle\instantclient_11_2
    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:\Program Files\Oracle\instantclient_11_2 is present and precedes any other client installations.
    java.library.path =
    D:\sqldeveloper-4.0.3.16.84-x64\sqldeveloper\jdk\jre\bin;
    C:\WINDOWS\Sun\Java\bin;C:\WINDOWS\system32;
    C:\WINDOWS;
    C:\Program Files\Microsoft MPI\Bin\;
    C:\oraclexe\app\oracle\product\11.2.0\server\bin;
    C:\Program Files (x86)\Intel\iCLS Client\;
    C:\Program Files\Intel\iCLS Client\;
    C:\WINDOWS\system32;C:\WINDOWS;
    C:\WINDOWS\System32\Wbem;
    C:\WINDOWS\System32\WindowsPowerShell\v1.0\;
    C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;
    C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;
    C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;
    C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;
    C:\Program Files\Intel\WiFi\bin\;
    C:\Program Files\Common Files\Intel\WirelessCommon\;
    C:\Program Files\Java\jdk1.8.0_65\bin;
    C:\Program Files\MicroStrategy\MicroStrategy Desktop\DataServices\180_77\64-bit\bin\server;
    C:\Program Files (x86)\MySQL\MySQL Fabric 1.5.4 & MySQL Utilities 1.5.4 1.5\;
    C:\Program Files (x86)\MySQL\MySQL Fabric 1.5.4 & MySQL Utilities 1.5.4 1.5\Doctrine extensions for PHP\;
    C:\Program Files (x86)\Skype\Phone\;
    C:\Program Files (x8;C:\Program Files\Git\cmd;
    C:\Program Files\Amazon\AWSCLI\;
    C:\WINDOWS\system32;
    C:\WINDOWS;
    C:\WINDOWS\System32\Wbem;
    C:\WINDOWS\System32\WindowsPowerShell\v1.0\;
    C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\;
    C:\Program Files\Microsoft SQL Server\130\DTS\Binn\;
    C:\Program Files\Oracle\instantclient_11_2\;
    C:\Users\sadakar\AppData\Local\Microsoft\WindowsApps;
    C:\Program Files\Docker Toolbox;;.

  3. John Peeken Reply

    FWIW: Spent the better part of two days chasing down my issue with getting “Reset Password’ to show up on the right mouse click of a disconnected connection, as well as, getting the property sqldeveloper.oci.available to show ‘true’ and not ‘false’.
    Matched the circumstances detailed in this webpage – http://www.databaseusers.com/article/6450077/sqldeveloper.oci.available+false
    Modified Path / PATH in Environment Variables –> User Variables and System Variables (on Windows 7) to try to get rid of the error “no ocijdbc in java.library.path” with no success.

    My Solution:
    Added the setting ‘AddVMOption -Djava.library.path=C:\ORACLE\instantclient_11_2’ as a line item into the %APPDATA%\sqldeveloper\\product.conf to point to the directory containing the ocijdbc.dll (this affects only you) or in the sqldeveloper\bin\sqldeveloper,conf (this affects all users).
    Ref: – http://www.thatjeffsmith.com/archive/2013/12/oracle-sql-developer-4-windows-and-the-jdk/

    Thanks Jeff for the numerous posts and webpages, they help get so much more from SQL Developer.
    Reports are now my daily obsession ! If only I could figure out how to do DML, like ‘grant :ROLENAME to :USRNAME’!
    Oh, can you add to the ‘File’ menu a ‘Restart’ selection to help those of us that have to recycle SQL Developer numerous times to troubleshoot things like getting OCI to work?

    • thatjeffsmith

      I wish you would have just reached out to me first – if you upgrade to version 17.2, you don’t need the thick client anymore to reset a password. We have it working with just the jdbc driver now.

    • John Peeken

      Hi Jeff,
      I agree with you that I do not need to have a thick client and the thin client works to reset the password.
      However, after removing the %APP_DATA%/SQL Developer and %APP_DATA%/sqldeveloper folders to get back to being like a ‘first time on the box’ install of SQL Developer, I then pointed to the Oracle Client on the box, but, Reset Password is greyed out when right clicking the connection and the sqldeveloper.oci.available property is false.
      Once I add ‘AddVMOption -Djava.library.path=C:\ORACLE\instantclient_11_2’ to %APPDATA%\sqldeveloper\\product.conf and recycle SQL Developer it then works.
      Is this step I am doing not normally needed?
      Thanks,
      John

    • John Peeken

      A few corrections/clarifications:
      1) I listed %APP_DATA% but it should be %APPDATA%
      2) I took the java_library_path parameter out of the product.conf and ensured that the c:\ORACLE\instantclient_11_2 was the first thing in both the User and System Environmental Variable’s Path/PATH, recycled SQL Developer, and the Reset Password worked.
      3) I can only get the ORA-01013 “User requested cancel of current operation” informational popup when I cancel a long running query when I have the “Use OCI/Thick driver’ checked in Preferences, but maybe that is expected behavior?
      Thanks,
      John

    • John Peeken

      Hi Jeff,
      After getting the OCI thin client working Reports started failing with java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

      After researching the issue came upon this:
      https://community.oracle.com/message/14445479#14445479
      which Gary Graham details :
      “From these results we might concluded one of following is true…
      1. The minimum required JDBC driver version for SQL Developer 17.2 is 12.1.0.1.
      2. There are one or more bugs in SQL Developer 17.2 that prevent it from working correctly with the 11.2 drivers.”

      Wound up upgrading the version of OCI thin client to 12.1 or higher (as well as the Microsoft Visual C++ Redistributable for Visual Studio 2015 or higher) to get my reports working.
      Thanks,
      John

    • thatjeffsmith

      All I am willing to commit to now is that we ship with a 12.2 JDBC driver, and it should work with any Oracle Client compatible with that version JAR.

  4. I downloaded new version 4.2.0.17.089. I liked Quick outline and formatter enhancements. However
    after setting OCI driver, I am getting ‘ORA-12705: Cannot access NLS data files or invalid environment specified’. I tried both 32-bit and 64-bit versions and both returns same error. Skip NLS Settings option has no effect. Is there any default NLS configuration supplied with new version ? Previous version 4.1 just worked fine.
    As I am trying to connect to a database, This is what I get:
    ORA-12705: Cannot access NLS data files or invalid environment specified
    12705. 00000 – “Cannot access NLS data files or invalid environment specified”
    *Cause: Either an attempt was made to issue an ALTER SESSION command
    with an invalid NLS parameter or value; or the environment
    variable(s) NLS_LANG, ORA_NLSxx, or ORACLE_HOME was incorrectly
    specified, therefore the NLS data files cannot be located.
    *Action: Check the syntax of the ALTER SESSION command and the NLS
    parameter, correct the syntax and retry the statement, or
    specify the correct directory path/values in the environment
    variables.
    Vendor code 12705

    • Satyendra

      This is what happening. What could be the possible cause and How should I debug?

      1) Version 4.1.5.21.78 – OCI Driver Works fine using Instatclient client 12.2.0.1.0
      2) Version 4.2.0.17.089.1709 – Throws NLS error when trying to connect to any database if OCI Driver is used
      3) Version 4.2.0.17.089.1709 – Works fine and connect to any database if OCI driver is not used

    • Satyendra

      If OCI/Thick Driver is not setup, given error is not returned…

  5. Since it’s hard to find steps on this type of thing, here is what I had to do (on Windows) in case it helps anyone. Among other things, I found that this allows me to queue one query while another is running.

    1. Set the OS environment variable – Control Panel > Edit System Environment Variables > Advanced > Environment Variables. Ensure that the path to your Oracle client is listed here (the actual folder that contains the dlls above, e.g. c:\oracle\instantclient_12_1). If SQL developer is open when you do this, close it and reopen
    2. In SQL Developer, go to Preferences > Database > Advanced and select Configure under the client section.
    3. If you have an instant client, select the Instant Client client type
    4. Browse to the folder containing the Oracle dlls
    5. Click ‘test’
    6. If successful, you’ll see a log message saying ‘success’
    7. Click OK
    8. Ensure that ‘Use Oracle Client’ is checked (this doesn’t check by default)
    9. Select Use OCI/Thick Driver

    • thatjeffsmith

      #1 is only necessary if you are using Instant Client, and if you alerady ‘installed’ the instant client, that step should have already been done.

      “Among other things, I found that this allows me to queue one query while another is running.”
      Explain please? We can run multiple queries just fine via unshared worksheets.

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

    • thatjeffsmith

      No, not expected. The Thick connection shouldn’t seen any performance degradation. You could try bumping up the array fetch size from say 50 to 200 and see if that makes a difference.

    • Jeff, thanks for the suggestion, but no luck. Do you have any other ideas?

      Thanks

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

Write A Comment