Orafusion.com: Oracle Networking for CF 5.0

Oracle database design, development and administration

Data Management

Project Management

Cryptic crossword.

A bit about me.

Read my blog

Recent additions

Home

Oracle Net for ColdFusion 5 (and below):

Notes:

i) The information here pertains to CF versions 5 and below. For later versions of CF please check out my article on Oracle networking for CFMX

i) Although this article is geared to ColdFusion users, it should be of some use to those new to Oracle Networking

(Last Revised: 29 July 2002)

Here's the situation: you've installed your ColdFusion software, and ensured that the CF server can communicate with your Web server. The next step is to set up connectivity between CF and an Oracle backend. This tip outlines the steps you need to undertake in order to do this, and also explain a bit about what is going on. Note that this discussion deals only with the very commonly used local naming method of setting up Oracle networking - in which database server locations are set up through a tnsnames.ora file. More on this below. For completeness, though, I should point out that there are other methods such as Oracle Names and Oracle Internet Directory. I also assume that you are running CF on Windows 9.x,/NT/2000/XP, although some of the discussion is fairly generic and should apply to other OSs as well.

The mechanics of client-database interaction:

I assume that the Oracle database is up and running, and has the (database) server side networking software installed and functioning. If this is correctly done, there should be an Oracle process called a Listener running on the database server machine. This process "listens" for incoming client requests, such as those from the CF server, and passes them on to the database (or more correctly, a database server process) for processing. The Listener's role is limited to establishing initial contact between the client process and the database server process. Thereafter the database and client talk directly to each other, and the listener continues to listen for new requests. The aforementioned discussion holds when the database operates in dedicated server mode (one database server process per client), which is the case for most Oracle databases. Things work a bit differently when the database operates in shared server (version 9i) or multithreaded server (version 8i) mode, in which several clients share a database server process, but I won't go into that here.
Installing client software and configuring tnsnames.ora:

You first need to install the Oracle Net8 (for versions 8.x) or Oracle Net (for version 9.x) client software on the machine on which CF resides. As you've likely noticed, Oracle Corporation has a penchant for changing product names from version to version. So, to keep things simple, I'll assume we're dealing with an 8.x version of the product. The required software can be installed off the Oracle installation CD. You may need to get your DBA's help, if you are doing this for the first time

After this you will need to create a tnsnames.ora file. This is a text file that has a list of connection parameters for different databases. It basically contains information pertaining to the name or IP address of the machine on which the Listener runs, the listening port, the service name of the Oracle instance, and the network protocol to be used for the connection. Having said that, here's a typical entry from a tnsnames file:

accounts =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS =
       (PROTOCOL = TCP)
       (Host = 10.10.10.20)
       (Port = 1521)
     )
    )
    (CONNECT_DATA =
     (SERVICE_NAME = ora8i)
    )
  )

You will recognize the different pieces of information I mentioned above. No doubt, you will also have noticed that the syntax is somewhat tricky. So, although you can create a tnsnames file using a text editor, it is strongly recommended that you use Oracle's GUI tool - Net8 Configuration Assistant- for this purpose. Once you have a template created using this tool, you can proceed to edit it using any old text editor.

Back to the deconstructing the tnsnames entry. The "accounts" on the first line is the net service name, which can be used as an alias when requesting connections to the database. The stuff on the right hand side, called the connect descriptor, has all the information needed in order to initiate the connection. The first part of the connect descriptor contains information pertaining to the Listener(s) - addresses for multiple listeners can be specified (hence address lists), but that will not be discussed here. For our situation, the protocol would generally be TCP/IP. In the host parameter you can specify the Listener machine name instead of the IP address as shown above, provided there is a name resolution service available or a hosts file configured on the client machine. The default port for the listener is 1521, but this could, of course, have been changed at your site. To move on, the last two lines describe the database service that you want to connect to. This is generally the global database name. Although not shown above, one could also specify the instance name, should this be required. This completes the deconstruction of the entry shown above. You may need to get some or all of the above information from your friendly DBA.

Testing your connection:

Use the Oracle networking utility tnsping, which checks that the networking software is functioning, and that your tnsnames entry for the database points to a valid Listener process. To do this you would type tnsping net_service_name or tnsping service_name from the DOS or Unix command line, where the names refer to those explained in the previous section. If you can tnsping the database server, congratulate yourself - you've successfully set up connectivity between Oracle and the CF server machine. If not, read the next section for some basics on what could be wrong.

You can now proceed to set up your datasources. Go directly to the CF administrator if you intend to use native datasources. If you will be using ODBC, however, you first need to configure a datasource externally. Details on this process are in the final section.

Troubleshooting your connection:

If you are unable to connect there can be one of several things wrong. Here are some of the more common (Oracle) errors that can be encountered:
1. ORA-12154 TNS:could not resolve service name
  This means that the networking software could not locate the tnsnames file, or that the service requested has no entry in the file. To deal with the first case, make sure that the tnsnames.ora is in the expected directory (In Windows this is ORACLE_HOME/network/admin for Oracle 8i). The exception to this is if a registry key TNS_ADMIN has been set under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. If so, the tnsnames file is expected to be in the directory specified in TNS_ADMIN. To deal with the second case, simply add the missing entry according to the guidelines detailed earlier.
2. ORA-12198 TNS:could not find path to destination
  This generally means that the networking software could not find a path to the database server. Try pinging the remote server. It could be possible that one of the intermediate servers is down. This issue can usually be resolved by your network support staff.
3. ORA-12533 TNS:illegal ADDRESS parameters
  This error generally points to a malformed address section of the tnsnames entry. Check the entry and edit it as necessary.
4. ORA-12514 TNS:listener could not resolve SERVICE_NAME given in connect descriptor
  Have your DBA check that the database service name is entered in the listener parameter file (listener.ora), or that the database is configured to register itself automatically with the Listener.
5. ORA-12541 TNS:no listener
  As the message puts it so succinctly: there is no Listener. Check that the Listener is up and running on the remote machine. If it is, check that the correct listening port has been specified in the client tnsnames file.
Configuring ODBC datasources:

ODBC datasources must first be configured on Windows before they show up on the CF administrator screen. Here are the steps to do this:
1. Fire up the ODBC administrator in Windows. This is usually found under the administrative tools.
2. Click on the system DSN tab, and then click Add to bring up a list of drivers available on your system.
3. Select an appropriate Oracle driver from the list and click Finish. This brings up a setup box where you can enter configuration parameters.
4. Enter the following in the box:
  a) Datasource Name: this can be any name you choose.
  b) Server: this should be the net service name from your tnsnames file.
  The other fields can and (for security reasons) probably should be left blank.
5. Click OK to finish the process.
Once you've completed this, the new datasource should show up on the CF administrator screen. You can now verify connectivity between CF and Oracle.

Back to the top