Oracle SID versus Service JDBC url

1

How do you connect to oracle using Oracle Service Name? The datasource form restricts to SID

2

Hi @banu_parasuraman this is a UI issue where the label was misnamed. We are actually generating a connection string based on service name. Could you try entering your Service Name into the SID field?

3

Hi Banu - did you have a chance to try this?

4

Apologies for the delay. Yes, I was able to try it today with oracle
service name and was able to successfully connect to the datasource. Thanks

1 Like
5

The JDBC String is definitely passing the service name (1521/service_name, not the SID (1521:sid)
However, I’m having some strange behavior when connecting even when tricking out as the service name in the sid box. I’m able to get the metadata using FQSN, but then when I go to select a table, I’m getting TNS-12514 again.

I know how to workaround it on the listener side, and I used orcl.oracle.com in the Dremio config (I have internal DNS mocking oracle.com), but I’m not sure why the jdbc call is leaving out the full service name on the table query even after I corrected it in data source config.

Service "orcl.oracle.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...

<txt>17-SEP-2017 15:42:59 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)
(USER=ted))(SERVICE_NAME=orcl)) * (ADDRESS=(
PROTOCOL=tcp)(HOST=192.168.42.1)(PORT=53916)) * establish * orcl * 12514
 </txt>
</msg>
<msg time='2017-09-17T15:42:59.667-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='workshop.oracle.com'
 host_addr='192.168.42.100'>
 <txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

The reason is seen in the jdbc thin driver call: "jdbc:oracle:thin:hr/hr@192.168.42.100:1521/orcl"
If calling a sid it should be "jdbc:oracle:thin:hr/hr@192.168.42.100:1521:orcl

 "pop" : "jdbc-scan",
    "@id" : 5,
"sql" : "SELECT *\r\nFROM \"HR\".\"EMPLOYEES\"\r\nWHERE ROWNUM <= 2000",
"columns" : [ "`EMPLOYEE_ID`", "`FIRST_NAME`", "`LAST_NAME`", "`EMAIL`", "`PHONE_NUMBER`", "`HIRE_DATE`", "`JOB_ID`", "`SALARY`", "`COMMISSION_PCT`", "`MANAGER_ID`", "`DEPARTMENT_ID`" ],
"config" : {
  "type" : "jdbc",
  "driver" : "oracle.jdbc.OracleDriver",
  "url" : "jdbc:oracle:thin:hr/hr@192.168.42.100:1521/orcl",
  "username" : null,
  "password" : null,
  "fetchSize" : 1000

Again, didn’t do a lot of debugging, but wanted to make a note in case someone else comes across this.

6

Quick update… I removed and re-ran the data source setup and this time I’m not getting the error. It seemed there was a timeout the first time querying metadata after I made the correction and I canceled out of the screen or something so it didn’t update fully. Either way, the original config without the service remained and couldn’t query the dataset that already displayed in metadata. Once I reconfigured it and waited for all metadata to populate, it worked fine.

1 Like
7

Thanks for the follow up Ted!