Connecting to Oracle 7.3/8.0 Databases

You can configure Oracle 7.3/8.0 ColdFusion data sources for both Windows NT and UNIX using ODBC and native drivers.

Configuring Oracle 7.3/8.0 Options (Windows)

If ColdFusion Server Enterprise edition is installed on a Windows NT server, you can configure an Oracle data source using a Native driver.

Note Native Drivers: Oracle 7.3 and 8.0 Options (Windows)

The following table describes ColdFusion Native driver options for Oracle 7.3 and 8.0 native driver data sources on either Windows NT or UNIX.

Oracle 8.0 Native Database Driver Options
Option Description
Data Source Name A name for your data source.
Description Descriptive information about the data source.
Host String Enter the database alias you created using the Oracle Net8 Easy Config utility. To find the database alias for the database you want to connect to, you can use the Oracle Net8 Easy Config utility.

Configuring Oracle 7.3/8.0 Options (UNIX)

If ColdFusion Server Enterprise edition is installed on a Windows NT server, you can configure an Oracle data source using a Native driver.

Note Native Drivers: Oracle 7.3 and 8.0 Options (UNIX)

The following table describes ColdFusion Native driver options for Oracle 7.3 and 8.0 native driver data sources on either Windows NT or UNIX.

Oracle 8.0 Native Database Driver Options
Option Description
Data Source Name A name for your data source.
Description Descriptive information about the data source.
Host String Enter the database alias you created using the Oracle Net8 Easy Config utility. To find the database alias for the database you want to connect to, you can use the Oracle Net8 Easy Config utility.

Note ODBC Drivers: MERANT Oracle 7 and Oracle 8 Options (UNIX)

The following table describes ColdFusion driver options for the MERANT Oracle 7 and MERANT Oracle 8.0 ODBC driver s on UNIX.

The following table describes ColdFusion ODBC options for MERANT Oracle 7 and MERANT Oracle 8 ODBC drivers.

MERANT Oracle 7 and Oracle 8 ODBC Database Driver Options
Option Description
Data Source Name A name for your data source.
Description Descriptive information about the data source.
Connect String The client connection string designating the server and database you want to access.
Performance Include REMARKS in Catalog Functions -- Specifies whether the result column REMARKS for the catalog functions SQLTables and SQLColumns and COLUMN_DEF for the catalog function SQLColumns have meaning for Oracle.
Customization Enable scrollable cursors -- Enables scrollable cursors for the data source. Both Keyset and Static cursors are enabled.

Connecting to Oracle 7 and 8.0 Databases (UNIX)

Before ColdFusion applications can connect to Oracle 7 and 8.0 databases, you must install the Oracle client software.

You'll need to modify the /opt/coldfusion/bin/start script to include the ORACLE_HOME environment variable and add the $ORACLE_HOME/lib directory to the LD_LIBRARY_PATH. See "The /opt/coldfusion/bin/start Script" for an example.

Here is an example of a tnsnames.ora file:

scup=
    (DESCRIPTION=
        (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=scup)
            (PORT=1521))
        (CONNECT_DATA=(SID=WG73)))

The first line above, where it says "scup" is what the connect string name should be set to in the ColdFusion Administrator.

The HOST=scup line could also say the ip address, as in HOST=205.185.22.33.

The PORT=1521 line is the port the tcp listener is assigned to. You can look at the /etc/services file of the UNIX machine where the Oracle server resides to find out what it is.

The SID=WG73 is the identifier for the database. See your Oracle administrator. These settings are set when the database is installed.

When you've completed all the steps in this section, you will need to stop and restart ColdFusion services to reload the odbc.ini file.

The /opt/coldfusion/bin/start Script

#!/bin/sh
# start - setup environment and run ColdFusion servers
# This script should be run as root.
# Run as root, we are able to start the system registry deamon
# and then change to the ColdFusion userid to start the servers
# Set during install
CFHOME=/opt/coldfusion
CFUSER=nobody
# Sybase Open Client directory
SYBASE=/work/sybclient11.1;export SYBASE
# Oracle SQL *Net RDBMS directory
# See CFHOME/odbc/src/oracle for script to build library
ORACLE_HOME=/opt/oracle7;export ORACLE_HOME
# Set library search path
#
# NOTE: Add your database client library directory to the FRONT of this 
list
#
# Example: 
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$SYBASE/lib:/usr/dt/lib:/lib:/usr/
openwin/lib:$CFHOME/lib
# This is the list of variables that ColdFusion will see
# Add any special Database environment varaibles here
VAR_LIST="LD_LIBRARY_PATH CFHOME SYBASE ORACLE_HOME INFORMIXDIR 
INFORMIXSERVER II_SYSTEM"
#==========================================================
# You should not need to touch anything below this point
#==========================================================

Troubleshooting

If ColdFusion is unable to establish a connection to the Oracle 7.3 driver, try the following workarounds:

What to do if the connection test fails

If the basic information you entered in the Oracle Net8 Easy Config is correct, but the connection test fails, then a visit to your local Oracle 8 DBA is probably necessary. The first thing to do is check the basic connection information: hostname, SID, username, and password. You can do this using the Net8 Easy Config utility or by directly inspecting the tnsnames.ora file.

In addition, on UNIX, make sure you have the Oracle client library and ORACLE_HOME defined in the coldfusion/bin/start script.

In some cases, connection problems can be solved by clearing your system of the Oracle 8 Client and reinstalling it. Unfortunately, Oracle does not provide an uninstaller for the Oracle 8 Client; you must uninstall manually.

Connecting to Oracle 8.0.x through ODBC (UNIX)

To use Oracle 8.0x databases with ColdFusion on UNIX, you need to build the libclntsh.so shared object file.

Note To build a version of libclntsh.so for Oracle 8.0.x:
  1. From the shell, set ORACLE_HOME, CFHOME, and LD_LIBRARY_PATH as set in the start script.

    ORACLE_HOME should be set to the location of the Oracle client libraries. CFHOME should be set to the location of your ColdFusion application server. LD_LIBRARY_PATH needs to be set the same as in your start script.

    For example:

    CFHOME=/opt/coldfusion;export CFHOME
    ORACLE_HOME=/opt/oracle8;export ORACLE_HOME
    LD_LIBRARY_PATH=/lib:/usr/dt/lib:/usr/openwin/lib:$CFHOME/
    lib:$ORACLE_HOME/lib;export LD_LIBRARY_PATH 
    
  2. From $CFHOME/odbc/src/oracle run the script./genclntsh8.

The script will generate an new libclntsh.so in CFHOME and place it in $CFHome/lib.

Note Oracle 8 users will have the original libclntsh.so library in the $ORACLE_HOME/lib directory. Therefore when creating the LD_LIBRAY_PATH in the $CFHOME/bin/start script, the $CFHOME/lib directory, containing the correct library, must be on the LD_LIBRARY_PATH before $ORACLE_HOME/lib. Otherwise, the original Oracle version will be loaded, resulting in an error.

Troubleshooting the Oracle 7.3 Native Driver (Windows)

When using the Oracle 7.3 Native Driver on Windows 95/98 or Windows NT you may get this error:

Error Diagnostic Information 
Oracle Error Code = 0 
Internal error: The data access manager failed to initialize the Oracle 
environment. 

The error occurred while processing an element with a general identifier 
of (CFQUERY), occupying document position (3:1) to (3:50). 

You may experience this problem with the Oracle 7.3 Native Driver. This can happen in several situations. Either you cannot verify the connection or you get this error when running a CFQUERY. The most likely cause of this problem is a duplicate copy of the OCIW32.DLL file.

Note To diagnose and fix the problem
  1. Make sure that SQL*Net 7.3.4.0 or higher is installed on your server.
  2. Make sure that your winnt\system32 directory contains the file ociw32.dll.

Configuring the Oracle 8 Client (Windows, UNIX)

This section includes the typical configuration steps you need to perform to use the Oracle 8 native database driver on either UNIX or Windows NT. These procedures were written using version 8.0.4.0.0 of the Oracle 8 Client.

Before you get started, make sure you have the following information handy:

Note To use the ColdFusion native driver for Oracle 8:
  1. Install the required client software
  2. Use the SQL Net Easy Configuration utility to create a database alias. The UNIX version of this utility can be found in $ORACLE_HOME/bin/net8wiz.sh.
  3. Create the data source in the ColdFusion Administrator, Native Drivers page.
  4. Edit the coldfusion/bin/start script to include the following values:
Note Installing the Oracle 8 Client
  1. Install the Oracle 8 Client software.
  2. Select the Database administrator or Application user option in the following dialog. In this example, we chose Application user.
  3. Step through options involving stopping Oracle services that may be running on your system, and choosing whether to install online documentation.
Note Running the Oracle Net8 Easy Config utility

This step creates a database alias you use to reference the Oracle database when creating the data source in the ColdFusion Administrator. The process of creating the database alias writes all of the database connection information to a configuration file called tnsnames.ora.

  1. Open the Oracle Net8 Easy Config utility. The icon is found in your Oracle for Windows NT program group. OnUNIX, this utility is found in $ORACLE_HOME/bin/net8wiz.sh.
  2. Enter a New Service Name and click Next.

  3. In the resulting dialog, select TCP/IP as the networking protocol to connect with and use the Oracle 8 database you want to use in your ColdFusion application.
  4. In the dialog that appears, you now need to enter the host name of the server where the Oracle 8 database resides. We took the default for the port number.

  5. After entering a hostname, you enter the Database SID, which identifies your specific Oracle database instance. The default is ORCL, but your database SID might be different. See your database administrator (DBA) for this information.
  6. In the next dialog, you test the database service you have created. To test the connection to the Oracle database, you'll need to enter a valid username and password for accessing the Oracle database. If you don't have this information, see your DBA.

    Now you need to create the data source in ColdFusion.

Note Creating the data source in ColdFusion:
  1. Open the ColdFusion Administrator to the Data sources, Native Drivers page.
  2. Enter a data source name and select the Oracle 8 native driver from the drop down list.
  3. When you click Add, ColdFusion opens the configuration page for the data source. Here you enter information that tells ColdFusion where to find the database. The options that are most important for a successful connection are:
  4. Once you have created the data source, open the Verify Data Source page in the Administrator to verify that ColdFusion can connect to the Oracle 8 database.