ORA-12154 error with PL/SQL Developer - not with SQL Developer

[LoriS]

I realize this subject has come up several times in the past, and I've tried some of the suggested solutions, but they have not worked in this case.

I am using a laptop provided by a client to login to their network instead of using VPN and logging in from my personal laptop (security reasons).

I am able to connect to the database using SQL Developer, but receive the ORA-12154 error when using PL/SQL Developer.

Support Info is below.

Thanks for your help!

----------------------------------------------

PL/SQL Developer
Version 10.0.5.1710
01.97376 - 5 user license
Windows 7 (64-bit) 6.1 Build 7601 (Service Pack 1)

Physical memory : 4,072,464 kB (1,728,064 available)
Paging file : 4,194,304 kB (4,194,304 available)
Virtual memory : 2,097,024 kB (1,768,656 available)

Parameters
C:\Program Files\PLSQL Developer\plsqldev.exe

Preferences
Session mode: Multi
OCI Library: C:\Oracle\instantclient_12_1\oci.dll
Use OCI7: False
Allow Multiple Connections: True

Preference Files
C:\Users\stebbla\AppData\Roaming\PLSQL Developer\Preferences\default\Default.ini
C:\Users\stebbla\AppData\Roaming\PLSQL Developer\Preferences\STEBBLA\default.ini

Plug-Ins
*Active Query Builder (C:\Program Files\PLSQL Developer\PlugIns\ActiveQueryBuilder.dll)
*PL/SQL Documentation (plsqldoc) (C:\Program Files\PLSQL Developer\PlugIns\plsqldoc.dll)
*Red Gate Deployment Plug-In (C:\Program Files\PLSQL Developer\PlugIns\RedGate.dll)
*Version Control Interface 1.2 (C:\Program Files\PLSQL Developer\PlugIns\VCS.dll)
(* is Active)

Homes
OraClient11g_32 (C:\Oracle\Ora11g_32\Client11g)
OraClient12Home1_32bit (C:\Progra~2\Oracle\Oracle12c_32\product\12.1.0\client_1)

DLLs
C:\Progra~2\Oracle\Oracle12c_32\product\12.1.0\client_1\bin\oci.dll

TNS File
C:\Oracle\TNSNAMES\tnsnames.ora

Using
Home: OraClient12Home1_32bit
DLL: C:\Oracle\instantclient_12_1\oci.dll
OCI: version 11.1

 

[Marco Kalter]

You will need to review the definition of the database you are trying to connect to in the tnsnames.ora file (C:\Oracle\TNSNAMES\tnsnames.ora).

 

[William Robertson]

Is the connection definition in SQL Developer using TNS? I think up until recently it only used JDBC and therefore would not have looked at the tnsnames.ora.

 

[LoriS]

Hi guys,

Thank you for your response!

I am able to connect from SQL Developer version 4.0.0.13 using the same TNSNAMES.ora file specified for PL SQL Developer.

Do you have any other ideas?

I really appreciate your help!
~Lori

 

[Marco Kalter]

Can you check if you can connect to the same database with C:\Progra~2\Oracle\Oracle12c_32\product\12.1.0\client_1\bin\sqlplus.exe?

On the Windows Command Prompt, do the following:

c:
cd \Progra~2\Oracle\Oracle12c_32\product\12.1.0\client_1\bin
sqlplus /@

 

[LoriS]

Hi Marco,

I tried this, using the following convention:
sqlplus myusername/mypassword@Host/servicename

I received the following:

ORA-12592: TNS:bad packet

Enter user-name:

So then I entered myusername and mypassword as per the prompts, and received the following:

ORA-12560: TNSrotocol adapter error
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Any further ideas?

Thanks so much for your help with this!
~Lori

 

[Marco Kalter]

It seems that the Oracle Client has a problem. I see that you have 2 clients installed. Can you start PL/SQL Developer without connecting, go to Tools > Preferences > Oracle / Connection, select "OraClient11g_32" from the Oracle Home selection list, restart PL/SQL Developer, and try again?

 

[LoriS]

Hi Marco,

I've tried this, and still get the same error: ORA-12154: TNS:could not resolve the connection identifier specified.

I also tried:
1) downloading the 32-bit Instant Client 12.1
2) changed the oracle home setting in the params.ini file to:
# oraclehome=C:\Oracle\instantclient_12_1
3) changed the OCI Library to: C:\Oracle\instantclient_12_1\oci.dll

This has worked for me on other workstations, but not on this one.

I also tried turning off the Windows firewall and connecting, but this also did not work.

I am hoping you are aware of some other parameter or setting that is needed? I am also checking with our DBAs and sysadmins to see if there is something that needs to be enabled on their end.

Any further assistance you can provide is greatly appreciated!

~Lori

 

[LoriS]

Marco,

I worked with an internal data administrator and copied his sqlnet.ora, ldap.ora, and tnsnames.ora files that he uses for Toad.

This did the trick! I am now able to connect to the databases using PL/SQL Developer.

Thanks so much for your help with this!

Happy Holidays!
~Lori