February 4, 2018
ColdFusion 2016 standard and Oracle adventures
Comments
(4)
February 4, 2018
ColdFusion 2016 standard and Oracle adventures
Newbie 29 posts
Followers: 6 people
(4)

So i was upgrading from ColdFusion 11 to ColdFusion 2016 yesterday.  Everything goes fairly smoothly and then i find that none of my data sources are connecting.  I’m getting a licensing error connecting to my Oracle databases.  Seems that ColdFusion 2016 standard does not include the Oracle driver, I have to have enterprise for that.  Yes, it’s on me that I should have checked for this but I didn’t because in every other version of standard I’ve used the Oracle driver was there.   So I own that it was my fault to not check this.  Off i go to Google and I start seeing that people say ColdFusion 11 standard doesn’t have it either.  Odd as i’m looking at my other server with ColdFusion 11 standard right now and there’s the Oracle driver I’ve been using in the drop down list.  So either i got lucky somehow or those Google results were incorrect.  It’s neither here nor there though but strange and another reason i didn’t expect it not to be in 2016.

I have to say that i’m really surprised the Oracle driver isn’t in standard.  As popular as Oracle is it seems a bad choice for it to only be included in enterprise.  I understand the logic of having value in enterprise that is not in standard but there is plenty of other advantages there without taking out a common driver.  I work at a university where 90% of all data is stored in Oracle and not having a quick and easy method to connect when they’re viewing features may knock it out of the running if someone was evaluating the software.  Just my quick soapbox rant.

But you can add the Oracle driver to 2016 and while it’s some hoops its not overly hard but Google as you will you may not get the answer right away.  You will quickly find the following advice in multiple locations which is very helpful and gives you a good head start. It may even be the answer first try.  I should point out that it’s from the ColdFusion development team so another reason it’s a little surprising its not included in Standard when even the Adobe folks quickly point out how to get around it’s absence.

Hi,

Since it’s a 3rd party driver, you don’t find it in drop down. Please follow the instructions below.

1. Download the thin driver from Oracle’s website.

2. You can copy the driver in ColdFusioncfusionlib or ColdFusioncfusionwwwrootWEB-INFlib

3. Restart ColdFusion service.

4. Login to CFadmin>Datasources>Add Datasource name and from the drop down select as “Other”.

5. URL for connecting to Oracle are

a. If you have configured SID, follow the below URL

jdbc:Oacle:thin:@//{HOSTNAME}:{PORT}/SID

b. If you have configured service name, follow the below URL

jdbc:Oracle:thin:@//{HOSTNAME}:{PORT};servicename={servicename}

6. Driver Class: oracle.jdbc.driver.OracleDriver

7. Enter username and password and Submit.

Hope this helps.

Thanks,

Priyank

Thanks go out to Priyank for getting me, and everyone else, started. I went to Oracle here to get the latest drivers. I chose to get the 12c drivers for Release 1. I grabbed ojdbc7.jar and i dropped it where Priyank advised and restarted. To give credit where credit is due I did find where to get the driver in multiple posts as well.

Now you can move on to the rest of Priyank’s advice and if it works for you awesome. It really was that simple and i envy you. It wasn’t for me. Trying variations of 5a and 5b i’d get “Invalid number format for port number”, “TNS:listener does not currently know of service requested in connect descriptor”, and variations of other messages. So down the Google rabbit hole i went. I kept finding references to the thin client being able to use the tnsnames.ora file which defines SIDs but while i saw it could be done there wasn’t much on how to do it. I did find some references to setting system variables for the path to the .ora file which i did and restarted to no avail. I found other references for the thin drivers saying to use service_name and not servicename. Again another failure.

So I went to the horse’s mouth in Oracle’s thin driver documentation here. Down in that page under “Writing the URL that Targets the Connection Manager” I found the answer. I had to alter my JDBC URL entry to the following: “jdbc:Oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={HOSTNAME})(PORT={port})))(CONNECT_DATA=(SERVICE_NAME = {servicename})))”. And there we go, successful connection and Oracle was back up and running.

Is there a better way, probably and hopefully someone expands on it in the comments. Am i saying Priyank was wrong? Definitely not, I’m sure there was something different about my setup than his and hopefully his answer worked right away for you. I just wanted to share what i had to do to get Oracle back up and running in case you have the same issue.

4 Comments
2018-02-15 18:29:44
2018-02-15 18:29:44

This is the proper way when using Standard Edition. Oracle and Sybase JDBC drivers are only included in the Enterprise Editions. While you were working on getting connected to Oracle, I was doing the same thing getting ColdFusion 11 Standard connected to Sybase. You’ll see my blog post in the Community Portal (https://coldfusion.adobe.com/2018/02/connect-coldfusion-jdbc-to-sybase-sql-anywhere/) I think the reason Standard Editions do not have the JDBC drivers is that the ‘full JDBC versions’ are a paid license at Oracle and SAP? In your situation you had to use the “thin JDBC” and in my case I used and open source JDBC driver.

Like
(3)
>
Wil Genovese
's comment
2018-02-20 01:13:26
2018-02-20 01:13:26
>
Wil Genovese
's comment

Yeah, everyone keeps saying only in Standard but I have two CF11 standard installs with the Oracle driver in my drop down. Like i said, guess i was just lucky somehow. That makes sense on the JDBC drivers. I didn’t think of the licensing issues. I haven’t had need of Sybase but good to know there’s some documentation out there for it now. Thanks for writing it up.

Like
>
Grae Desmond
's comment
2018-08-06 08:31:18
2018-08-06 08:31:18
>
Grae Desmond
's comment

The ColdFusion Developer Edition has the Oracle driver. May be, you set up the driver before entering the license key and never again touched the datasource?

Like
IQ1
>
Wil Genovese
's comment
2019-05-13 14:43:45
IQ1
2019-05-13 14:43:45
>
Wil Genovese
's comment

I am having a similar issue, I have successfully configured the JDBC thin driver to connect to Oracle from ColdFusion 2018. Everything works but some of my reports are breaking when I Pass a datetime string of the format dd/mm/yyy 00:00:00

For anything else other than 00:00:00 it seems to work. I don’t have the same issue with my Developer installation on another Dev server. What could be wrong any suggestions ?

Like
Add Comment