18

I have an Oracle tnsnames.ora file from my previous workplace. I want to pick it up with my newly installed PL/SQL Developer on another computer. I have copied the file into ..ORACLE/product/11.2.0/client_32/NETWORK/ADMIN, but PL/SQL Developer cannot find it.

When it starts it is not showing me any choice of database. In About->i->TNSNames I don't see any lines.

I have found a number of advices to look for it in Tools->Preferences->Database->..., but I don't have a Database tab in my preferences.

How can I fix it?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
MiamiBeach
  • 3,261
  • 6
  • 28
  • 54
  • Once I had the same problem. I found, that my PLSQL Developer had one more file `tnsnames.ora` in a program folder (the same folder, where file `plsqldev.exe` is situated). Make sure that it is not your case. – Dmitriy Mar 30 '15 at 18:23

7 Answers7

39

If you are certain your tnsnames.ora file is correct (e.g. by testing the connection with the Oracle Net Config Assistant, or logging in successfully with SQLplus), and you are able to open the PLSQL Developer application, but you still can't connect to the database in PLSQL Developer, then follow these steps:

  1. In PLSQL Developer (version 11.0) go to Help/Support Info

  2. Click the TNS Names tab. If the path in PLSQL Developer is wrong it will be blank (no tns file found) or incorrect (wrong tns file in use)

  3. On the Info tab scroll down to the TNS File entry and to see the path for the tns file PLSQL Developer is using. Very likely this is wrong.

  4. To correct the path:

  • open a command prompt

  • navigate to the PLSQL Developer directory in Program Files

  • enter this command:

    plsqldev.exe TNS_ADMIN=c:\your\tns\directory\path\here

    *path is to the directory containing your tnsnames.ora file - for me this is: c:\Oracle\product\11.2.0\client_1\network\admin

  1. A new PLSQL Developer UI will open and you should be able to connect.

  2. Make sure you have a Windows environment variable TNS_ADMIN set to the same path

  • On Windows 7 you go to Start, Control Panel, System, Advanced System Settings, Environment Variables to view/add/update environment variables
Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
Jessie G.
  • 391
  • 3
  • 3
  • Answer is correct, just noting additionally, TNS_ADMIN argument should be to the path only, do not enter tnsnames.ora into the argument, it is assumed – Markus Aug 26 '20 at 16:12
5

Which Oracle client are you using?

Oracle 64bit 11g client isn't support in PLSQL Developer. Try to install 32bits client.

Tarondor
  • 51
  • 1
  • 3
  • It's not that the client isn't supported but if you run a 32 bit Oracle 11g client you need to run a 32 bit PLSQL installation. If you run a 64 bit Oracle 11g client you need to run a 64 bit PLSQL installation – Albert Scholtz Oct 02 '18 at 12:48
4

I had the same problema, but as described in the manual.pdf, you have to:

You are using an Oracle Instant Client but have not set all required environment variables:

  • PATH: Needs to include the Instant Client directory where oci.dll is located
  • TNS_ADMIN: Needs to point to the directory where tnsnames.ora is located.
  • NLS_LANG: Defines the language, territory, and character set for the client.

Regards

Manuel Spigolon
  • 11,003
  • 5
  • 50
  • 73
2

Check if tnsnames.ora not saved as text file with an additional hidden .txt extension. Windows File Explorer will not show it by deafult settings.

monedula
  • 21
  • 1
0

I recently had the problem of deleting the tnsnames.ora from the path where I had it, my solution was to create an environment variable called TNS_NAME with the value the path where the tnsnames.ora file is located and ready

user12888860
  • 1
0

Yes, many years later. But still useful. PLSQLDEV: 14.0.6 InstantClient: 12c

Create an environment variable "ORA_HOME" pointing to your InstantClient installation. Inside it create "Network" and "Admin" and put the TNSNames there.

It worked for me after trying all other solutions.

Marcelo Lima
  • 1
-5

You most certainly have a databases tab in sql developer (all versions I've used in the past have this). Maybe check again? Perhaps, you're looking in the wrong location.

On a mac, the preferences is under "Oracle SQL Developer" (top left) -> Preferences -> Database -> Advanced -> section called Tnsnames Directory is where you specify the file.

On windows (going from memory so might have to search if this isn't correct) Tools -> Preferences -> Database -> Advanced -> section called Tnsnames Directory is where you specify the file.

See this image enter image description here

Justin Maat
  • 1,965
  • 3
  • 23
  • 33