OHDSI Home | Forums | Wiki | Github

Probelm connecting to oracle data source


(Jay Boyer) #1

When I try to deploy the .war file, the flyway datasource connection fails. I believe it is because the Oracle database was set up with a Service Name of ORCL rather than an SID of xe

In the example the settings.xml is configured for an SID of xe

  <flyway.datasource.url>jdbc:oracle:thin:@//127.0.0.1:1521/xe</flyway.datasource.url>

How do specify the datasource for a Service Name.

I tried replacing /xe in the xml with /ORCL, but that did not work.


(Chris Knoll) #2

I found this: https://stackoverflow.com/questions/4832056/java-jdbc-how-to-connect-to-oracle-using-service-name-instead-of-sid.

But rebuilding the WAR each time to test a connection string is annoying. Do you have a JDBC client (like SqlWorkbench) that you an use a JAR to connect to the database? That way you can try out different URLs to see which works for you. From the article, the 2 styles are:

jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1521/ABCD

Or the TNS format:

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL=TCP)(HOST=blah.example.com)(PORT=1521)))(CONNECT_DATA=(SID=BLAHSID)(GLOBAL_NAME=BLAHSID.WORLD)(SERVER=DEDICATED)))

Also, confirm your port of 1521 is correct for your env, and there’s no firewall blocking connections. You’ll be able to determine this more easily if you get the JDBC client and use it to test your JDBC urls.

I’ve just set up Oracle XE myself, and this worked for me:

<datasource.url>jdbc:oracle:thin:@//127.0.0.1:1521/xe</datasource.url>

And in SqlDeveloper (Oracle’s tool) I did specify to connect via the ‘Servcie Name’…So without further information, I’m not sure where your problem is.


(Jay Boyer) #3

Thanks, I used a TNS format string and that worked.

  <flyway.datasource.url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=blah.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))</flyway.datasource.url>

(Anita Basa) #4

Hello,

Good Evening,

If you are facing problem connecting to oracle data source, please check this link once : https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ntqrf/database-connection-issues.html#GUID-71FADFEE-5D9B-4C02-B836-C1375E82810E I hope this link will help you.

Thanks & Regards,
Anita


t