Oracle has recently released Oracle 10g Express Edition (XE), a free edition of their database server. While Macromedia supports connections to Oracle 10g from ColdFusion MX 7.01 using the built-in JDBC drivers that ship with ColdFusion, this version of Oracle is not officially supported with earlier versions including ColdFusion MX 7 or ColdFusion MX 6.x.

If you would like to connect an earlier version of ColdFusion MX to Oracle 10g, or would like to try an alternate driver if you're already using CFMX 7.01, then here is how to configure the 10g JDBC driver provided by Oracle for use with ColdFusion. This driver is often refered to as the Oracle Thin Client. Sometimes driver behavior varies between two vendors, so its often very helpful to try alternate driver versions.

  1. Obtain the Oracle 10g Release 2 JDBC driver from Oracle
    • If you already have Oracle 10g XE installed, then you can find the appropriate driver at oraclexe_rootapporacleproduct10.2.0serverjdbclibojdbc14.jar
    • You can also download the 10g R2 JDBC driver here.
  2. Copy the ojdbc14.jar to the standard J2EE web application custom library location for ColdFusion.
    • Server Configuration: CFusionMX7_rootwwwrootWEB-INFlib
    • JRun Configuration: JRun4_rootserverscfusioncfusion-earcfusion-warWEB-INFlib
  3. Restart the ColdFusion MX server
  4. Browse to the ColdFusion Administrator page for Data Sources.
  5. Choose a datasource name and select the driver option for Other. This datasource name is the alias you will use when specifying the value for the datasource attribute of CFQUERY.
  6. Provide the following information for the various fields:
    • JDBC URL: jdbc:oracle:thin:@[host]:1521:XE
      • The XE part here is the Oracle SID you wish to connect to. The SID defaults to XE but it may have been changed. Check tnsnames.ora in oraclexe_rootapporacleproduct10.2.0serverNETWORKADMIN
    • Driver Class: oracle.jdbc.OracleDriver
    • Driver Name: (optional: leave empty or provide custom name)
    • User Name
    • Password

  7. Click Submit on lower right
  8. If all goes will you will get a Verified message in green, but if there is any problem you will get a detailed error message in red font. The error is usually very precise and should help resolve the problem.
  9. You are now free to use this new ColdFusion Data Source in your web application.
  10. If using ColdFusion MX 7.01 you may want to configure another datasource for comparison using the Driver Option listed as "Oracle" in the drop down list. This will use the built-in driver supplied by ColdFusion that supports Oracle 10g.

If you are having trouble connecting from ColdFusion MX server, its often useful to try a pure Java test. To that end, I've created a Java program that will test the connection to the database outside of ColdFusion. The Java test executes the query the same way ColdFusion would when using the Oracle driver from Oracle, so its an apples to apples comparison. If the Java test produces the same error, at least you know that the problem is not in ColdFusion.

The self-contained test is here: JDBC Test for Oracle 10g.

To use it:
  1. download & unzip anywhere on the machine
  2. edit to set your host, username and password
  3. edit compileTest.bat and runTest.bat to set the JAVA_HOME variable for your system.
    (e.g., C:CFusionMX7 untimejre or C:j2sdk1.4.2_05)
  4. double click compileTest.bat to compile the Java source file
  5. double click runTest.bat to run the test

When running the test a command window will pop up and tell you to press any key to continue. When you do so, the program will attempt to load the database properties file, the database driver, make a database connection, and then run a query SELECT EDITION,VERSION FROM V$INSTANCE as a simple test.

You will either get a successful result or the same error as ColdFusion.