Configuring the Oracle 10g Database Driver for ColdFusion MX

Viewing By Entry

Configuring the Oracle 10g Database Driver for ColdFusion MX

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.

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_root\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar
- You can also download the 10g R2 JDBC driver here.
Copy the ojdbc14.jar to the standard J2EE web application custom library location for ColdFusion.
- Server Configuration: CFusionMX7_root\wwwroot\WEB-INF\lib
- JRun Configuration: JRun4_root\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\lib
Restart the ColdFusion MX server
Browse to the ColdFusion Administrator page for Data Sources.
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.
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_root\app\oracle\product\10.2.0\server\NETWORK\ADMIN
- Driver Class: oracle.jdbc.OracleDriver
- Driver Name: (optional: leave empty or provide custom name)
- User Name
- Password
Click Submit on lower right
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.
You are now free to use this new ColdFusion Data Source in your web application.
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:

download & unzip anywhere on the machine
edit database.properties to set your host, username and password
edit compileTest.bat and runTest.bat to set the JAVA_HOME variable for your system.
(e.g., C:\CFusionMX7\runtime\jre or C:\j2sdk1.4.2_05)
double click compileTest.bat to compile the Java source file
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.

posted on 14 November, 2005 at 12:01 PM.
ColdFusion | Comments (9)

Comments

Hi Steven,

At step 2 you can also store ojdbc.jar/zip in another location and use CF Admin's 'Java and JVM' section to add this file to the ColdFusion Class Path.

This has the benefit of being able to keep all your JARs in one location so other apps can use them also.

The only gotcha here is that I have found I had to include the JAR directly in the CF class path - just adding the directory did not work.

cheers

David

C:\Oracle\jdbc\lib\ojdbc14.zip

comment by djw, posted at 11/14/05 11:35 PM

That's true. You could choose to keep any custom jar files in one central location outside the application server root, and then point to them individually via the CF Classpath setting.

It is the J2EE convention though to keep custom web application jars in WEB-INF/lib and custom classes in WEB-INF/classes. So its a toss up between convention and convenience.

comment by Steven Erat, posted at 11/15/05 8:19 AM

Steven,
When I try to compile compileTest.bat, I get an error: ORA-12505....
Here is how I configured the url: jdbc.url=jdbc:oracle:thin:@localhost:1521:GENS
My question is: do I type @localhost OR @servername ???

If it works, then can I use the same driver to connect to Oracle 9i, 8i, 7.3 ?

Eugene

comment by Eugene, posted at 12/27/05 2:20 PM

You can do a pure java test inside coldfusion with cfml. Then cfdump the object and use the
methods to check the connection.

comment by Dan plesse, posted at 8/15/06 3:47 PM

You can do a pure java test inside coldfusion with cfml. Then cfdump the object and use the
methods to check the connection.

oracle_thin = oracle.jdbc.OracleDriver.connect("jdbc:oracle:thin:@localhost:1521:GENS", props);

oracle_thin_Statement = oracle_thin.createStatement();

oracle_thin_Statement.close();
oracle_thin.close();

comment by Dan plesse, posted at 8/15/06 3:48 PM

Hi Steven,

I have your approach for connecting to Oracle 10g (Rel. 2) successfully and was wondering whether the same approach could be followed for connecting Eclipse (WTP) to an Oracle database instance, I have already done this too successfully, however was curious as to whether this is the same approach.

Many thanks

Ethan Cane

comment by Ethan Cane, posted at 8/25/06 9:14 AM

Ethan, I'm sorry that I don't undertand your question. Specifically about connecting to Oracle from Eclipse WTP.

comment by Steven Erat, posted at 8/25/06 2:16 PM

Ethan,

Are you looking for something like the DBEdit plug-in for Eclipse? (http://sourceforge.net/projects/dbedit)

comment by djw, posted at 8/30/06 3:16 AM

Hi,

I have followed the steps mentioned by you on CFMX 6.1 dev box.
We also have Oracle 10g installed on the same machine.

I have created below SP in Oracle:
CREATE OR REPLACE PACKAGE Ravi_Pkg
IS
TYPE cursorType is ref cursor;

PROCEDURE Test (
   p_Merch_NUM    IN NUMBER,
   p_OUTCURSOR OUT cursorType
);

END Ravi_Pkg;
/
CREATE OR REPLACE PACKAGE BODY Ravi_Pkg
IS

-- Get Terms and Conditions Data based on ClientNum/PlatformNum/LanguageId
PROCEDURE test (
   p_Merch_NUM    IN NUMBER,
   p_OUTCURSOR OUT cursorType
)

IS

BEGIN
--p_OUTCURSOR cursorType;

OPEN p_OUTCURSOR FOR
      SELECT Item_num
      FROM TBL_MERCHANT_ITEMS
      WHERE MERCH_NUM = p_MERCH_NUM;
END test;

END Ravi_Pkg;
/

And granted ALL permissions to the one through whom I'm connecting.
After this I tried to execute below cfm code it is throughing the error can anyone please help me out.




Thank you,
Ravi.

comment by Ravi, posted at 12/3/07 5:06 PM

Calendar

Search This Site

This is an exact search only

About This Site

Adobe Alumni & Community Professional. Expert in ColdFusion, Flex, LCDS, Photoshop, Lightroom. Linux RHCE. Follow Me!. For my photography check out Boston Portrait Photographer.