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
[email protected] (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 := [email protected];
[email protected] (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 := [email protected] (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;
[email protected] (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)
);
[email protected] (l_cursor);
RAISE;
END fetch_intervaldata;
