12 Replies Latest reply: Feb 3, 2012 1:59 PM by Gary Graham-Oracle RSS

    Test failed - no ocijdbc11 in java.library.path

    user200021
      Hello

      I'm really struggling with the following issue. Whenever I now try and connect to a DB I receive the following error:

      Test failed - no ocijdbc11 in java.library.path

      I had previously been able to connect to these databases. I tihnk I may have made a change to a registry file (mistakenly) as since then, I have not been able to connect. I have done lots of googling and searching on this forum, but have yet to find a fix that has been successful for me... hence the reason for this new thread

      I am a functional consultant and simply use SQL Developer for basic queries. I'm not really that technical. Feel a bit out of my depth trying to fix this problem!

      FYI - The following setup may give you a better understanding of how to help
      - Preferences > Database > Advance > 'Use OCI/Thick drive' - This option is NOT checked
      - Preferences > Database > Advance > 'Tnsnames Directory' - This is set to the same directory as that before I started experiences problems

      Please help! I'm not sure what else to try

      Thanks in advance

      Ross
        • 1. Re: Test failed - no ocijdbc11 in java.library.path
          user200021
          Forgot to mention....

          Connection Type is 'TNS'
          Connect Identifier is that of DB name in the TNS file

          Thanks again in anticipation for your help

          Ross
          • 2. Re: Test failed - no ocijdbc11 in java.library.path
            user200021
            I found the following in a similar thread...

            "I had the "verify the url format for the specified driver" problem and what was missing in my case was the ORACLE_HOME setting in the environment variables. Once I set that, it worked just fine."


            Problem is - even if this would fix my problem - I dont know how to set the ORACLE_HOME setting in the environment variables

            Thanks for your help

            Ross
            • 3. Re: Test failed - no ocijdbc11 in java.library.path
              Gary Graham-Oracle
              Hi Ross,

              So close, yet so far... The thread you reference,
              Re: no ocijdbc11 in java.library.path

              is chock full of information, so I really can't offer much new information, but perhaps I can restate it in a better context.

              1. If you have no Oracle client installed, you should still be able to connect to databases using the Basic connection type.
              2. Assuming the data shown in TNS Connection Identifier is not corrupt, get the host, port, sid or service name required by Basic from there.
              3. This assumes you aren't restrained by some special requirement so that you must use a TNS Connection Identifier.
              4. If this is what you wish to try, then please leave unchecked the Tools|Preferences|Database|Advanced|Use OCI/Thick Driver box.
              5. I recommend leaving the existing connection definitions alone, but create a new Basic connection for each as discussed above.

              or

              4. You really had an Oracle client installed, and nothing happened recently to remove it or add additional Oracle client software.
              5. See what value is set for Tools|Preferences|Database|Advanced|Tnsnames Directory.
              6. Maybe it is something like: C:\app\<userid>\product\11.2.0\dbhome_1\NETWORK\ADMIN
              7. If a similar directory exists on your hard drive, then you may set your ORACLE_HOME to the part preceding \NETWORK\ADMIN
              8. Right-click on My Computer, select Properties, Advanced tab, Environment Variable button
              9. Look in the System Variables list, and see if an ORACLE_HOME variable already exists.
              10. Either edit/verify it matches the value discussed above, or create a new ORACLE_HOME system variable with that value.
              11. Check-off the Tools|Preferences|Database|Advanced|Use OCI/Thick Driver box if you need to use TNS Connection Identifiers.
              12. Otherwise leaving it unchecked should be OK.
              13. If the ORACLE_HOME had to be created or altered, then you may need to exit and restart SQL Developer.

              Regarding the java.library.path, if an ORACLE_HOME is set, then SQL Developer uses ORACLE_HOME to set java.library.path automatically on Windows. On Unix/Linux, one must manually set the LD_LIBRARY_PATH environment variable.

              Hope this helps,
              Gary
              SQL Developer Team
              • 4. Re: Test failed - no ocijdbc11 in java.library.path
                user200021
                Hi Gary

                Thanks a lot for your help - really appreciate it!

                I recently installed Quest TOAD, as a trial, but then uninstalled it. Since then, I've been having these problems. Perhaps that's related?

                So, to answer the second set of your instructions below:

                4. I recently installed and then uninstalled TOAD. I then uninstalled and reinstalled SQL developer
                5. Currently (for ease of access) this is set to - C:\Users\wilkinsonr2\Desktop - (i.e. my desktop)
                9. ORACLE_HOME variable doesnt exist - what should I set the value to (in light of where my tns.ora file is currently stored)?
                11. This is unchecked. Previously I had been setting the 'Connection Identifier' with it unchecked - should I now check it?

                I'm still not having much luck in connecting using my existing connections, so any further help would be greatly appreciated - thanks again!

                Ross
                • 5. Re: Test failed - no ocijdbc11 in java.library.path
                  user200021
                  Hi Gary

                  Thanks a lot for your help - really appreciate it!

                  I recently installed Quest TOAD, as a trial, but then uninstalled it. Since then, I've been having these problems. Perhaps that's related?

                  So, to answer the second set of your instructions below:

                  4. I recently installed and then uninstalled TOAD. I then uninstalled and reinstalled SQL developer
                  5. Currently (for ease of access) this is set to - C:\Users\wilkinsonr2\Desktop - (i.e. my desktop)
                  9. ORACLE_HOME variable doesnt exist - what should I set the value to (in light of where my tns.ora file is currently stored)?
                  11. This is unchecked. Previously I had been setting the 'Connection Identifier' with it unchecked - should I now check it?

                  I'm still not having much luck in connecting using my existing connections, so any further help would be greatly appreciated - thanks again!

                  Ross
                  • 6. Re: Test failed - no ocijdbc11 in java.library.path
                    orafad
                    To repeat, sort of, part of Gary's reply -

                    Do you need to use Connection Type: TNS? Why not Basic?

                    The latter type means that no thick oci jdbc driver is needed, the jdbc and java net implementation is used instead. Not having to rely on separate set of dll's makes SQL Developer more "portable", which might seem like a nice attribute for a tool used by consultants.

                    (4.) Also, note that Oracle Database Client (or Instant Client) is a separate install from SQL Developer (or toad etc.).

                    You may find this thread helpful, {thread:id=2317271}.


                    Edit:
                    added a few pieces

                    Edited by: orafad on Feb 2, 2012 11:10 AM

                    Edited by: orafad on Feb 2, 2012 11:18 AM
                    • 7. Re: Test failed - no ocijdbc11 in java.library.path
                      user200021
                      Many thanks for your reply and helpful comments orafad

                      "Do you need to use Connection Type: TNS?" - Dont know is the honest answer! What's puzzling me is I had been able to successfully connect using this 'type' before, to the same databases, from the same location etc etc, but now it just wont work. So, I wonder with me installing and then unstalling toad, something has got 'undone / overridden' etc. It'd just be really nice to get to the bottom of this

                      Thanks again for all your help

                      Cheers

                      Ross
                      • 8. Re: Test failed - no ocijdbc11 in java.library.path
                        orafad
                        user1576265 wrote:
                        It'd just be really nice to get to the bottom of this
                        I know the feeling.

                        The cause could be as simple as a change to PATH variable setting.

                        One suggestion is to use the Process Monitor tool, to be able to trace SQL Developer file actions as it tries to load ojdbc -> ocijdbc -> ... and so on.
                        • 9. Re: Test failed - no ocijdbc11 in java.library.path
                          Irian
                          You could try to create a batch file in the SqlDeveloper install directory with contents similar to this:
                          set ORACLE_HOME=%CD%
                          call sqldeveloper.exe
                          Then launch SqlDeveloper from this batch file; this will set your ORACLE_HOME variable to the SQL Developer home directory, possibly removing ORACLE_HOME related problems, then you will have to specify the directory where your TNSNAMES.ORA file lives in:
                          Tools -> Preferences -> Database -> Advance -> Tnsnames Directory
                          • 10. Re: Test failed - no ocijdbc11 in java.library.path
                            Gary Graham-Oracle
                            Hi Ross,

                            After getting lots more good advice from orafad and Irian, I think you need to make a decision. Obviously you want to go back to the way things were before the Toad installation, but it's doubtful you will ever be able to do that. Maybe if you were using some special monitoring software that tracks what gets installed and lets you rollback it back. Something like the Windows Restore Points feature, but for all software installations, not just system software, registry, and device drivers.

                            Often a "remove and replace" maintenance approach is cheaper than trying fix a problem in-place. Move forward!

                            So, my advice is:
                            1. For the fullest functionality, use the link orafad provides and read Turloch's last post on getting and installing an instant client.
                            2. For the simple functionality (Basic connections), use Irian's advice. You can even install and run SQL Developer from a flash driver.

                            Best Wishes,
                            Gary
                            • 11. Re: Test failed - no ocijdbc11 in java.library.path
                              user200021
                              Thanks for all your help Gary, Orafad & Irian - really appreciate it. For now I think i'll stick with the basic connection, and if I need to look into installing anything else I know where to go!

                              Just out of interest - what are the limitations when using the basic connection, as apposed to TNS?

                              THanks

                              ROss
                              • 12. Re: Test failed - no ocijdbc11 in java.library.path
                                Gary Graham-Oracle
                                Ross,

                                This may be a case of too much information, but see the list of differences here:
                                http://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#CJAJFBAI

                                which I found in this Ask Tom thread:
                                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2754953100346422385

                                As for differences of specific interest to a SQL Developer user, I can only think of one recent complaint:
                                3.1 EA  reset password


                                Regards,
                                Gary