I have a requirement to replicate data from a MS SQL (AWS EC2) table to an Oracle (AWS RDS) instance. After several failed approaches, I decided to create a Linked Server in SQL to the Ora database and insert new data on a trigger event. So far, so good.
Now I am attempting to insert data into a single Oracle table from an [identical] SQL table, but receive the following error
"The OLE DB provider "OraOLEDB.Oracle" for linked server "desora12" supplied invalid metadata for column "CREATE_DATE". The data type is not supported."
I've tried taking CREATE_DATE out of the insert statement, but receive the same error. I also tried casting and converting the CreateDate in the select statement. Same error.
The CREATE_DATE column type in Oracle is TIMESTAMP(6). The corresponding CreateDate column type in SQL is nullable DateTime
There are 31 columns in the table, here is an abridged version of the insert.
INSERT INTO [desora12]..[DATAENTRY].[TBL_API_ITEM_TYPEID1_LBL_TEST]
([SITE_ID]
,[USER_ID]
,[CREATE_DATE]
,[TRANSFER_DATE]
,[TRANSFER_STATUS])
SELECT TOP 1 SiteID,
UserID,
CreateDate,
TransferDate,
TransferStatus
FROM API.ItemDataTypeID1Label
WHERE TransferDate is null
AND TransferStatus is null
AND ReturnStatus=200
Any help?
I always do "pulls" to transfer data, rather than a "push" like you are doing. Pulls ensure that you aren't harming a production database when refreshing a development or test database. As far as the dates go, I struggled with the date type between Oracle and SQL Server. I eventually converted my Oracle date to text (remember I am pulling) and allowed SQL Server to automatically convert the text to date. You can see this in my call to bindvar below where I convert the Oracle date to a string.
If you are pulling from Oracle, look into DBMS_HS_PASSTHROUGH. It is many times faster than selecting through a database link. I am fetching 2 million records at a time, my run time went from 4 1/2 hours to under 5 minutes.
PROCEDURE bindvar (
p_cursor IN INTEGER
, p_pos IN INTEGER
, p_value IN VARCHAR2
)
AS
l_routine CONSTANT oracleobj_t := 'bindvar';
BEGIN
DBMS_HS_PASSTHROUGH.bind_variable@intervaldata.world (p_cursor, p_pos, p_value);
EXCEPTION
WHEN OTHERS
THEN
make_log_error_entry (
p_routine => l_routine
, p_message => cealogging.activity_log_maintenance_pkg.labels (
'p_cursor'
, p_cursor
, 'p_pos'
, p_pos
, 'p_value'
, p_value
)
);
RAISE;
END;
-- ***********************************************************************
-- Fetch Interval Data
-- Purpose:
-- Retrieve interval data from SQL*Server
-- Arguments:
-- p_earliestintervaldate - earliest interval date from which data will be fetched
-- p_latestintervaldate - latest interval date from which data will be fetched
-- p_earliestlogtime - earliest log date for which data will be fetched
-- p_latestlogtime - latest log date for which data will be fetched
-- p_maxrecords - maximum records to fetch from SQL Server
-- ***********************************************************************
FUNCTION fetch_intervaldata (
p_earliestintervaldate IN DATE
, p_latestintervaldate IN DATE
, p_earliestlogdate IN DATE
, p_latestlogdate IN DATE
, p_meterno IN VARCHAR2 DEFAULT NULL
, p_maxrecords IN INTEGER DEFAULT NULL
)
RETURN PLS_INTEGER
AS
l_routine CONSTANT oracleobj_t := 'fetch_intervaldata';
l_format CONSTANT oracleobj_t := 'YYYYMMDD HH24:MI:SS';
l_cnt PLS_INTEGER;
l_cursor INTEGER;
l_earliestlogdate DATE := p_earliestlogdate;
l_numrows INTEGER;
l_row intervaldata_load%ROWTYPE;
l_ret PLS_INTEGER := 0;
l_sql VARCHAR2 (200)
:= ';select * from cea.fetchCisIntervalData( ?, ?, ?, ?, ?) where interval_read is not null';
l_latestlogtimearg DATE;
BEGIN
close_databaselink (p_link => 'INTERVALDATA.WORLD');
EXECUTE IMMEDIATE 'truncate table intervaldata_load';
-- set l_cnt = 1 to allow the first pass to run
-- thereafter it is the number or records returned by the pass that will
-- be tested for continuation
l_cnt := 1;
WHILE l_earliestlogdate <= p_latestlogdate
AND l_cnt > 0
AND (p_maxrecords IS NULL
OR l_ret < p_maxrecords)
LOOP
make_log_entry (
p_routine => l_routine
, p_message => 'processing starting for ' || TO_CHAR (l_earliestlogdate, c_intervaldateformat)
);
l_cursor := DBMS_HS_PASSTHROUGH.open_cursor@intervaldata.world;
DBMS_HS_PASSTHROUGH.parse@intervaldata.world (l_cursor, l_sql);
bindvar (p_cursor => l_cursor, p_pos => 1, p_value => TO_CHAR (l_earliestlogdate, l_format));
bindvar (
p_cursor => l_cursor
, p_pos => 2
, p_value => TO_CHAR (l_earliestlogdate + INTERVAL '6' HOUR - INTERVAL '1' SECOND, l_format)
);
bindvar (p_cursor => l_cursor, p_pos => 3, p_value => TO_CHAR (p_earliestintervaldate, l_format));
bindvar (p_cursor => l_cursor, p_pos => 4, p_value => TO_CHAR (p_latestintervaldate, l_format));
bindvar (p_cursor => l_cursor, p_pos => 5, p_value => p_meterno);
l_cnt := 0;
LOOP
l_numrows := DBMS_HS_PASSTHROUGH.fetch_row@intervaldata.world (l_cursor);
EXIT WHEN l_numrows = 0
OR (p_maxrecords IS NOT NULL
AND l_ret >= p_maxrecords);
DBMS_HS_PASSTHROUGH.get_value@intervaldata (l_cursor, 1, l_row.meterno);
DBMS_HS_PASSTHROUGH.get_value@intervaldata (l_cursor, 2, l_row.interval_start);
DBMS_HS_PASSTHROUGH.get_value@intervaldata (l_cursor, 3, l_row.endpointid);
DBMS_HS_PASSTHROUGH.get_value@intervaldata (l_cursor, 4, l_row.logtime);
DBMS_HS_PASSTHROUGH.get_value@intervaldata (l_cursor, 5, l_row.interval_read);
DBMS_HS_PASSTHROUGH.get_value@intervaldata (l_cursor, 6, l_row.buy_back);
DBMS_HS_PASSTHROUGH.get_value@intervaldata (l_cursor, 7, l_row.phase_a);
DBMS_HS_PASSTHROUGH.get_value@intervaldata (l_cursor, 8, l_row.phase_b);
DBMS_HS_PASSTHROUGH.get_value@intervaldata (l_cursor, 9, l_row.phase_b);
EXIT WHEN l_row.logtime > p_latestlogdate;
INSERT INTO intervaldata_load
VALUES l_row;
l_cnt := l_cnt + 1;
l_ret := l_ret + 1;
END LOOP;
DBMS_HS_PASSTHROUGH.close_cursor@intervaldata.world (l_cursor);
make_log_entry (
p_routine => l_routine
, p_message => LPAD (l_cnt, 10)
|| ' records retrieved for '
|| TO_CHAR (l_earliestlogdate, c_intervaldateformat)
|| ' to '
|| TO_CHAR (l_earliestlogdate + INTERVAL '6' HOUR, c_intervaldateformat)
);
l_earliestlogdate := l_earliestlogdate + INTERVAL '6' HOUR;
END LOOP;
RETURN l_ret;
EXCEPTION
WHEN OTHERS
THEN
make_log_error_entry (
p_routine => l_routine
, p_message => 'processing ' || TO_CHAR (l_earliestlogdate, l_format)
);
DBMS_HS_PASSTHROUGH.close_cursor@intervaldata.world (l_cursor);
RAISE;
END fetch_intervaldata;