An example of Polybase for Oracle

Published Dec 03 2019 09:40 AM 2,606 Views
Microsoft

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] 
);

2.png

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]
);

 

3 Comments
%3CLINGO-SUB%20id%3D%22lingo-sub-1186278%22%20slang%3D%22en-US%22%3ERe%3A%20An%20example%20of%20Polybase%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1186278%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EAfter%20learning%20about%20this%20great%20new%20feature%20Polybase%20I%20started%20to%20implement%20it%20against%20a%20Oracle%2012c%20external%20server.%3C%2FP%3E%3CP%3EI%20have%20still%20not%20had%20success%20in%20getting%20this%20to%20work%2C%20even%20after%20upgrading%20my%20SQL2019%20to%20cu2%20(build%26nbsp%3B%3CSPAN%3E15.0.4013.40)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20both%20used%20TSQL%20and%20%22Azure%20Data%20Studio%22%20and%20the%20error%20I%20get%20is%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22110045%3BUser%20authorization%20failed%3A%20%5BMicrosoft%5D%5BODBC%20Oracle%20Wire%20Protocol%20driver%5D%5BOracle%5DORA-01017%3A%20invalid%20username%2Fpassword%3B%20logon%20denied%20Additional%20error%20%26lt%3B2%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Oracle%20Wire%20Protocol%20driver%5D%5BOracle%5DORA-01017%3A%20invalid%20username%2Fpassword%3B%20logon%20denied%2C%20SqlState%3A%2028000%2C%20NativeError%3A%201017%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20might%20be%20fixed%20by%20changing%26nbsp%3Bsqlnet.ora%20-%20%22sqlnet.allowed_logon_version_server%22%26nbsp%3B%26nbsp%3Bbut%20I%20do%20not%20have%20control%20over%20the%20ORA%20server.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThere%20seems%20to%20have%20been%20a%20similar%20error%20fixed%20in%20cu2%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F4537072%2Ffix-creating-an-external-table-against-an-oracle-database-in-sql-serve%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F4537072%2Ffix-creating-an-external-table-against-an-oracle-database-in-sql-serve%3C%2FA%3E%26nbsp%3B%20-%20but%20this%20does%20not%20help%20me.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESo%20at%20last%20my%20questions%20are%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E-%20When%20will%20MS%20have%20a%20newer%20ORA%20driver%20in%20the%20Polybase%20subsystem%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189522%22%20slang%3D%22en-US%22%3ERe%3A%20An%20example%20of%20Polybase%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189522%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Hraunfjord%26nbsp%3B%3CSPAN%20class%3D%22UserName%20lia-user-name%20lia-user-rank-Occasional-Visitor%20lia-component-message-view-widget-author-username%22%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20white-space%3A%20nowrap%3B%22%3E%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20have%20been%20working%20on%20the%20issue%2C%20however%2C%20the%20new%20driver%20has%20not%20been%20released%20yet.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20class%3D%22lia-message-author-rank%20lia-component-author-rank%20lia-component-message-view-widget-author-rank%22%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20display%3A%20inline-block%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2014px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20overflow%3A%20hidden%3B%20padding-top%3A%2010px%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-overflow%3A%20ellipsis%3B%20text-transform%3A%20none%3B%20vertical-align%3A%20top%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20nowrap%3B%20word-spacing%3A%200px%3B%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1208904%22%20slang%3D%22en-US%22%3ERe%3A%20An%20example%20of%20Polybase%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1208904%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EThanks%20for%20answering%20me.%3C%2FP%3E%3CP%3EI%20got%20the%20Oracle%20team%20to%20change%20SQLNET.ORA%20-%20so%20now%20I%20can%20connect%20to%20the%20Oracle%20server.%3C%2FP%3E%3CP%3EOf%20course%20I%20have%20a%20new%20problem%20now%3A%3C%2FP%3E%3CP%3EUnsupported%20Column%20Types%20found%3A%3C%2FP%3E%3CP%3E%26nbsp%3Bthese%20colomn%20types%20are%20not%20supported%20for%20external%20tables%3C%2FP%3E%3CP%3E%26nbsp%3B*%20MESSAGE%20(CLOB)%3C%2FP%3E%3CP%3EThe%20AzureDataStudio%20Wizard%20sets%20the%20%22SQL%20Data%20Type%22%20as%20%22CLOB%22%26nbsp%3B%20for%20ORA%20column%20%5BMESSAGE%5D%20which%20is%20a%20CLOB%20field%20in%20Oracle%20and%20might%20be%20bigger%20than%204000%20chars.%3C%2FP%3E%3CP%3EAccording%20to%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fpolybase%2Fpolybase-type-mapping%3Fview%3Dsql-server-ver15%23oracle-type-mapping-reference%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fpolybase%2Fpolybase-type-mapping%3Fview%3Dsql-server-ver15%23oracle-type-mapping-reference%3C%2FA%3E%3C%2FP%3E%3CP%3ECLOB%20is%20supported.%3C%2FP%3E%3CP%3ECan%20you%20comment%20on%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1044636%22%20slang%3D%22en-US%22%3EAn%20example%20of%20Polybase%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1044636%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20the%20topology%20of%20the%20environment%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20cellspacing%3D%221%22%20cellpadding%3D%2210%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20valign%3D%22top%22%3ESQL%20Server%3C%2FTD%3E%0A%3CTD%20valign%3D%22top%22%3EOracle%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20valign%3D%22top%22%3ESQL%20Server%202019%20RTM%26nbsp%3B%3CP%3EPlatform%20%3ARedhat%207.6%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20valign%3D%22top%22%3EOracle%2011g%26nbsp%3B%3CP%3EPlatform%3AWindows%202016%3C%2FP%3E%0A%3CP%3EIP%3A192.168.1.31%3C%2FP%3E%0A%3CP%3EPort%3A1521%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESQL%202019%20RTM%20does%20not%20support%20versions%20greater%20than%2012.1%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EOracle%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3D%3D%3D%3C%2FP%3E%0A%3CP%3E1.Create%20a%20user.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ecreate%20user%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22user1%22%20identified%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Eby%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EPassword1%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3E2.Grant%20dba%20permission.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Egrant%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edba%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Eto%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22user1%22%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3E3.Create%20table%20and%20insert%20data%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ecreate%20table%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22user1%22%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22employee%22%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22id%22%20number%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E19%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%2C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22name%22%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Evarchar%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E25%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E))%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Einsert%20into%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22user1%22%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22employee%22%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Evalues%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E1%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'Fennik'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ecommit%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CDIV%20id%3D%22tinyMceEditorclipboard_image_3%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20id%3D%22tinyMceEditorclipboard_image_8%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20id%3D%22tinyMceEditorclipboard_image_7%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20id%3D%22tinyMceEditorclipboard_image_6%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSTRONG%3ESQL%20Server%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3D%3D%3D%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E1.Install%20Polybase%20component.%20Please%20follow%20the%20article%20to%20install%20the%20component%20(please%20follow%20this%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fpolybase%2Fpolybase-linux-setup%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Earticle%3C%2FA%3E%20if%20you%20are%20not%20using%20redhat%2Fcentos)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Esudo%20yum%20install%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E-%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Ey%20mssql%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E-%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Eserver%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E-%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Epolybase%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E2.Restart%20SQL%20Server%20to%20take%20effect.%3C%2FP%3E%0A%3CP%3E3.Enable%20polybase%20feature%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Eexec%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20maroon%3B%22%3Esp_configure%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%40configname%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'polybase%20enabled'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%40configvalue%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E1%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ereconfigure%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3E4.Create%20a%20user%20database.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ecreate%20database%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EpolyDB2%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3E5.Create%20a%20mater%20key%20in%20the%20user%20database.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Euse%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EpolyDB2%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ego%0ACREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'StrongP%40ssword1'%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3E6.create%20the%20credential.%20The%20identify%20stands%20for%20the%20User%20name%20in%20Oracle%20database%2C%20and%20the%20Secret%20is%20the%20password%20of%20the%20user%20.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20DATABASE%20SCOPED%20CREDENTIAL%20%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EOracleCredentialTest%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20IDENTITY%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'user1'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESecret%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'Password1'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3E7.Create%20an%20external%20data%20source%20referring%20the%20credential%20in%20previous%20step.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20EXTERNAL%20DATA%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3ESOURCE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BORACLE%5D%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Elocation%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'Oracle%3A%2F%2F192.168.1.31%3A1521'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREDENTIAL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EOracleCredentialTest%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3E8.Create%20the%20external%20table%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20EXTERNAL%20TABLE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbo%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eemployee%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eid%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ebigint%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ename%20varchar%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E25%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20magenta%3B%22%3ECOLLATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3ELatin1_General_100_BIN2_UTF8%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%20%0A%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Elocation%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'ORCL.user1.employee'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATA_SOURCE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BORACLE%5D%20%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.png%22%20style%3D%22width%3A%20225px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159835i63C53453C4C5DDDD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222.png%22%20alt%3D%222.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAnd%20you%20can%20choose%20which%20columns%20are%20used%20in%20the%20external%20table%20in%20SQL%20Server.%20Following%20query%20is%20valid%20too.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20EXTERNAL%20TABLE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbo%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eemployee%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eid%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ebigint%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%20%0A%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Elocation%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'ORCL.user1.employee'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATA_SOURCE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BORACLE%5D%20%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E9.The%20external%20able%20is%20available%20to%20use%20now.%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorclipboard_image_5%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSTRONG%3E%3CSPAN%20style%3D%22font-size%3A%20xx-large%3B%22%3EErrors%20I%20experienced%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E1.%20Msg%2046530%2C%20Level%2016%2C%20State%2011%2C%20Line%2017%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CFONT%20size%3D%221%22%3EExternal%20data%20sources%20are%20not%20supported%20with%20type%20GENERIC.%3C%2FFONT%3E%3CBR%20%2F%3ETSQL%3A%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CREATE%20EXTERNAL%20DATA%20SOURCE%20%5BORACLE%5D%26nbsp%3B%20WITH%20(%20location%20%3D%20'Oracle%3A%2F%2F192.168.1.31%3A1521'%2CCREDENTIAL%20%3D%20OracleCredentialTest)%3CBR%20%2F%3ESolution%3AMake%20sure%20Polybase%20component%20is%20installed%20and%20'polybase%20enabled'%20is%20set%20to%201%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CBR%20%2F%3E2.%20Msg%20110045%2C%20Level%2016%2C%20State%201%2C%20Line%2021%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CFONT%20size%3D%221%22%3E110045%3BUser%20authorization%20failed%3A%20%5BMicrosoft%5D%5BODBC%20Oracle%20Wire%20Protocol%20driver%5D%5BOracle%5DORA-01017%3A%20invalid%20username%2Fpassword%3B%20logon%20denied%20Additional%20error%20%26lt%3B2%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Oracle%20Wire%20Protocol%20driver%5D%5BOracle%5DORA-01017%3A%20invalid%20username%2Fpassword%3B%20logon%20denied%2C%20SqlState%3A%2028000%2C%20NativeError%3A%201017%3C%2FFONT%3E%3CBR%20%2F%3ETSQL%3A%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20EXTERNAL%20TABLE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbo%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eemployee%0A%20%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eid%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ebigint%0A%20%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%20%20%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Elocation%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'ORCl.user1.employee'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATA_SOURCE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BORACLE%5D%0A%20%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3ESolution%3CBR%20%2F%3E%3D%3D%3D%3CBR%20%2F%3EThis%20error%20is%20obvious.%20Check%20the%20'CREATE%20DATABASE%20SCOPED%20CREDENTIAL%20OracleCredentialTest%20'%2C%20make%20sure%20the%20user%20name%20and%20password%20combination%20is%20corrected.%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CBR%20%2F%3E3.%20Msg%202706%2C%20Level%2016%2C%20State%201%2C%20Line%2021%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CFONT%20size%3D%221%22%3ETable%20'%22user1%22.%22employeE%22'%20does%20not%20exist.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3EPlease%20read%20this%20article%20%3CA%20href%3D%22https%3A%2F%2Fdocs.oracle.com%2Fcd%2FB28359_01%2Fserver.111%2Fb28286%2Fsql_elements008.htm%23SQLRF51109%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.oracle.com%2Fcd%2FB28359_01%2Fserver.111%2Fb28286%2Fsql_elements008.htm%23SQLRF51109%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E1)Based%20on%20my%20understanding%2C%20it%20becomes%20case-sensitive%20if%20you%20create%20table%20with%20quotation%20mark.%3CBR%20%2F%3EPlease%20make%20sure%20the%20schema%20name%20and%20table%20name%20in%20the%20'location'%20exactly%20matches%20the%20one%20you%20used%20in%20Oracle%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20you%20create%20a%20table%20in%20Oracle%20like%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ecreate%20table%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22user1%22%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22employee%22%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22id%22%20number%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E19%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%2C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22name%22%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Evarchar%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E25%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E))%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3ERunning%20following%20TSQL%20will%20cause%20the%20error.%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20EXTERNAL%20TABLE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbo%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eemployee%0A%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eid%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ebigint%0A%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Elocation%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'ORCl.user1.employe%3CSTRONG%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%20background-color%3A%20%23ffff00%3B%22%3EE%3C%2FSPAN%3E%3C%2FSTRONG%3E'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20green%3B%22%3E---The%20last%20E%20is%20upper%20case%2C%20which%20causes%20error.%0A%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATA_SOURCE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BORACLE%5D%0A%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)If%20the%20table%20is%20created%20without%20quotation%20marks%2C%20Oracle%20will%20use%20an%20all-uppcase%20search%20for%20the%20name.%3CBR%20%2F%3EIf%20you%20have%20a%20table%20like%20following%3A%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ecreate%20table%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22user1%22%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eemployee%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22id%22%20number%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E19%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%2C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%22name%22%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Evarchar%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E25%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E))%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3EThe%20word%20'employee'%20in%20the%20'location'%20must%20be%20upper%20case%2C%20else%20it%20raises%20error%202706%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20EXTERNAL%20TABLE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbo%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eemployee%0A%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eid%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ebigint%0A%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Elocation%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'ORCl.user1.EMPLOYEE'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20green%3B%22%3E---if%20any%20character%20of%20'EMPLOYEE'%20is%20lower%20case%2C%202706%20happens.%0A%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATA_SOURCE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BORACLE%5D%0A%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CSTRONG%3EPlease%20note%2C%20this%20rule%20also%20applies%20the%20schema%20name.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.1%20Msg%20105083%2C%20Level%2016%2C%20State%201%2C%20Line%203105083%3BThe%20following%20columns%20in%20the%20user%20defined%20schema%20are%20incompatible%20with%20the%20external%20table%20schema%20for%20table%20'employee'%3A%20user%20defined%20column%3A%20'iD'%20was%20not%20found%20in%20the%20external%20table.%20The%20detected%20external%20table%20schema%20is%3A%20(%5Bid%5D%20DECIMAL(19)%2C%20%5Bname%5D%20VARCHAR(25)%20COLLATE%20Latin1_General_100_BIN2_UTF8).%3C%2FP%3E%0A%3CP%3EThis%20is%20similar%20to%20the%20error%202706.%3CBR%20%2F%3EIf%20the%20column%20is%20created%20with%20quotation%20mark%2C%20the%26nbsp%3B%20name%20of%20column%20in%20the%20'External%20table'%20in%20SQL%20Server%20needs%20to%20exactly%20match%20the%20one%20you%20used%20in%20Oracle.%3CBR%20%2F%3EIf%20the%20column%20is%20created%20without%20quotation%20mark%2C%20the%26nbsp%3B%20name%20of%20column%20in%20the%20'External%20table'%20in%20SQL%20Server%20needs%20to%20be%20upper%20case.%3C%2FP%3E%0A%3CP%3E4.2%20Msg%20105083%2C%20Level%2016%2C%20State%201%2C%20Line%203105083%3BThe%20following%20columns%20in%20the%20user%20defined%20schema%20are%20incompatible%20with%20the%20external%20table%20schema%20for%20table%20'employee'%3A%20user%20defined%20column%3A%20(%5Bname%5D%20VARCHAR(25))%20is%20not%20compatible%20with%20the%20type%20of%20detected%20external%20table%20column%3A%20(%5Bname%5D%20VARCHAR(25)%20COLLATE%20Latin1_General_100_BIN2_UTF8).%20The%20detected%20external%20table%20schema%20is%3A%20(%5Bid%5D%20DECIMAL(19)%2C%20%5Bname%5D%20VARCHAR(25)%20COLLATE%20Latin1_General_100_BIN2_UTF8).%3C%2FP%3E%0A%3CP%3EIn%20case%20the%20default%20collation%20in%20sql%20server%20database%20is%20not%20the%20collation%20in%20Oracle%2C%20you%20need%20to%20specify%20the%20collation%20explicitly%20used%20in%20Oracle.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20EXTERNAL%20TABLE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbo%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E.%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eemployee%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Eid%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ebigint%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ename%20varchar%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E25%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20magenta%3B%22%3E%3CSTRONG%3ECOLLATE%3C%2FSTRONG%3E%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%3CSTRONG%3E%3CSPAN%20style%3D%22background-color%3A%20%23ffff00%3B%22%3ELatin1_General_100_BIN2_UTF8%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%20%3CBR%20%2F%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E(%3CBR%20%2F%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Elocation%3C%2FSPAN%3E%3D%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'ORCL.user1.employee'%3C%2FSPAN%3E%2C%3CBR%20%2F%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATA_SOURCE%3C%2FSPAN%3E%3D%20%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BORACLE%5D%3CBR%20%2F%3E%3C%2FSPAN%3E)%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1044636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sql-loves-linux_2_twitter-002-640x358-100648879-large.png%22%20style%3D%22width%3A%20579px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159832i583A1A0C41D53CC1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22sql-loves-linux_2_twitter-002-640x358-100648879-large.png%22%20alt%3D%22sql-loves-linux_2_twitter-002-640x358-100648879-large.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Apr 28 2020 08:12 AM
Updated by: