PowerBuilder 12.5连接Oracle 9i数据库遇ORA-12154错误及后续异常问题求助

Hi Kris, let's walk through your problems step by step to get your PowerBuilder (PB) 12.5 connected to Oracle 9i properly.

1. 32-bit vs 64-bit Compatibility: The Likely Root Cause of ORA-12154 #

First, you’re absolutely right about the 32/64-bit mismatch being a critical factor here. PowerBuilder 12.5 is a 32-bit application, which means it can only load 32-bit Oracle client libraries.

Your SQLTools works because it’s probably a 32-bit tool that can use the ERP-provided Oracle files, but if those files are 64-bit, PB (32-bit) won’t be able to load them—this directly causes the ORA-12154 error (since PB can’t find/initialize the Oracle client to resolve the TNS alias).

How to Verify: #

• Navigate to C:\facterp\dbms\oracle\bin and right-click oci.dll → Properties → Details. Check if it’s labeled as a 32-bit or 64-bit file.
• If it’s 64-bit, you’ll need to replace it with a 32-bit Oracle 9i client installation (or compatible 32-bit Oracle client libraries). You can grab a 32-bit Oracle 9i client installer from Oracle’s archive, or find a trusted 32-bit package that matches your ERP’s requirements.

2. TNS Configuration & listener.ora Clarification #

Great question about listener.ora—let’s clear this up:

• listener.ora is a server-side file: It configures the Oracle listener that accepts incoming connections on the database server. You don’t need it on your client machine (PB or SQLTools).
• tnsnames.ora is client-side: This is what your client tools use to resolve TNS aliases like GERP_POLAND to the server’s address. Since SQLTools works, your tnsnames.ora is correctly formatted—so the issue isn’t the TNS entry itself, but PB not finding/using it properly.

3. Fixing ORACLE_HOME & Environment Variables #

Your incorrect ORACLE_HOME path (pointing to network\admin\oracle) let you connect but caused crashes because it broke the expected directory structure for Oracle client components. Here’s the correct setup:

Correct Environment Variable Settings: #

• ORACLE_HOME: Set this to the root directory of your Oracle client files (the one containing bin and network folders). For your setup, this should be C:\facterp\dbms\oracle.
• TNS_ADMIN: Set this to the folder where your tnsnames.ora is located. For example, if it’s in C:\facterp\dbms\oracle\network\admin, set TNS_ADMIN=C:\facterp\dbms\oracle\network\admin. This ensures PB (and other tools) know exactly where to find the TNS configuration file.
• PATH: Move %ORACLE_HOME%\bin to the top of your system PATH. This ensures PB uses the correct Oracle client libraries first, avoiding conflicts with any leftover Oracle files from previous installations.

Verify the Configuration: #

Open a command prompt (cmd) and run these commands to confirm everything is set correctly:

echo %ORACLE_HOME%
echo %TNS_ADMIN%
tnsping GERP_POLAND

If tnsping succeeds (returns "OK"), your client configuration is valid—now try connecting from PB again.

4. Resolving Post-Connection Issues (No Tables, Crashes) #

The crashes and missing tables were caused by the invalid ORACLE_HOME path. Once you fix the environment variables:

1. Open PB’s Database Profile and create a new Oracle connection.
2. Select the appropriate driver (e.g., "Oracle 9.0.1" for Oracle 9i).
3. In the Connection tab:
   • For Server, use either your TNS alias (GERP_POLAND) or paste the full connection string directly (to bypass TNS lookup entirely):

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

   • Enter your database username and password, then click Test Connection.
4. If tables still don’t show up:
   • Check if your database user has permissions to view the tables (ask your DBA if needed).
   • In PB’s Database painter, go to Design → Options and ensure "Show System Tables" isn’t checked (unless you need them), and verify the default schema is set correctly for your user.

Final Notes #

Since your local machine has leftover Oracle installations, I recommend using the clean VM for testing first—once you get it working there, you can replicate the exact environment variables and client setup on your local machine (making sure to disable/uninstall any conflicting Oracle instances or clean up their PATH entries).

内容的提问来源于stack exchange，提问作者Kris