Problems sometimes arise when attempting to connect to an Oracle database. This is almost always due to a misconfiguration in the user’s environment. The following suggestions can often help detect and overcome such problems.
Ensure you can connect to the database with the service name, user name, and password using SQL*Plus.
Ensure that you have the correct version of the Oracle client software installed. Oracle 8.1.5 or newer is recommended. Note that many clients have had problems if they have both 8.0.4 and 8.1.x installed on the same computer.
Ensure that your ORACLE_HOME environment variable is correctly set—see the Oracle documentation for details on this. This is required for some specific versions of Oracle 8i, and may be required even if SQL*Plus appears to operate correctly without it.
If you have had older versions of the Oracle client software installed, make sure that your PATH variable has the current version’s Oracle directory first, before any other Oracle software, including the WebDB package.
It is sometimes helpful to define an environment variable named ORACLE, with the same value as the ORACLE_HOME variable. With some installations, it often helps to ensure that the variable named ORACLE is not defined.
When running on UNIX, the following environment variables should be defined:
|
Variable |
Contents |
Sample Value |
|
ORACLE_BASE |
Top level of directory into which Oracle client software is installed. |
/opt2/oracle8i/app/oracle |
|
ORACLE_HOME |
The Oracle product directory. |
/opt2/oracle8i/app/oracle/product.8.1.5 |
|
ORACLE_SID |
The system ID for the host’s database instance. |
FME |
|
LD_LIBRARY_PATH |
A list of directories which will be searched for shared objects. This list must include the FME_HOME path, as well as the lib subdirectory of ORACLE_HOME. |
${LD_LIBRARY_PATH}: |
In most cases, the ORACLE_SERVER_NAME and ORACLE_DATABASE keywords should be left with blank values, with the ORACLE_DATASET keyword containing the Oracle service name of the database.
To test connectivity to Oracle, change directory in to the FME installation directory and execute the following command. On UNIX, it is necessary to first define the environment variables listed above. (The ora8ilist script in the UNIX FME’s installation directory sets up the required environment variables prior to running the actual ora8ilist executable.)
ora8list <service> <username> <password> -
For example,
ora8list BONES scott tiger -
Note that the last word must be a hyphen (-). When executed, the list of tables with a geometry column should be output.
The ora8list program is a simple program which connects to the database and executes a single SQL query. If this program cannot be made to work, then FME will not be able to connect to the database either. Safe Software will provide the source code for this program to any party that continues to have problems making a connection to their database to assist in locating the problem with their Oracle configuration.