Oracle Connector on Polybase

Question

Hi everyone,

I currently have Polybase (SQL Server 2019 RTM) that connects to Oracle database. This was working well untill the Oracle database was upgraded to 13c and I started getting this error:

TNS: Listner could not resolve SERVICE_NAME given in the connect descriptor.

Please I need someone help me confirm if Polybase works with Oracle 13c.

Thanks

Answer 1

Are you able to connect to the Oracle server by creating an ODBC DSN using Control Panel on the SQL 2019 instance? If you are not able to connect using ODBC then troubleshoot that first.

Based on the error the listener on the Oracle server is OK, but you can verify that by using tnsping.

Another troubleshooting step is to check the listener status on the Oracle server and make sure on the list of services listed, the service name is listed as OK.

By the way. 13c does not exist as Oracle versions. Valid versions of Oracle are 12c, 18c, 19c and 21c.

One last thing you can try that should work, is to change the SERVICE_NAME=TEST by SID=TEST. On the Oracle server you can get the SID.

Answer 2

Hi @tobz ,

Welcome to Microsoft Q&A!

By the way. 13c does not exist as Oracle versions. Valid versions of Oracle are 12c, 18c, 19c and 21c.

So I deduce that you are 12c. If I misunderstood you, please feel free to let me know.

Please I need someone help me confirm if Polybase works with Oracle 13c.

Assuming your Oracle is 12c, that's no problem.

TNS: Listner could not resolve SERVICE_NAME given in the connect descriptor.

Take a look at this article, which covers as many reasons as possible for this error. The author divides them into four options ABCD.
Hope this would give you some help.
Finally, i put this related article here: https://www.sqlshack.com/enhanced-polybase-sql-2019-external-tables-for-oracle-db/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

If you get the Listner could not resolve SERVICE_NAME given in the connect descriptor error, you need to provide the service name in the connection options, but not as "service_name" like you would expect.. rather:
CONNECTION_OPTIONS ='servicename=the-service-name'
**
Then...
When creating the "External Table" be sure to provide the 3 part FQN in the LOCATION:**
LOCATION='database.schema.table',

**USUALLY the "database" and "service name" will be the same

So typically:
create/alter Data Source My-Data-Source**
CONNECTION_OPTIONS ='servicename=abc'

CREATE EXTERNAL TABLE MY_NEW_TABLE(

[BATCH_ID] FLOAT(53) NOT NULL,
ANOTHER_FIELD varchar(200))

WITH (

LOCATION='abc.some-schema.some-table', --CASE Sensitive!

DATA_SOURCE=My-Data-Source)

ALSO note:
Most Oracle connections are case sensitive