Run SQL File on Oracle using Service Name instead of SID

book

Article ID: 127993

calendar_today

Updated On:

Products

CA Release Automation - Release Operations Center (Nolio) CA Release Automation - DataManagement Server (Nolio)

Issue/Introduction

We need a way to run .sql files against our Oracle server using the Service Name instead of the SID. How can this be done?

The action "Run SQL File on Oracle(c) Database Server" shows that it has a required field for SID.

Environment

CA Release Automation v6.6
Oracle 12c

Resolution

When configuring the "Run SQL File on Oracle(c) Database Server" using the following properties it successfully connects using a Service Name:
Servername: yourservername
User Name: your username
Password: yourpasswd
SQL File To Execute: /path/to/your.sql
Out Put File Path: /path/to/your.out
Driver Class Name: oracle.jdbc.driver.OracleDriver
Connection String: jdbc:oracle:thin:@//yourservername:1521/ORCLPDB1
SID: silly rabbit trix are for kids

NOTE: While using Driver Class Name and Connection String it appears that SID is not used - as that SID definitely does not exist. It is only provided since the SID field is required. 

Additional Information

While running tests the following conditions were true: 
a. Using Oracle 12c out of the box docker image built using the buildDockerImage.sh script they provide (thanks Oracle!).  This image does not appear to have any SID's for the PDB that gets generated.
b. Attempting to connect via sqldeveloper to SID ORCLPDB1 returns: Status: Failure - Test failed: Listener refused the connection with the following error: ORA-12505, TNS: listener does not currently know of SID given in connect descriptor.
c. Using ORCLPDB1 in the Service Name field allows sqldeveloper to connect successfully.