TechnicallyChris.com

As you may find after upgrading to ColdFusion MX, and/or deciding that Oracle is the right database for you, ColdFusion MX Standard Edition does not come with drivers to connect with Oracle. You can buy drivers, you can use other methods to connect, but there’s nothing built-in.

The free solution that I find most developers using is going with the Oracle JDBC drivers. While JDBC is much faster than ODBC, there are drawbacks. The one that usually causes problems is that the JDBC drivers do not support Oracle REF Cursors.

If you decide that JDBC is the way to go for you, I’m not going to reinvent the wheel built by others by telling you how to install and configure JDBC, it’s been done 100 times over, and search for the various things you’ll need will likely turn up the results. But let me make it very easy for you, I’ll link to exactly what you need, and give you some details they may not.

First, read about what Adobe says on the matter, and get their instructions:
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_18344
Now in the article, they’re going to tell you about classes12.jar, but not where to get it if you don’t have it. That’s where I come in: Click Here for your Classes12

The installation is really as easy as Adobe makes it seem. Place classes12.jar where it needs to go, point to it using ColdFusion Administrator, and restart the ColdFusion service.
The one “gotcha” that I’m going to help you with to this is that unless you are familiar with Oracle, you may not know exactly how the datasource’s JDBC URL should be written. The example on Adobe’s site is this:

jdbc:oracle:thin:@ ps_db : 1521 : pubs

If we consider the colon the parameter separator, than the first three parameters are static. JDBC, Oracle, and Thin should never change. Now the fourth parameter, @ps_db, is the name of your Oracle server and you will need to change this part of the line. The firth parameter is the port that Oracle is listening on for your database, and the last parameter is the name of the database. So if your server is oraserver, the port is 1522, and the database is cfprog, then your URL would be:

jdbc:oracle:thin:@oraserver:1522:cfprog

If you’re unsure about the server name, port, or database name, you have a few options. The first, and easiest, would be to find your DBA – he or she will know for certain what these details are. If you’re using Oracle XE (as I’ve described in my previous post), your URL will probably be:

jdbc:oracle:thin:@localhost:1521:xe

Still unsure of the details? If you have the Oracle Client installed on one of your computers, you can try using TNSPING to get this information. The TNSPING tool, if installed on your computer, will allow you to ping the database by its name and get these details. To test it out, drop to a command line and enter “TNSPING ” (without the quotes or brackets) and press enter. If it can find the database using a name server or TNSNAMES.ORA file, it will tell you what you need to know. In the results you will see the HOST, PORT, and SID. These are Server, Port, and Database, respectively. Using these values, you should be able to setup your JDBC URL.
My last recommendation for setting up the JDBC URL if you cannot determine the information you need would be to look at your TNSNAMES.ORA file. This file contains databases that your Oracle client is configured to connect to. Note that this isn’t the only way, and may not be used in your company. Search your system for TNSNAMES.ORA. Open each file found, and look for your database. Find it? Great! Just use the HOST, PORT, and SID just like I described in the last paragraph to form your JDBC URL.

If you still need help trying to connect to your database, please feel free to add a comment to this post and I will try my best to help you.

Chris ColdFusion ColdFusion, Oracle