Here is the topology of the environment
| SQL Server | Oracle |
| SQL Server 2019 RTM
Platform :Redhat 7.6 |
Oracle 11g
Platform:Windows 2016 IP:192.168.1.31 Port:1521 SQL 2019 RTM does not support versions greater than 12.1 |
Oracle
===
1.Create a user.
create user "user1" identified by Password1;
2.Grant dba permission.
grant dba to "user1";
3.Create table and insert data;
create table "user1"."employee"("id" number(19), "name" varchar(25)); insert into "user1"."employee" values(1,'Fennik'); commit;
SQL Server
===
1.Install Polybase component. Please follow the article to install the component (please follow this article if you are not using redhat/centos)
sudo yum install -y mssql-server-polybase
2.Restart SQL Server to take effect.
3.Enable polybase feature
exec sp_configure @configname = 'polybase enabled', @configvalue = 1;
reconfigure
4.Create a user database.
create database polyDB2
5.Create a mater key in the user database.
use polyDB2 go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongP@ssword1'
6.create the credential. The identify stands for the User name in Oracle database, and the Secret is the password of the user .
CREATE DATABASE SCOPED CREDENTIAL OracleCredentialTest WITH IDENTITY = 'user1', Secret = 'Password1';
7.Create an external data source referring the credential in previous step.
CREATE EXTERNAL DATA SOURCE [ORACLE] WITH ( location = 'Oracle://192.168.1.31:1521',CREDENTIAL = OracleCredentialTest)
8.Create the external table
CREATE EXTERNAL TABLE dbo.employee ( id bigint, name varchar(25) COLLATE Latin1_General_100_BIN2_UTF8 ) WITH ( location=N'ORCL.user1.employee', DATA_SOURCE= [ORACLE] );
And you can choose which columns are used in the external table in SQL Server. Following query is valid too.
CREATE EXTERNAL TABLE dbo.employee ( id bigint ) WITH ( location=N'ORCL.user1.employee', DATA_SOURCE= [ORACLE] );
9.The external able is available to use now.
Errors I experienced
1. Msg 46530, Level 16, State 11, Line 17
External data sources are not supported with type GENERIC.
TSQL:
CREATE EXTERNAL DATA SOURCE [ORACLE] WITH ( location = 'Oracle://192.168.1.31:1521',CREDENTIAL = OracleCredentialTest)
Solution:Make sure Polybase component is installed and 'polybase enabled' is set to 1
2. Msg 110045, Level 16, State 1, Line 21
110045;User authorization failed: [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01017: invalid username/password; logon denied Additional error <2>: ErrorMsg: [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01017: invalid username/password; logon denied, SqlState: 28000, NativeError: 1017
TSQL:
CREATE EXTERNAL TABLE dbo.employee ( id bigint ) WITH ( location=N'ORCl.user1.employee', DATA_SOURCE= [ORACLE] );
Solution
===
This error is obvious. Check the 'CREATE DATABASE SCOPED CREDENTIAL OracleCredentialTest ', make sure the user name and password combination is corrected.
3. Msg 2706, Level 16, State 1, Line 21
Table '"user1"."employeE"' does not exist.
Please read this article https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements008.htm#SQLRF51109
1)Based on my understanding, it becomes case-sensitive if you create table with quotation mark.
Please make sure the schema name and table name in the 'location' exactly matches the one you used in Oracle
For example, you create a table in Oracle like this:
create table "user1"."employee"("id" number(19), "name" varchar(25));
Running following TSQL will cause the error.
CREATE EXTERNAL TABLE dbo.employee ( id bigint ) WITH ( location=N'ORCl.user1.employeE',---The last E is upper case, which causes error. DATA_SOURCE= [ORACLE] );
2)If the table is created without quotation marks, Oracle will use an all-uppcase search for the name.
If you have a table like following:
create table "user1".employee("id" number(19), "name" varchar(25));
The word 'employee' in the 'location' must be upper case, else it raises error 2706
CREATE EXTERNAL TABLE dbo.employee ( id bigint ) WITH ( location=N'ORCl.user1.EMPLOYEE',---if any character of 'EMPLOYEE' is lower case, 2706 happens. DATA_SOURCE= [ORACLE] );
Please note, this rule also applies the schema name.
4.1 Msg 105083, Level 16, State 1, Line 3
105083;The following columns in the user defined schema are incompatible with the external table schema for table 'employee': user defined column: 'iD' was not found in the external table. The detected external table schema is: ([id] DECIMAL(19), [name] VARCHAR(25) COLLATE Latin1_General_100_BIN2_UTF8).
This is similar to the error 2706.
If the column is created with quotation mark, the name of column in the 'External table' in SQL Server needs to exactly match the one you used in Oracle.
If the column is created without quotation mark, the name of column in the 'External table' in SQL Server needs to be upper case.
4.2 Msg 105083, Level 16, State 1, Line 3
105083;The following columns in the user defined schema are incompatible with the external table schema for table 'employee': user defined column: ([name] VARCHAR(25)) is not compatible with the type of detected external table column: ([name] VARCHAR(25) COLLATE Latin1_General_100_BIN2_UTF8). The detected external table schema is: ([id] DECIMAL(19), [name] VARCHAR(25) COLLATE Latin1_General_100_BIN2_UTF8).
In case the default collation in sql server database is not the collation in Oracle, you need to specify the collation explicitly used in Oracle.
CREATE EXTERNAL TABLE dbo.employee
(
id bigint,
name varchar(25) COLLATE Latin1_General_100_BIN2_UTF8
)
WITH (
location=N'ORCL.user1.employee',
DATA_SOURCE= [ORACLE]
);