Oracle – for when it was like that when you got there

Main menu

Post navigation

Solved – The Mystery of SQLDeveloper and the Missing ocijdbc11

This is a follow up to my earlier post about SQLDeveloper being moody and suddenly refusing to connect to a database via TNS.
Having had a bit of a dig around, it would seem that this problem is not platform specific and affects Windows in the same way.
At this point, I’d like to say a big “thank you” to Grzegorz Wilczura, who referred me to this article by Sue Harper.
If you’re hitting this problem on Windows, then you may want to follow the instructions there to set up a TNS_ADMIN environment variable.

Remember, this problem had two primary symptoms :-

Empty Network Alias list when defining a TNS connection

When testing an existing TNS connection you get :Status: Failure – Test failed : no ocijdbc11 in java.library.path

In Sue’s article, it states that SQLDeveloper looks for a tnsnames.ora in the following places in this order :

$HOME/.tnsnames.ora

$TNS_ADMIN/tnsnames.ora

/etc/tnsnames.ora

$ORACLE_HOME/network/admin/tnsnames.ora

Only one of these places is has an absolute path. The rest all rely on environment variables being set. However, when I run SQLDeveloper from the Ubuntu desktop menu, I’m not starting a shell, so my .bashrc doesn’t get executed. Therefore, these variables are not set.
When I setup my first tns connection, I’d just installed sqldeveloper and ran it by executing the shell script ( sqldeveloper.sh) from a Terminal Window. Of course, the $ORACLE_HOME was set in this environment and SQLDeveloper could therefore see the tnsnames.ora in $ORACLE_HOME/network/admin.

All of this means that the cause of the problem is that SQLDeveloper cannot see, or can no longer see, the tnsnames.ora file.

Copying the tnsnames.ora to /etc will fix the problem. However, probably the best solution is to ensure that we’re only referencing one tnsnames.ora and don’t replicate it. That way, we only ever have to change it in one place, should the need arise.

So, the alternative I’ve chosen is to set the $ORACLE_HOME environment variable in sqldeveloper.sh – the script that gets called to start SQLDeveloper.
Start a terminal and go to the SQLDeveloper home directory ( in my case, I installed SQLDeveloper in /opt) :-

Now re-start SQLDeveloper from the Ubuntu menu. Remember, this menu item is simply executing the shell script we’ve just changed.
If you have an existing tns connection defined then you can test doing the following :

Right-click the connection and select Properties from the pop-up menu.

This will bring up the New/Select Database Connection window.

Enter the password in the Password field

Hit the test button.

The test should now succeed.

If you haven’t got a TNS connection defined currently, you should now be able to test by setting one up, with no problem.

26 thoughts on “Solved – The Mystery of SQLDeveloper and the Missing ocijdbc11”

Hi Mike – I do not understand what the content of Sue’s article has to do with the missing ocijdbc11, or even what your article has to do with it :-( I only uderstand that you can’t connect because your tnsnames.ora was missing, but what is the relation to ocijdbc11 here?

the reason that SQLDeveloper gives the missing ocijdbc11 error is that it cannot see the tnsnames.ora.
The tnsnames.ora is not missing. It’s right where it’s supposed to be ( usually $ORACLE_HOME/network/admin)
The reason that SQLDeveloper can’t see it is because looking for it using environment variables ( $HOME, $TNS_ADMIN, $ORACLE_HOME) which have not been set for the session in which it’s running.
By setting these environment variables, you allow SQLDeveloper to find the tnsnames.ora. This means that it will now allow you to connect via TNS and will no longer give you the ocijdbc11 error.

I was able to get SQLDeveloper to connect using your guidance regarding setting an ORACLE_HOME to help it find the tnsnames.ora. Using this tool on a Windows box I simply set the environment variable from ‘Advanced’ tab on the system properties. Making sure the SQLDeveloper check box for using OCI/Thick client connections was unchecked, I was able to once again connect.

I was referring to setting an ORACLE_HOME environment variable from my windows box using the Properties/Advanced/Environment Variables tab. You can also arrive at a compatible solution by following rockspider’s advice in setting the tnsnames directory preference within the SQLDeveloper tool. Both approaches appear to work.

I *did* have TNS_ADMIN set correctly, but that directory also contained a file called tnsname.ora.test, to which I had no access. SQLDeveloper tried to read it (even though it had already read the tnsnames.ora file which was there and which it could read). Once it found it couldn’t read it it seems to have ignored TNS, so I got the “no ocijdbc11″ error.

Why is it looking at tnsname.ora.test in the first place?
And why does it ignore data from the correct file?

This strange lookup behaviour is not exhibited by an older linux binary install (of Oracle9) – it just looks for tnsnames.ora.
Also, SQLDeveloper makes no attempt to look for $HOME/.tnsnames.ora

Rockspider’s solution is perfect for SQLDeveloper. The reason I chose to go down the route of setting system wide variables is because, if you have any other Oracle client software on your system, that will also need to see the tnsnames.ora to connect.
Notwithstanding that, I’m really glad you found a solution here. Thanks again to Rockspider for the info.

I was facing the same problem with ocijdbc11. Tried many things with Environment Variables and Registry with no effect. Then started using version 3.1, could use it without any problem. Now I have both version 2.1 that is having problem and not usable and also version 3.1 that is working perfectly.

im installing the sql developer but it shows some errors like
# An unexpected error has been detected by HotSpot Virtual Machine:
EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x77c47fd4, pid=308, tid=3696

This is an example of a truly bizarre error message. I had a file called tnsnames.old in the folder that was pointed to by the Tnsnames directory variable in tools/preferences/databases/advanced .When I removed that the error went away. So even though it appears that the problem is with a java path value, that is just to throw you off the scent!

Thanks dude. you’ve made my day.. I was having the same error with my mac.
i have modified my sqldeveloper.sh in /Applications/SQLDeveloper.app/Contents/MacOS to include TNS_ADMIN i previously defined in my .bash_profile.