What to do about "no ocijdbc11 in java.library.path" SQL Developer error

Question

I am trying to get create a TNS connection in SQL Developer on my mac laptop (OS X 10.9.5). I get this error no ocijdbc11 in java.library.path I googled around and found out that I need to install oracle's instant client. I found the instance client files here :

http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html

The files are just zip files that you need to download and extract somewhere. Then I found the instructions that actually tell you what to do with the zip files here:

https://docs.oracle.com/cd/E11882_01/install.112/e38228/inst_task.htm#BABHEBIG

The instructions say that :

Set the DYLD_LIBRARY_PATH and the NLS_LANG environment variables to the full path of the instantclient_11_2 directory. For example, if you unzipped the Instant Client zip file in the /bin/oracle directory, then set the DYLD_LIBRARY_PATH environment variable to /bin/oracle/instantclient_11_2.

What the instructions do not tell me is HOW to set environment variables permanently and how to make the environment variables accessible to GUI tools like SQL developer.

Has anyone gotten SQL Developer to work with instantclient?

Answer 1

Based on the answer of @Alex Poole: In El Capitan when SIP is enabled, this doesn't seem to work, because the DYLD_LIBRARY_PATH environment variable doesn't get transferred to the environment that bash ./sqldeveloper starts (last line of the SQLDeveloper.app/Contents/MacOS/sqldeveloper.sh) .

Solution: instead of editing the file SQLDeveloper.app/Contents/MacOS/sqldeveloper.sh I edited the file SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper and added the export DYLD_LIBRARY_PATH=/path/to/instantclient line there.

#!/bin/bash

export DYLD_LIBRARY_PATH=/path/to/instantclient

#=============================================================================
#  Launcher for Oracle SQL Developer
#  Copyright (c) 2005, Oracle. All rights reserved.
#=============================================================================

...

Answer 2

If you're comfortable editing files, you can set the library path in the internal startup script. I edited this through Terminal.app and vim, by going to:

cd <wherever SQL Developer was installed/unzipped>
cd SQLDeveloper.app/Contents/MacOS
cp -p sqldeveloper.sh sqldeveloper.sh.backup
chmod o+w sqldeveloper.sh
vim sqldeveloper.sh

The file is protected by default, so I'm changing it to be writable (and making a backup first, just in case). If you skip that step, with vim you can save it with :w! to save it anyway.

Alternatively find the SQLDeveloper application in Finder, right click, and choose 'Show Package Contents', then drill down to Contents->MacOS, right-click the sqldeveloper.sh file and choose 'Open With' and your favourite text editor - TextEdit will do. As the file is locked you will be prompted to unlock it at some point - maybe on open or first edit, but TextEdit will ask you if you want to unlock it when you save.

However you get into the file, you can then specify add a line to set/export DYLD_LIBRARY_PATH:

#!/bin/bash
# Next line added for TNS connections
export DYLD_LIBRARY_PATH=/path/to/instantclient
export JAVA_HOME=`/usr/libexec/java_home -v 1.7`
here="${0%/*}"
cd "${here}"
cd ../Resources/sqldeveloper/sqldeveloper/bin
bash ./sqldeveloper -clean >>/dev/null

... where /path/to/instantclient is your unzipped directory; in the quoted example above, that would be /bin/oracle/instantclient_11_2. Also note that this needs to be the 64-bit instant client; it will complain about the wrong architecture if you try to use the 32-bit version.

One the modified file has been saved, relaunch SQL Developer and your TNS connection should now work. If you want to be able to use a TNS alias you can also set/export a TNS_ADMIN variable that points to a directory containing a tnsnames.ora file.