Connecting to Oracle: Troubleshooting


(c) Lubos VNUK, 2003-01

Foreword

Gupta Team Developer can be configured to use its native router to connect to Oracle database. The router exploits OCI (Oracle Call Interface) and there are a few catches that may occur while establishing your first connection. In this article I tried to do a write-up of the problems you may encounter (or have encountered and keep wondering why). It should be used only as a supplement to the official Gupta documentation (Connecting Gupta Objects to Databases). It does not describe the OLEDB or ODBC connectivity options. It presumes you have former Oracle connectivity experience.

Important note: Since TD2005 PTF1 it does not implicitly try to use ODBC first anymore and thus if you have TD2005 PTF1 or later, please ignore the paragraph 2.4 completely.

1. Oracle Client Installation

1.1. Run sqlplusw.exe from the command prompt to check if the DB is accessible. Should sqlplusw.exe be missing, install Oracle Client. Should only the connection fail, correct the tnsnames.ora and sqlnet.ora in the Oracle\Network\ADMIN\ directory.

1.2. Check the Windows Registry. Should these keys:
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
"ORACLE_HOME"="C:\\Progra~1\\Oracle"
"ORACLE_HOME_NAME"="oraHomeXYZ"
"ORACLE_GROUP_NAME"="Oracle - oraHomeXYZ"

be missing, insert them (use your Oracle path and XYZ version).

The problem here may appear after upgrading Oracle client. Oracle uses
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES]
instead of the keys mentioned above whereas Gupta router still wants them.

1.3. Check the Environment variables:

PATH=...;C:\Progra~1\Oracle\bin;...
NLS_NUMERIC_CHARACTERS=.,

The NLS variables can be set using either Environment variables or in the Windows Registry in the Oracle section but I recommend using ALTER SESSION statement directly in the application.


2. Gupta/Centura Connectivity Installation

2.0. Check if the router to Oracle (sqlora32.dll) is in the application directory. If not, install (TD CD/Connectivity Components/Native DB Routers/SQLRouter for Oracle) or copy one.

2.1. Rename ociw32.dll in your Centura/Gupta application directory as well as in your Windows directory (if you find any) and keep the only active one in Oracle\bin. As an alternative, ORAOCI registry key could be added in [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE] (see the Online book Connecting Gupta Objects to Databases).

2.2. Identify which sql.ini is being used. If the SQLBASE environment variable is set, search that path only. Otherwise, search the current directory, SQLBASE directory of current drive, root directory of the current drive, every directory defined in the PATH environment variable, in that order. Keep in mind here that the current directory is usually changed during the File/Open dialog operation, e.g. if you want to connect to a DB after opening an SQL file in SQLTalk, expect the current directory to be the one that the file was opened from.

2.3. Look for the following keys in sql.ini:

[win32client.dll]
comdll=sqlora32

[oragtwy]
remotedbname=ALIAS_NAME,@ORACLE_CONNECT_STRING

The ALIAS_NAME is an up-to-8-characters-long name you will use as the database name throughout the Gupta tools, i.e. in SQLWindows as the SqlDatabase variable value or in SQLTalk:
connect ALIAS_NAME USER_NAME/PASSWORD;

The ORACLE_CONNECT_STRING is processed by Oracle's network subsystem in the order defined by the NAMES.DIRECTORY_PATH key in Oracle's sqlnet.ora file. This is usually a TNS name from Oracle's tnsnames.ora file.
If you wanted to skip tnsnames.ora, you might yant to use the following syntax (single line):
remotedbname=ALIAS_NAME,@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER_HOST_NAME)(PORT=SERVER_PORT_NUMBER)))(CONNECT_DATA=(SERVICE_NAME=LISTENER_SERVICE_NAME)))
or
remotedbname=ALIAS_NAME,@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER_HOST_NAME)(PORT=SERVER_PORT_NUMBER)))(CONNECT_DATA=(SID=DB_SID_NAME)))

2.4. [This paragraph is valid only for until TD 2005 PTF1] Should the database ALIAS_NAME be the same as one of the ODBC32 DSNs (Data Source Names), change either the DSN or the ALIAS_NAME (or rename sqlodb32.dll and remove gupta.ini from the application directory). Gupta applications (except for SQLTalk) always try to connect first through the ODBC router as default even if it is not in the [win32client.dll].


3. Restart the computer


P.S.: CCP and the ROLLBACK trick

Cursor context preservation (CCP) preserves any result sets that you retrieved during a transaction, as well as the cursors associated with those result sets, even after you do a COMMIT.
To turn CCP on (per SQL handle), include the statement: Call SqlSetParameter(hSql, DBP_PRESERVE, TRUE, )

If you roll back the transaction, all open result sets are lost even if CCP is ON.
This means that any time you rollback a transaction you will have to re-prepare all your open cursors. What a pain! The good news is:
I have a trick for you if you want to avoid this.

Instead of:

Call SqlPrepareAndExecute(hSql, 'ROLLBACK')

use:

Call OraRollback(hSql)

 Function: OraRollback
Description: Oracle Rollback with Cursor Context Preservation
Returns
Boolean:
Parameters
Sql Handle: hSql
Actions
Return SqlOraPLSQLPrepare( hSql, "BEGIN ROLLBACK; END;" ) AND SqlOraPLSQLExecute( hSql )