topic Re: DB Connect 2: How to configure a connection to an Oracle database with the service name instead of SID? in All Apps and Add-ons

DB Connect 2: How to configure a connection to an Oracle database with the service name instead of SID?

karabsze — Wed, 15 Apr 2015 02:34:35 GMT

I am trying to connect the oracle db with the service name, but the following warning pops up:

Validating connection with URL [jdbc:oracle:thin:@{host}:1521:{service_name}] failed, please verify that the connection is configured correctly.

May you advise what is the right configuration for using Service name instead of SID?
Thanks

Re: DB Connect 2: How to configure a connection to an Oracle database with the service name instead of SID?

fdi01 — Mon, 28 Sep 2020 19:28:40 GMT

Thin-style Service Name Syntax

Thin-style service names are supported only by the JDBC Thin driver. The syntax is:

@//host_name:port_number/service_name

For example:

jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename

So I would try:

jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD
or
[jdbc:oracle:thin:@//{your_host}:1521/{your_service_name}]

for more information see this link:
http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#BEIDHCBA

Re: DB Connect 2: How to configure a connection to an Oracle database with the service name instead of SID?

karabsze — Mon, 28 Sep 2020 19:34:05 GMT

Hi fdi,
Thanks for you reply.
I know about the thin style but I am not sure how I can setup it in DBConnect V2

In the db_connections.conf

[TEST_CONNECTION]
connection_type = oracle
database = test.services
host = host.com
identity = test_user
port = 1521

readonly = 1

The connection string is generated by above configuration and the ":" inside
jdbc:oracle:thin:@{host}:1521*:*{service_name}
cannot be skipped....i have no way to construct the string like below format

jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD
or
[jdbc:oracle:thin:@//{your_host}:1521/{your_service_name}]

Re: DB Connect 2: How to configure a connection to an Oracle database with the service name instead of SID?

pmdba — Mon, 28 Sep 2020 19:34:10 GMT

The connect string format is defined in the DBX v2 "db_connection_types.conf" file:

[oracle]
displayName = Oracle
serviceClass = com.splunk.dbx2.OracleJDBC
jdbcUrlFormat = jdbc:oracle:thin:@//<host>:<port>/<database>
jdbcDriverClass = oracle.jdbc.OracleDriver
database = XE
testQuery = SELECT 1 FROM DUAL
port = 1521

Make sure you copy the file from the default folder to the local folder before making changes. See the documentation for more details.

Re: DB Connect 2: How to configure a connection to an Oracle database with the service name instead of SID?

mmensch — Thu, 16 Apr 2015 14:35:19 GMT

I am also having this issue. Can someone confirm that Oracle DB 12.1.0.2.0 is supported with this app? The database matrix on the user documentation only lists that Oracle DB 11g works with JDBC driver ojdbc6.

Thanks

Re: DB Connect 2: How to configure a connection to an Oracle database with the service name instead of SID?

icyfeverr — Tue, 29 Sep 2020 07:41:03 GMT

I struggled with this myself just recently, the main piece is selecting the correct connection type, as you will see below. For SID use 'oracle', if you are using Service name use 'oracle_service'.

If you are using a SID follow this format:

[SID Example]
connection_type = oracle
database = sid
host = hostname
jdbcUseSSL = 0
port = 1521

If you are using a Service Name follow this format:
[Service Name Example]
connection_type = oracle_service
database = service_name
host = hostname or ip address
jdbcUseSSL = 0
port = 1563

Re: DB Connect 2: How to configure a connection to an Oracle database with the service name instead of SID?

MadiAvalos — Fri, 11 Feb 2022 18:12:58 GMT

Thanks! this work for me totally!