Unable to connect to Oracle 9 (Powerbuilder 12.5) - ORA-12154 error

Question

after 10+ hours of trying I'd like to ask for your help.

I'm trying to connect to Oracle database "O90 Oracle 9i" in Powerbuilder 12.5.

My machine is a little bit messy regarding Oracle (I had couple if instances and I'm not able to perfectly remove them) so I set up a Virtual Machine to test it in clean environment.

That's what I do on the clean machine:

1. Installed Powerbuilder 12.5
2. Installed ERP software (where I try to connect). The ERP software distribution comes with \dbms\oracle\bin|network folders.
3. Added "C:\facterp\dbms\oracle\bin" (one of the folders from point 2) to PATH variable

Now I'm able to connect to server with SQLTools (apparently it reads data from tnsnames.ora located in one of the folders from point 2). Inside the tnsnames.ora:

GERP_POLAND = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.ip.given.here)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl)

However when trying to connect from Powerbuilder I get an error ORA-12154.

I thought that maybe PB uses a different tnsnames.ora file (if I remove or modify the file the error is the same). However there is no other file. From what I know Powerbuilder uses ORACLE_HOME variable for tnsnames.ora location. I added the variable but the issue remains the same.

My questions:

1. Can it be a 32bit vs 64bit issue? I know I should install 32bit Oracle client to work with 32bit PB but I don't install any client in this case. I just use provided files. That's enough for SQLTools but not for PB.
2. The TNS includes a line: (SERVICE_NAME = orcl). Does that mean I need to configure and use listener.ora?

I admit I haven't figured out the whole concept of different ways of connecting yet (flat ora file/services/ect.)

Exactly the same happens on my "messy" machine.

I'll very gretefull for showing me the direction here.

Thank you,

Kris.

---

An update:

I expanded the ORACLE_HOME variable from:

C:\facterp\dbms\oracle

to

C:\facterp\dbms\oracle\network\admin\oracle

Now Powerbuilder connects to the database but when I expand the "tables" treeview there are no tables. When I expand the "users" treeview it crashes whole application.

It seems logical that the change I implemented (longer ORACLE_HOME path) shouldn't work - there are some ORACLE_HOME% references in other places...

Answer 1

ORA-12154 usually means that TNSNAMES.ORA doesn't contain database you're connecting to, or that tool you use can't locate TNSNAMES.ORA.

Try to create the TNS_ADMIN environment variable which points to directory which contains TNSNAMES.ORA file. For example, on my PC, I set it as

TNS_ADMIN=C:\0_Oracle_library

Every Oracle software will now know where to look at. Because, each of them contains its own \network\admin or similar directory which has its own TNSNAMES.ORA. Whenever you add a new database, you have to maintain ALL of those TNSNAMES.ORA files (which becomes tedious). But, with TNS_ADMIN, you maintain ONLY ONE copy of that file and - no problem.

So, try it. It doesn't have to be a new directory, you can use existing one, such as

TNS_ADMIN=C:\facterp\dbms\oracle\bin

(or wherever your TNSNAMES.ORA file is).

Answer 2

I forgot to mention that I already tried adding the TNS_ADMIN variable. Without any change.

However there is clearly something I'm missing:

In Powerbuilder when attempting the connection I provide:

*profile name* (probably has not meaning for this conversation, just internal name)
*server*: server.ip.given.here/orcl
*login&password*

I can't see anything that Oracle should look for in tnsnames.ora file. Should it look for "GERP_POLAND" from the tnsnames.ora below? If yes I can't see any reference to it in my connection atempts.

GERP_POLAND = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.ip.given.here)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl)

If I modify the server.ip.given.here/orcl to server.ip.given.here/orclblabla I get the same result (seems like it's connected - green check sign) but it's not connected.

By the way - using the PowerBuilder tracking information:

SELECT OWNER, TABLE_NAME, TABLE_TYPE FROM SYS.ALL_CATALOG --> empty result SELECT USERNAME FROM SYS.ALL_USERS --> crash of the application

Anyway - I feel like I must be missing some basic stuff here...

Many thanks for your help.