2

ColdFusion 2018 on Windows 2016 Server, currently using Oracle 12c.

My institution is planning an Oracle upgrade to 19c, I have found nothing in the CF documentation about connections to a 19c database.

Has anyone done this? Any issues I should know about? Our CF install is up to date.

Thank you,

Michelle

michelle H
  • 93
  • 1
  • 6
  • 2
    On a recent project, we updated to 19c and didn't need to make any changes to the ColdFusion datasource. – Scott Stroz Aug 12 '20 at 17:19
  • 1
    I don't have any experience with ColdFusion, but cannot imagine how it would connect any differently than any other database client. It should be relying on either a jdbc thin client for oracle, or the standard oracle client libraries. The only issue would be if _those_ libraries were too far behind. What database version are you upgrading from? – EdStevens Aug 12 '20 at 19:32
  • @EdStevens Adobe ColdFusion comes with some commercial Oracle JDBC drivers. Using Thin Drivers is indeed always an option. – Bernhard Döbler Aug 12 '20 at 20:08
  • Current Oracle is 12c (oops, said 11g in my post, my bad) – michelle H Aug 12 '20 at 21:12
  • @BernhardDöbler - then the op would just need to make sure that the drivers are compatible with the target version of Oracle. If he has an MOS account (can't imagine supporting Oracle without it) then he can check the Client / Server Interoperability Support Matrix - Doc ID 207303.1 – EdStevens Aug 13 '20 at 00:05
  • Welll... I have no idea what an MOS account is. So, I'm guessing I don't. – michelle H Aug 13 '20 at 12:32
  • @ScottStroz when your shop upgraded, did they not switch over from using a `SID` to a `SERVICE_NAME`? That must be the reason why you didn't need any changes. If you notice the Oracle driver doesn't have an entry for `SERVICE_NAME`, they only have one for a `SID`. This means Michelle will have to use the jdbc thin client. – user12031119 Aug 18 '20 at 20:51
  • @user12031119 I do not know the details, but I know we did not need to make any changes to the datasource definition. – Scott Stroz Aug 20 '20 at 12:18

2 Answers2

2

Actually, for the built-in Oracle database drivers, if you leave the SID blank and add the following to your Connection String it should work:

ServiceName=myoracle.service.name;
fcdt
  • 2,371
  • 5
  • 14
  • 26
Joseph
  • 21
  • 2
0

In my shop, we switched over to Oracle 19c which was a pain because every Oracle datasource needed to be recreated again only differently. If during your upgrade from 12c to 19c, your shop switched from using a SID to a SERVICE_NAME then the native Oracle driver will no longer work since there's no entry for the SERVICE_NAME on the create datasource screen. Instead, you must create the datasource using "Other" for the driver and then use the jdbc thin client. Here are the values you will need to plug in.

Firstly, you may need to download the Oracle thin driver from Oracle's website. I'm not sure on this because we have another team that manages the server.

Driver Class: oracle.jdbc.OracleDriver

Driver Name: ojdbc.jar

JDBC URL: Go to your tnsnames.ora file and copy and paste the entry in the CF Admin create datasource screen. However you will replace the DATABASE.WORLD= with jdbc:oracle:thin:@ and keep the rest of the entry the same. So for example, if you had a TNS entry that reads:

MYDB.WORLD=(DESCRIPTION=(ADDRESS=(COMMUNITY=TCP.WORLD)(PROTOCOL=TCP)(HOST=HOSTNAME)(PORT=2727))(CONNECT_DATA=(SERVICE_NAME=MYDB)))

Then your JDBC URL would read

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(COMMUNITY=TCP.WORLD)(PROTOCOL=TCP)(HOST=HOSTNAME)(PORT=2727))(CONNECT_DATA=(SERVICE_NAME=MYDB)))

Lastly, you can go here for similar but somewhat different information.

user12031119
  • 1,228
  • 4
  • 14
  • Service names were introduced in [Oracle 8.1](https://docs.oracle.com/cd/A87860_01/doc/network.817/a76933/namingcf.htm) in 1998. It seems very strange that apps can't connect using the network alias from a tnsnames.ora file, which means that SID or SERVICE_NAME can be used and the app won't know or care. – Christopher Jones Aug 18 '20 at 23:20
  • Definitely 12c, CF connected to the test 19 install, but I they don't have any data on it to try to use it. – michelle H Aug 19 '20 at 12:03
  • @ChristopherJones I didn't know that service names were introduced that long ago. However, my shop recently switched over from using a SID to SERVICE_NAME. The native Oracle driver that ships with CF doesn't have an entry for SERVICE_NAME, so the only method we were able to come up with required using the jdbc thin client. If the OP can confirm they switched over during their O19c upgrade, then the solution I provided works beautifully. – user12031119 Aug 19 '20 at 12:07
  • @michelleH did they change from using a SID to a SERVICE_NAME when upgrading from 12c to 19c? If so, the jdbc thin client works like a charm. – user12031119 Aug 19 '20 at 12:09
  • 1
    If you can put a tnsnames alias in there somewhere (and tnsnames.ora files have been around forever), then you don't need to change to jdbc. Just change your tnsname.ora entry mapping to use the service name. Some related discussion is at https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#net-service-names-for-connection-strings – Christopher Jones Aug 19 '20 at 22:54
  • @ChristopherJones Thanks. Unfortunately I didn't know about this. When we did the upgrade, the server admin worked with the DBAs on this solution we use and like I said, it works like a charm. However, I didn't know the jdbc thin driver could have worked much simpler after seeing this. `jdbc:oracle:thin:@hostname:port/service_name ` – user12031119 Aug 20 '20 at 01:33
  • Oracle 19c also introduced Easy Connect Plus (https://docs.oracle.com/en/database/oracle/oracle-database/20/jjdbc/data-sources-and-URLs.html#GUID-F0A0C45C-C5E9-45B6-AB86-181218D348CB) Where you can pass connection properties in the connection URL. – Nirmala Aug 21 '20 at 09:43