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 :-

  1. Empty Network Alias list when defining a TNS connection
  2. 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) :-

cd /opt/sqldeveloper
sudo gedit sqldeveloper.sh

Now amend the file so it looks something like this :

#!/bin/bash
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_HOME
cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*

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 :

  1. Right-click the connection and select Properties from the pop-up menu.
  2. This will bring up the New/Select Database Connection window.
  3. Enter the password in the Password field
  4. 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.

About these ads

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

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

    • Werner,

      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.

      HTH

      Mike

  2. Hey, Mike –

    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.

    Thanks for the heads up.

    Peace.

      • Hello, Hardy –

        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.

        HTHYO. Peace.

  3. OK – possibly weirder…

    This is for SQLDeveloper on Linux.

    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

  4. Just use the setting in sqldeveloper:
    Tools > Preferences > Database > Advanced > Tnsnames Directory

    Click Browse to select the location of the parent folder of your tnsnames file.

  5. You rock rockspider!! I’ve been searching everywhere for the solution.

    Mike,

    You should move rockspider’s solution to the top of your article.

    I appreciate you guys taking the time to post.

    -Duke

    • Duke,

      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.

      Mike

  6. Pingback: Upgrading to SQLDeveloper 3.0 on Ubuntu « The Anti-Kyte

  7. I have such error on Windows. Dissapear after adding to user environment variables two values, In my case:
    ORACLE_HOME set to C:\oracle
    TNS_ADMIN set to C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

    • Diya,

      what version of SQLDeveloper are you using and which OS ?
      Also, what environment variables do you have set ?

      Mike

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

  9. Specify the TNS entry folder under the Tools–>Preferences–>Database–>Advanced tab and also add the jdbc driver path under the Tools–>Preferences–>Third Party Jdbc Driver. That all works fine to me.

    Cheers,
    Suresh

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

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

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

    Thanks again.

  13. Dear All,

    If you don’t have Oracle Software on your system even though you can connect to the database :
    Please follow below steps:

    Connection name: ABDD
    USERNAME: ….
    PASSWORD: …..

    Connection Type : BASIC Role : Default
    Host name : IP of Oracle Server
    PORT: 1521
    SID: DB Name

    Regards,
    Ashish

  14. Pingback: Oracle SQL Developer and missing ocijdbc11.dll | Wilson Wu's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s