Connecting CFMX Pro to Oracle

This has been convered in a few places, but people ask this alot, so I figured I would drop my version of the answer somewhere that may be a little easier to find. I've done this a few times here and there.

There are a variety of ways that you can connect Oracle, but most of them provide limited functionality.

JDBC Thin driver

Details can be seen here:
ColdFusion Technote
CFGuru Blog (Yup, that comment at the bottom is me).

Pros – This is by far the most lightweight installation to get Oracle connected with CFMX.

Cons – You cannot get Result Sets to return from Stored Procedures using this driver. (There may be other stored proc issues I may not be aware of, but I hit this one and gave up.)

Oracle ODBC Driver

Pros – You have the full functionality of Oracle (i.e you can get result sets out of stored procs).

Cons – It's a longer and slightly more tricky install process as you have to install the Oracle Client.

Personally, I like the ODBC version the best, simply because getting result sets out of Stored Procs is of high priority to me.

The steps for installing the ODBC version are the following:

  1. Download the Oracle Client (It's big. Be Warned.)
  2. Download the Oracle ODBC driver.
  3. Install the ODBC Client (create an 'Oracle Home' in any dir. Default should be fine).
  4. Install the ODBC drivers (You may have to do this with the Oracle Universal Installer, which comes with the Client).
  5. Open Oracle – oraHome > Configuration and Migration Tools > Net Manager
  6. Click Service Naming in the tree.
  7. Click Edit > Create from the menu at the top. You will get a new window.
  8. Enter the Net Service Name. I tend to use the same name as the Oracle SID/Service Name. Click Next
  9. Leave at TCP/IP and click Next.
  10. Enter the Host Name of the Oracle server (i.e. oracle.mysite.com), and the port number. The default Oracle port is displayed. Click Next.
  11. Enter the SID/Service name of the Oracle database you are connecting to. Leave as Database Default for connection type. Click Next.
  12. Click Test to test the Oracle connection. First test will probably fail, as it will have the wrong login details. Enter the right details and try again.
  13. Once the connection works, click Finish.
  14. Exit the Net Manager and click Save on the dialog.
  15. Goto Control Panel > Administrative Tools > Data Sources.
  16. Click System DSN.
  17. Click Add.
  18. Choose Oracle in OraHomeand click Finish. (Why finish? you're not done!)
  19. Enter the name in DataSource Name. Doesn't really matter what. I tend to stick to the SID / Service Name.
  20. Choose your TNS Service Name from the drop down provided.
  21. Test the connection. The first test will probably fail, as the username and password will be wrong. Enter the correct details until it works.
  22. Click OK.
  23. Now go into the CF Administrator > Data Sources
  24. Add a ODBC Socket datasource.
  25. Choose the Oracle ODBC DSN from the drop down.
  26. Click Show Advanced Settings and enter the username and password (if required).
  27. Click Submit.
  28. The datasource should now validate (if it doesn't, try restarting the machine, as sometimes a restart is required for the new odbc connection to take full effect).

I don't think I've missed anything. Like I said, it's a tad long and convoluted, but it does work in a production environment.

Once it's set up once, adding a new datasource is a 5 minute job.

I hope that solves someone's problem, because it took me a few weeks to work that one out.

 

Leave a Comment

Comments

  • Vince Bonfanti | June 30, 2004

    Some comments:

    1. With Oracle 10g the client software is packaged separately for a smaller, more convenient download:

    http://otn.oracle.com/tech/oci/instantclient/instantclient.html

    The 10g client is backward-compatible through Oracle 8.1.7.

    2. If you want to try BlueDragon as an alternative to CFMX, you can use the Thin Client JDBC driver and retrieve results set from stored procedure:

    http://www.newatlanta.com/products/bluedragon/index.cfm

    3. Another alternative is to use the OCI driver for JDBC, which also requires you to install the Oracle client (I’ve tested this on BlueDragon, but not CFMX).

  • Mark | June 30, 2004

    Nice to know about the 10g client download. We’ve been using 9i, so that was all I knew about.

    Cheers.

  • Patrick Whittingham | July 1, 2004

    Is there more Oracle Drivers for cfmx Enterprise (ie., Native) ? Could you add to your blog concerning 9i/10g.

    – Pat

  • Mark | July 1, 2004

    Patrick –
    If you have enterprise, you shouldn’t need any of these drivers.

    The JDBC drivers that come with CFMX Enterprise should work without a hitch.
    (Least I know they do for 9i, I assume so for 10g).

    But anything that will work for Pro will work for Enterprise – they are just jdbc drivers at heart, so feel free to experiment.