Where is pl/slq developer picking up tnsnames.ora

I installed Oracle instant client 11.1

I am able to log into database using command prompt. It picks up the correct tnsnames.ora in folder C:\oracle\instantclient_11_1\TNSNAMES.ora

However, when i use an external tool such as TOAD or Pl/Sql developer it fails to pick up that tnsnames.ora

How do i know which tnsnames.ora is pl/sql developer picking up, and redirect the tool to pick up tnsnames from the above given path?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
In Pl-Sql Developen
Under Help Menu>Support Info

will contain the details of TNS names files like below

TNS File
Take a look at your HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\inst_loc setting and make sure it points to the right place.

The individual home directories you have set up will have sub-keys under there, make sure they exist and are correct as well.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Example registry settings from my system running PL/SQL:
  inst_loc = C:\Program Files\Oracle\Inventory

    ORACLE_HOME = C:\Oracle\product\10.1.0\Client_1
    ORACLE_HOME_NAME = OraClient10g_home1
    ORACLE_GROUP_NAME = Oracle - OraClient10g_home1
    ORACLE_BUNDLE_NAME = Enterprise

      TraceFileName = C:\ODPNET.trc
      TraceLevel = 0
      TraceOption = 0
      StatementCacheSize = 0

Open in new window

gram77Author Commented:
sqldeveloper shows oracle homes this way:
  OraClient10g_home1  (C:\oracle\product\10.2.0\client_1)

I have tnsnames in:

still pl/sql developer is not picking up the file.

I want pl/sql developer to pick up TNSNAMES.ora from 11.1 instantclient:

how do i do this
There obviously isn't an Oracle Home se tup for your 11.1 instant client.  You'll need to set that up.  There are instructions on how to do that here (scroll to the bottom):
try following

go to Tools->Preferences

Under Oracle Select "Connection" Option

Select manually Oracle Home  and OCI Library

Apply the changes.

Restart PL/SQL developer and test connection.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gram77Author Commented:
I am getting access denied in this path as the folder is write protected, and any attempt to change folder from readonly results in error


new setting in tools->preferences
oracle home:C:\oracle\instantclient_11_1
oci library: C:\oracle\instantclient_11_1\oci.dll

however still i am getting error, however seems at least it is oracle error:
could not resolve the connect identifier specified
which version of windows you are usind? Do you have admin aCcess?
gram77Author Commented:
i don't have admin access to my pc, so can't install any software at will
gram77Author Commented:
all i want to know is when TNSNAMES.ora and oci.dll are present in C:\oracle\instantclient_11_1
and i have provided the explicit path to the toll, why isn't the tool picking them up
Try defining an environment variable TNS_ADMIN and set to the directory of your instant client. Don't tell me you can't do that either :(-
gram77Author Commented:
thanks newbie, but i don't need your help.
Well, I'm just speculating at this point, but usually the tnsnames.ora file would be located in C:\oracle\instantclient_11_1\network\admin\tnsnames.ora and not C:\oracle\instantclient_11_1.  If you have rights to create those directories, you might try moving it there.
gram77Author Commented:
didn't work
Well, it was a shot in the dark.  I'm not sure you're going to be able to get anywhere without modifying the registry.  You've tried manually setting the home directory in the tool and that didn't help either.

I warn you that I'm really grasping at straws here.  Once I've run out of the most logical answers I usually start trying everything I can think of, and sometimes I can find something that works, and sometimes not.

That being said, take a look at this page:

Specifically this part of it:
    How does SQL Developer find my TNSNames file?
        SQL Developer checks the following:
        1) $HOME/tnsnames.ora
        2) $TNS_ADMIN/tnsnames.ora
        3) /etc/tnsnames.ora ( non-windows )
        4) $ORACLE_HOME/network/admin/tnsnames.ora
        5) Registry Key

It has information about how "SQL Developer" (An Oracle tool unrelated to Toad or PL/SQL Developer) finds the tnsnames.ora file.  It's possible if you try all the ways it can find the file, it might also work for PL/SQL Developer and/or Toad.

I'd try starting a command prompt and setting TNS_ADMIN and/or ORACLE_HOME (In that order) and then starting PL/SQL Developer or Toad from that same command prompt session.  Also, MAKE SURE that you set the variable to upper-case.  There is a thread I was reading that said it won't work if it's not all  upper-case, however I haven't tried this myself.

You have to do it from the same prompt because the settings are not kept outside of each command session unless you can change the environment variables in the system properties, which I'm assuming you do not have rights to do.

Good luck... if this doesn't work I'm really out of ideas.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.