December 24, 2020 at 12:01 pm
Hello everybody,
I am trying to use external table with PolyBase for import data from Oracle to Sql Server 2019.
I have an issue about creating an external table.
I created external data source OracleExternalData.
CREATE EXTERNAL DATA SOURCE OracleExternalData
WITH
( LOCATION = 'oracle://145.145.145.145:1521',
CONNECTION_OPTION = 'DSN=CData Oracle Sys'
CREDENTIAL = OracleProxyAccount
) ;
I couldn't create external table. I think that part in the locatio isn't good.
CREATE EXTERNAL TABLE payment5(
payment_id INT NOT NULL,
customer_id SMALLINT NOT NULL,
staff_id SMALLINT NOT NULL,
rental nvarchar(50) NOT NULL,
amount DECIMAL(5, 2) NOT NULL,
payment_date DATETIME2(6) NOT NULL
)
WITH (
LOCATION=’.schema.payment5’,
DATA_SOURCE=OracleExternalData
);
I got an error:
Msg 105070, The ODBC data source name was no specified or the driver is missing.
2. I put in location schema.table:
CREATE EXTERNAL TABLE payment5(
payment_id INT NOT NULL,
customer_id SMALLINT NOT NULL,
staff_id SMALLINT NOT NULL,
rental nvarchar(50) NOT NULL,
amount DECIMAL(5, 2) NOT NULL,
payment_date DATETIME2(6) NOT NULL
)
WITH (
LOCATION=’schema.payment5’,
DATA_SOURCE=OracleExternalData
);
I got error:
Msg 105077, The specific location string ’schema.payment5’ could not be parsed. A 2-part inedentifier was found. Excepted 3.
3. I put in location database.schema.table:
CREATE EXTERNAL TABLE payment5(
payment_id INT NOT NULL,
customer_id SMALLINT NOT NULL,
staff_id SMALLINT NOT NULL,
rental nvarchar(50) NOT NULL,
amount DECIMAL(5, 2) NOT NULL,
payment_date DATETIME2(6) NOT NULL
)
WITH (
LOCATION=’service1.schema.payment5’,
DATA_SOURCE=OracleExternalData
);
I got error:
Msg 105076, The specific location string ’service1.schema.payment5’ could not be parsed.
4. I created synonym for schema.table@database in Oracle database. And I put synonym in location:
CREATE EXTERNAL TABLE payment5(
payment_id INT NOT NULL,
customer_id SMALLINT NOT NULL,
staff_id SMALLINT NOT NULL,
rental nvarchar(50) NOT NULL,
amount DECIMAL(5, 2) NOT NULL,
payment_date DATETIME2(6) NOT NULL
)
WITH (
LOCATION=’synonym’,
DATA_SOURCE=OracleExternalData
);
I got error:
Msg 105077, The specific location string ’’ could not be parsed. A 1-part inedentifier was found. Excepted 3.
When I connect with SQL developer studio to Oracle I use
Hostname: 145.145.145.145,
Port: 1521
Service name: service1
I think that part in LOCATION in my script isn't god. Could anybody help me?
Thank you in advance for help me.
December 25, 2020 at 12:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 30, 2020 at 2:11 pm
Are you using a stored procedure or SSIS? Why not have Oracle just create a file to import into SQL? Are you using Oracle Instant Client ODBC Installation? I know a lot of people who have had issues just because if not using right Oracle Client.
January 4, 2021 at 9:55 am
I used Oracle Client and I imported data from Oracle using SSIS.
But I want to see how work new feature in SQL 2019 with External Oracle table? Is it faster or not?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply