Create external data source in Azure Synapse Analytics (Azure SQL Data warehouse) to Oracle

Question

I am trying to create external data source in Azure Synapse Analytics (Azure SQL Data warehouse) to Oracle external database. I am using the following code in SSMS to do that:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myPassword';
CREATE DATABASE SCOPED CREDENTIAL MyCred WITH IDENTITY = 'myUserName', Secret = 'Mypassword';
CREATE EXTERNAL DATA SOURCE MyEXTSource
WITH (
LOCATION = 'oracle://<myIPAddress>:1521',
CREDENTIAL = MyCred
)

I am getting the following error:

CREATE EXTERNAL DATA SOURCE statement failed because the 'TYPE' option is not specified. Specify a value for the 'TYPE' option and try again.

I understand from the below that TYPE is not a required option for Oracle databases.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azure-sqldw-latest

Not sure how what the problem is here, is this feature still not supported in Azure Synapse Analytics (Azure DW) when it is already available in MS SQL Server 2019? Any ideas are welcome.

Any update on this @Ani?

wBob
– wBob

2020-03-25 18:56:04 +00:00
Commented Mar 25, 2020 at 18:56 — wBob
– wBob, Commented Mar 25, 2020 at 18:56

wBob · Accepted Answer · 2020-03-24 22:04:18Z

1

Polybase has different versions across the different products with different capabilities. Most of these are described here:

The ability to connect to Oracle is only present in the SQL Server versions, currently 2019. The documentation is quite clear that is only applies to SQL Server and not to Azure Synapse Analytics (formerly Azure SQL Data Warehouse):

https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-oracle?view=sql-server-ver15

In summary, Azure Synapse Analytics and its version of Polybase does not currently support to access external Oracle tables at this time.

answered Mar 24, 2020 at 22:04

wBob

14.4k3 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create external data source in Azure Synapse Analytics (Azure SQL Data warehouse) to Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related