Configure the JDBC driver for Oracle on ColdFusion 8 Standard Edition
We have had a lot of people asking us if they could use the Oracle database with their ColdFusion Standard Edition. Of course, Adobe would prefer to sell you the Enterprise Edition for using Oracle, but fortunately ColdFusion is based on Java and thus we can use the free and existing Oracle JDBC Driver.
Note: If you do go with the Oracle JDBC “Thin Client” option you will NOT be able to return result sets from stored procedures. So, if you depend upon PL/SQL stored procedures for returning queries, then you will be out of luck without the ColdFusion Enterprise Edition. On the other hand one could also buy the same JDBC drivers as Adobe is using from DataDirect.
Now, before you will be able to use the JDBC Driver you will need to download the appropriate JDBC Oracle Driver. Make sure, that you download the correct driver for the correct version of your database. They are not all the same! Also, download the “ojdbc14.jar” file, since this is for the JDK 1.4 and JDK 1.5.
Grab the “ojdbc14.jar” file and copy/move it to “ColdFusion8/wwwroot/WEB-INF/lib”. Once done, you will need to restart the ColdFusion Server.
Go the the ColdFusion Administration and go to your “data sources”. Add a new “data source” and choose “Other” from the Database driver options.
On the following page enter “jdbc:oracle:thin:@10.10.10.11:1521:ORCL” in the “JDBC URL” Textarea. In the “Driver Class” textfield enter “oracle.jdbc.OracleDriver”. Of course, enter your username and password. Once done you should connect to your Oracle database without fail.
Here is a screenshot to visualize the above.
22:46 UTC
Copying the jar file from the oracle directory is something they even left off the Adobe site. Thanks!
11:44 UTC
Hi, thanks for the great article on this, been trying to connect to oracle quite sometime.
I’ve successfully connected the datasource and my coldfusion application is working fine and smooth, until i dealt with date columns in my database.
I have a date type column in my oracle database that contains date and time information stored in, if i use this jdbc thin client datasource, retrieving using the standard cfquery like:
select createdDateTime from myTable —> createdDateTime contains value of 12-Apr-2008 17:00:00
#qgetdatetime.createdDateTime# —> this produces something like 12-Apr-2008 00:00:00
The date is fine but the time returned is always zero.
This problem does not happen in CF Enterprise with datasource type set as Oracle to the same database.
Any solutions for this kind of problem?
Thanks a lot !
22:11 UTC
Have you tried this in the jvm.config file:
ADD THIS: -Doracle.jdbc.V8Compatible=true
and ensure your ojdbc14.jar is in the class path.
example:
# Arguments to VM
java.args=-server -Doracle.jdbc.V8Compatible=true -Xmx512m -Dsun.io.useCanonCaches=false -XX:MaxPermSize=192m -XX:+UseParallelGC -Dcoldfusion.rootDir={application.home}/../ -Dcoldfusion.classPath={application.home}/../lib/updates,{application.home}/../lib,{application.home}/../gateway/lib/,{application.home}/../wwwroot/WEB-INF/cfform/jars -Dcoldfusion.libPath={application.home}/../lib
# JVM classpath
Java.class.path={application.home}/servers/lib,{application.home}/../lib/macromedia_drivers.jar,{application.home}/lib/cfmx_mbean.jar,{application.home}/lib,D:/ColdFusion8/wwwroot/WEB-INF/lib/ojdbc14.jar
1:57 UTC
I have a problem with the char columns. When I do a condition on a char column with cfqueryparam, I must put trailing blanks. I read that I can use a fixedString connection property in order to compare without trailing blanks. How can I define this property?
0:02 UTC
You would probably define this in the Connection Strings within the CF Admin.
23:14 UTC
Datadirect 4,000 dollars per CPU for the DB Driver for datadirect. A little on the pricey side for me. Need a low budget oracle proc solution.
17:04 UTC
Thanks for the post on getting JDBC working for Oracle. Had it up and running in no time.
For those who do need to return queries from stored procedures this is possible using the Oracle ODBC drivers (ie not JDBC). This is relatively easy on Windows, however I haven’t tried on Linux. I believe it adds a bit of overhead. There may also be other incompatabilities I don’t know about.
I used the Oracle ODBC drivers that came with 11g Oracle Instantclient.
20:28 UTC
I did what you suggested and am getting the following
Connection verification failed for data source: RMDC-ECC
java.sql.SQLException: No suitable driver available for RMDC-ECC, please check the driver setting in resources file, error: oracle.jdbc.OracleDriver
The root cause was that: java.sql.SQLException: No suitable driver available for RMDC-ECC, please check the driver setting in resources file, error: oracle.jdbc.OracleDriver
10:42 UTC
Sorry, for the late reply, our blog software did not send us any emails notifications anymore for new comments.
I think your error is that you are using the wrong driver version for your database. Check the Oracle JDBC driver page to see which driver to use with which database version.
15:10 UTC
Most aswomest post to same money, ever…..
23:33 UTC
I strictly recommend not to wait until you get big sum of money to order different goods! You can just take the loan or consolidation loans and feel comfortable