# 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.

| 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.

| 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.

| 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.

- Make sure you can connect to the Oracle server from Solaris with sql*plus before you try to connect from ColdFusion.
- Make sure you have the Oracle listener running
- Make sure you have a `tnsnames.ora` file set up. For oracle 7.3 it is in the `/var/opt/oracle` directory.

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:

- You may need to move the location of the ORACLE_HOME/lib to the end of the LD_LIBRARY_PATH in the start script. Make sure that the $ORACLE_HOME directory has a /lib directory below it. In that lib directory there should be a libclntsh.so file.
- Make sure that the listener port number is the the right one you are pointing to in `tnsnames.ora` file.
- Check in `$ORACLE_HOME/lib` directory for the existence of the `libclntsh.so` file. If it does not exist you need to reinstall the oracle client software.
- Oracle 7.1 users must build the Oracle client shared library (`libclntsh.so` for Solaris) before the Oracle driver can be used. Run the `genclntsh` script found in the `odbc/src/oracle` directory to build the library.

### 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.

| 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.

| 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 `y`our `winnt\system32` directory contains the file `ociw32.dll`.
   - If you have a copy in `winnt\system32`, rename it and then stop and start the ColdFusion services.
   - If you do not have a copy in `winnt\system32`, search your hard drive for the file. If you find a copy anywhere other than `orant\bin` or `cfusion\bin` rename the file then restart ColdFusion services.

## 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:

- The name of the host system where the Oracle database resides.
- The System Identifier (SID) for your Oracle 8 database.
- A login ID and password for connecting to the Oracle 8 database.

| 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:
   - The database variable `ORACLE_HOME`
   - The library path set in `LD_LIBRARY_PATH`

| 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.

| 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.

| 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:
   - Host string -- Enter the exact database alias you created using the Oracle Net8 Easy Config utility.
   - ColdFusion Login username and password -- These options appear when you click the CF Settings button. The username and password are the same as those used in the Oracle Net8 Easy Config connection test. If you don't know what the username and password should be, see your Oracle 8 DBA.

     You may want to avoid setting a username and password here for security reasons. If you don't set a name and password, each CFML database call must specifically define a username and password.

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.