Tables replication automation with stored procedure giving statistics & error handling by email

Hi everyone,
I wrote this small script to ease replications tasks.
it gives error handling + 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 PostGre and Eol 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
  );
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');
-- 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
--
--
--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