-1

I have a tnsnames.ora and PL/SQL Developer. I want to make PL/SQL Developer establish a connection based on tnsnames.ora. What I read was that it is expected that tnsnames.ora would be found in $ORACLE_HOME/(...) but I don't have any Oracle product installed and this system variable is not set at all. I thought that maybe if I set this variable on some dummy location containing my tnsnames.ora file I will succeed but when connecting I get: 

Initialization error
Could not locate OCI dll
OracleHomeKey:
OracleHOmeDir:'my dummy path'

I know what OCI is. I used to connect php with my Oracle dbs using oci based modules. I have no idea where PL/SQL Dveloper takes this OCI from when the $ORACLE_HOME is not set manually by me. I checked if OID dll is maybe included in the installation directory in Program Files(I'm using Windows) but it wasn't there. Why does every tutorial for connecting using tnsnames.ora assumes that the user have instant_client installed? Did PL/SQL Developer install it for me under the hood?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Z.Szymon
  • 337
  • 1
  • 13
  • 2
    You don't have a oracle client installed or an oracle driver installed and therefore you can't connect to oracle as it takes more than just the tnsnames.ora file to make the connection happen. I 100% agree with you though. Connecting to Oracle is one of the most hair brained backwards poorly documented over engineered pain in the rears compared to literally every other database product in the world. "DSN is too simple and standardized, lets add a middle layer with a special file and an environment variable that doesn't always get set to complicate things" -oracle engineer, probably. – JNevill Mar 01 '19 at 14:00
  • What is the connection between the error (PL/SQL Developer unable to locate OCI.dll), tnsnames.ora and your statement about the difficulty of configuring an Oracle client you have not installed? What is your question exactly? – William Robertson Mar 02 '19 at 09:32
  • @WilliamRobertson I'd say that 'how to make plsql see tnsnames.ora' file. – Z.Szymon Mar 02 '19 at 13:02
  • What is "plsql"? [PL/SQL](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/overview.html) is Oracle's in-database programming language. [PL/SQL Developer](https://www.allroundautomations.com/plsqldev.html) is a commercial desktop development tool. tnsnames.ora is part of the Oracle full client. [Oracle Instant Client](https://www.oracle.com/technetwork/topics/winx64soft-089540.html) doesn't need a tnsnames file. PL/SQL Developer needs one or the other. – William Robertson Mar 02 '19 at 22:44

2 Answers2

2

True; you can put TNSNAMES.ORA anywhere on your disk. Then set the TNS_ADMIN environment variable which will point to that directory, e.g. 

M:\>set tns_admin
TNS_ADMIN=C:\0_Oracle_library

M:\>

Doing so, Oracle products will look for it in that location. Even if there are several TNSNAMES.ORA files (as every Oracle software product creates its own copy), only that one will be used.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
2

The error message is rather specific:

Could not locate OCI.dll

Unlike Java software (like SQL Developer) PL/SQL Developer depends on the Oracle Database Client software, the core being the OCI.dll library.

TNSNAMES.ORA isn't the problem. Instead, it's software that's missing on your PC. So you have to install a recent version of Oracle Database Client ... for Microsoft Windows .... Check the PL/SQL Developer documentation to figure out what release is required and whether you need a 32 or 64 bit version.

Codo
  • 75,595
  • 17
  • 168
  • 206
  • The funny thing is that when I'm specifing //host:port/sid I can connect. So pl/sql has some build in OCI.dll in it. – Z.Szymon Mar 01 '19 at 15:29
  • PL/SQL is a programming language. PL/SQL Developer is a commercial desktop development tool. – William Robertson Mar 01 '19 at 23:18
  • @M.Dawid *The funny thing is that when I'm specifing //host:port/sid I can connect.* - so is your question answered? When connected, what is the location of oci.dll (see Help > Support Info)? – William Robertson Mar 01 '19 at 23:49
  • @M.Dawid the PL/SQL Developer installation does not include any Oracle client. OCI.dll is installed in ORACLE_HOME/BIN (where ORACLE_HOME is defined in the registry under Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE), or the bin folder of the instant client. If you can connect without a tnsnames file then you must have an Oracle Instant Client somewhere. – William Robertson Mar 03 '19 at 11:08