I wrote this small script to ease replications tasks. It provides error handling and script housekeeping statistics and reporting by email. I thought I my be useful for others.
In my case this script runs everyday and replicates 3.2 million of records (with many rows) in about 15 minutes, from 45 tables from a PostgreSQL and Exact Online to SqlServer.
--
-- First we create a simple temp table containing the table names that we want to retrieve.
--
create or replace table table_list@inmemorystorage
( source varchar2
, table_name varchar2
, records_inserted integer
, exectime decimal
);
--
-- Add as much inserts as you need, fields [records_inserted] and [exectime] will be filled when the stored procedure runs to give feedback on statistics.
--
insert into table_list@inmemorystorage
(source, table_name)
values
('source_container_name_1','table_name_1')
;
insert into table_list@inmemorystorage
(source, table_name)
values
('source_container_name_1','table_name_2')
;
--
--The stored procedure starts here.
--
declare
l_error_txt varchar2 := '';
l_error_cnt varchar2 := 0;
l_script_start datetime := sysdateutc;
l_script_elapsedtime decimal;
l_script_end datetime;
l_instructionstart datetime;
l_instructionduration decimal;
l_stmt varchar2;
l_sqlrowcount integer;
l_email_table varchar2;
begin
set smtp-host-address@mail 'your.stmtp.com'; -- fill here your smtp
set smtp-minimum-deliver-duration-ms@mail 5000;
set smtp-user-name@mail 'youremail@domain.com'; -- fill here your the email used to send from
set smtp-password@mail 'password'; -- fill here your password
set [mail-from-name]@mail 'email name'; -- fill here the mail-from-name
set [mail-from-email]@mail 'youremail@domain.com'; -- fill here the mail-from-email
set [mail-body-html]@mail false;
set smtp-enable-ssl@mail true;
set mail-priority@mail -1;
set smtp-minimum-deliver-duration-ms@mail 1000;
set mail-body-html@mail true;
set mail-priority@mail 0;
for r in
( select *
from table_list@inmemorystorage
where source = 'source_container_name_1'
)
--
-- Fill here the source_container_name_1 that is the [source] field in the temp table.
--
loop
begin
l_instructionstart := sysdateutc;
l_stmt := 'Create or replace table ' || r.table_name || '@destination_container as SELECT * FROM ' || r.table_name || '@source_container';
--
-- Fill here the @destination_container and @source_container aliases given in your invantive connection string.
--
execute immediate l_stmt into l_sqlrowcount;
l_sqlrowcount := l_sqlrowcount - 1;
l_instructionduration := round((sysdateutc - l_instructionstart)*60*60*24,3); --into seconds
l_stmt := 'update table_list@inmemorystorage SET records_inserted = ' || l_sqlrowcount || ', exectime = ' || cast(l_instructionduration as text) || ' where table_name = ''' || r.table_name || '''';
execute immediate l_stmt;
exception
when others
then
l_error_txt := l_error_txt
|| chr(13) || chr(10)
|| sqlerrm
;
l_error_cnt := l_error_cnt + 1;
end;
end loop;
--
-- Here the script finished to work on the tables listed in the temp table thanks to the loop stmt
-- below we add another step to connect to Exact On Line and add a replication of one table into @destination_container
-- if you need more table then you may use the loop scenario above
--
begin
use your_devision_1@eol, your_devision_2@eol; -- fill in your eol divisions here, with the invantive alias [@eol]
l_instructionstart := sysdateutc;
create or replace table eol_xml_AccountReceivables@destination_container
as
select *
from aroutstandingitemsex@eol
;
-- change destination container, table name and eol allias as needed
--
l_sqlrowcount := sqlrowcount;
l_instructionduration := round((sysdateutc - l_instructionstart)*60*60*24,3); --into seconds
insert into table_list@inmemorystorage (source, table_name, records_inserted, exectime) values ('eol', 'aroutstandingitemsex', l_sqlrowcount, l_instructionduration);
l_script_end := sysdateutc;
exception
when others
then
l_error_txt := l_error_txt
|| chr(13) || chr(10)
|| sqlerrm
;
l_error_cnt := l_error_cnt + 1;
end;
--
-- Creating the email table with the results statistics.
--
l_email_table := '<table border = 1>
<tr>
<th>source</th>
<th>table name</th>
<th>records inserted</th>
<th>exec time(s)</th>
</tr>';
for r in (select * from table_list@inmemorystorage)
loop
begin
l_email_table := l_email_table
||'<tr><td>'
||r.source
||'</td><td>'||r.table_name
||'</td><td style="text-align:right">'||r.records_inserted
||'</td><td style="text-align:right">'||r.exectime
||'</td></tr>'
;
end;
end loop;
-- dbms_output.put_line(l_email_table); -- if you need to see this in dbms window
--
-- Sending email.
--
insert into smtp@mail
( toEmail
, ccEmail
, bccEmail
, subject
, body
, attachmentContents
, attachmentName
, attachmentMimeType
)
select 'destination_email@domain.com' -- add here your recipients separed by a [,]
toEmail
, cast(null as varchar2)
ccEmail
, cast(null as varchar2)
bccEmail
, 'Invantive replication script'
subject
, 'Invantive replication script as run<br>'
|| 'script start time: ' || dateadd('hour','2',l_script_start) || '<br>'
|| 'full script elapsed time : ' || round((l_script_end - l_script_start)*60*24,3) || ' minutes' || '<br>'
|| 'Errors count: ' || l_error_cnt || '<br>'
|| 'Errors msg: ' || l_error_txt || '<br>'
|| '<br>detailed data of replicated tables :<br>'
|| l_email_table
body
, null -- rfe.file_contents
attachmentContents
, null -- basename('{USER_MULTI_PARTIES_FILE_NAME}')
attachmentName
, null -- cast(null as varchar2)
attachmentMimeType;
--
-- Do some cleanup.
--
drop table table_list@inmemorystorage;
end