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.’
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.
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:
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.
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:
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 🙂
95 Comments
Trying to get kerberos working w/ SQL Developer. Thank you for this article. Followed the article to the T and also the few tips mentioned in the comments. My environment :
(giving example paths for help in identifying what I missed)
SQL Developer : Version 20.4.1.407 (C:\sqldeveloper)
Oracle Client : SQL*Plus: Release 12.2.0.1.0 (C:\12.2.0\client_1)
Enabled Kerberos on the databases (Linux) and works just fine w/ the Oracle Client. (SQLPlus)
Configuring SQL Developer to work w/ Kerberos.
Under Tools ==> Preferences ==> Database ==> Advanced ==>
a. Under Kerberos Thin Config :
Config File : C:\12.2.0\client_1\network\admin\krb5.conf
Credential Cache File : blank
b. Use Oracle Client : checked
Oracle Home: file:/C:12.2.0/client_1/
c. Use OCI/Thick driver : checked
Tnsnames : C:\12.2.0\client_1\network\admin (We dont use tnsnames. We use LDAP. But I wanted to see if it works at all)
In sqldeveloper.conf : AddVMOption -Djava.library.path=C:\12.2.0\client_1\bin
In Control Panel ==> User accounts ==> Path : C:\12.2.0\client_1;C:\12.2.0\client_1\bin
Testing the connection :
a. Right click on connection : Reset Password is enabled.
b. Help ==> About ==> Properties ==> oci is “true”
c. Authentication Type : Kerberos, Connection Type : Basic, Username/Password : blank, Service Name used
Error : no ocijdbc21 in java.library.path
Tried copying the 2 dlls from 21c client .. got another error : the specified procedure could not be found.
My question :
a. Do we need 21c client instead of 12.2 ?
b. How can I go about getting it to work w/ thin client (the tabs removal from krb5.conf etc doesn’t make any difference)
c. What are the settings for the connection ? Kerberos and Basic or Kerberos and LDAP..
That install has a 19c jdbc driver so a 12.2 client should work..what jdk are you using?
Trying a 21c Client wouldn’t hurt, but weird.
So, 21c client worked w/ OCI/Thick selected.
My JDK info is : (from About==> Properties)
java.endorsed.dirs C:\Program Files\sqldeveloper\jdk\jre\lib\endorsed
java.ext.dirs C:\Program Files\sqldeveloper\jdk\jre\lib\ext;C:\windows\Sun\Java\lib\ext
java.home C:\Program Files\sqldeveloper\jdk\jre
jdk.home C:\Program Files\sqldeveloper\jdk\jre\..
sun.boot.class.path /Program Files/sqldeveloper/rdbms/jlib/ojdi.jar;C:\Program Files\sqldeveloper\jdk\jre\lib\resources.jar;C:\Program Files\sqldeveloper\jdk\jre\lib\rt.jar;C:\Program Files\sqldeveloper\jdk\jre\lib\sunrsasign.jar;C:\Program Files\sqldeveloper\jdk\jre\lib\jsse.jar;C:\Program Files\sqldeveloper\jdk\jre\lib\jce.jar;C:\Program Files\sqldeveloper\jdk\jre\lib\charsets.jar;C:\Program Files\sqldeveloper\jdk\jre\lib\jfr.jar;C:\Program Files\sqldeveloper\jdk\jre\classes;C:\Program Files\sqldeveloper\jdk\lib\tools.jar;C:\Program Files\sqldeveloper\jdk\lib\dt.jar
sun.boot.library.path C:\Program Files\sqldeveloper\jdk\jre\bin
Is Thin client Kerberos config w/ OSMSFT (windows cache) supported on SQL Developer 20.4.1 ?
I came across this again today when setting up SQL Developer.
I found I have to drop back to version 19.4 of SQL Developer to get this to work with the latest version of the client (19.8).
Also as a mac user I found I had issues with the configuration… to hopefully save others some time, here is a link to the oracle community post for fixing the DYLD_LIBRARY_PATH. Scroll to the bottom and see the John McGinnis post.
https://community.oracle.com/tech/developers/discussion/3995451/error-loading-the-native-oci-library-on-os-x-using-12-1-0-2-0
the MAC tax is very heavy when it comes to setting up thick clients and DYLD bits, thanks for sharing!
If you want to use client with 20.4.1 you actually need the 21c client, but 20.4.0 should work with the 19c client…
Sorry, Jeff, I meant instant client, not client.
The latest instant client for mac is 19.8
Yeah, I don’t have Mac rig setup for instant client, but 19.8 works just fine on Windows with SQLDev 21.2
Hello,
Is there a way to inventory what Oracle clients remain installed on a system?
Thanks,
Wil Blake
On a ‘system’ – what kind of system?
Thanks for this information. Why the software is not already set for Oracle thick client and tnsnames.ora by default is hard to imagine. Not to mention that the Help is entirely useless in determining which settings need to be set and where to go to get it to work with them.
Because for 90+% of people you don’t need a thick client. We give you a jdbc driver, and you’re good to go.
Tell me where you got lost, and I can make sure the help is correct. Note you’re commenting on a blog post that I wrote in 2014, so I’m not sure what version of SQL Developer or Oracle Database you’re using today in 2020 – things have changed a bit since then.
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?
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;;.
Show me your OS PATH setting.
Also, 4.0.3 is old. Very old.
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?
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.
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
You only need to do that if you want a THICK connection
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
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
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.
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
we set NLS based on what you have set in preferences, database – NLS
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
did you compare your nls settings from 4.2 and 4.1?
If OCI/Thick Driver is not setup, given error is not returned…
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
#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.
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?
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
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;.