DECLARE
   CURSOR ord_prog
   IS
      Select column_list from <target_table>@dblink;

   TYPE order_tab IS TABLE OF ord_prog%ROWTYPE;

   o_data          order_tab;
--   Local Variabl declartion
   l_array_size    NUMBER                           := 1000;
   l_error_count   NUMBER                           := 0;
   ex_dml_errors   EXCEPTION;
   PRAGMA EXCEPTION_INIT (ex_dml_errors, -24381);
   ls_id_prgm      t_err_log_pm.telp_prg_id%TYPE    := 'I2PMB059';
   ls_err_msg      VARCHAR2 (1000);
   ls_param        t_err_log_pm.telp_remarks%TYPE;
   ln_test         INTEGER                          := 0;

--   Error Handling procedure
   PROCEDURE i2pmb666 (
      ld_date       DATE DEFAULT SYSDATE,
      ls_err_msg    VARCHAR2,
      ls_id_prgm    VARCHAR2,
      ls_param      VARCHAR2,
      ls_err_date   DATE DEFAULT SYSDATE,
      ls_user_id    VARCHAR2 DEFAULT NULL
   )
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO t_err_log_pm
                  (telp_date, telp_prg_id, telp_error_code_msg,
                   telp_remarks, telp_user_id
                  )
           VALUES (ld_date, ls_id_prgm, ls_err_msg,
                   ls_param, ls_user_id
                  );

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         DBMS_OUTPUT.put_line (   SQLERRM
                               || 'error in inserting into the log table '
                              );
   END;

BEGIN
--   Delete of T_UD_HR_ORDER_PROG is handled before
   OPEN ord_prog;

   LOOP
      FETCH ord_prog
      BULK COLLECT INTO o_data LIMIT l_array_size;

--      Start Bulk Insert of Order progress data and log errors in case any
      BEGIN
         FORALL i IN 1 .. o_data.COUNT SAVE EXCEPTIONS
            INSERT INTO <target_table>
                 VALUES o_data (i);
         COMMIT;
         EXIT WHEN ord_prog%NOTFOUND;
         ln_test := ln_test + l_array_size;
         DBMS_OUTPUT.put_line (ln_test);
      EXCEPTION
         WHEN ex_dml_errors
         THEN
            ROLLBACK;
            l_error_count := SQL%BULK_EXCEPTIONS.COUNT;

            FOR i IN 1 .. l_error_count
            LOOP
               ls_err_msg :=
                     'Step-3::INS_ORD_PROG::'
                  || '::'
                  || SQLERRM (-SQL%BULK_EXCEPTIONS (i).ERROR_CODE);
               ls_param :=
                     o_data (SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).tho_id_order
                  || '-'
                  || o_data (SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).tho_id_order_item
                  || '-'
                  || o_data (SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).tho_id_part_order;
               i2pmb666 (SYSDATE, ls_err_msg, ls_id_prgm, ls_param, SYSDATE);
            END LOOP;
         WHEN OTHERS
         THEN
            ROLLBACK;
            ls_err_msg :=
               'Step-4::INS_ORD_PROG::' || '::' || SQLERRM || '::' || SQLCODE;
            ls_param := '-';
            i2pmb666 (SYSDATE, ls_err_msg, ls_id_prgm, ls_param, SYSDATE);
      END;
   END LOOP;

   CLOSE ord_prog;
EXCEPTION
   WHEN OTHERS
   THEN
      ls_err_msg :=
           'Step-6::PRGM_LEVEL_ERROR::' || '::' || SQLERRM || '::' || SQLCODE;
      ls_param := '-';
      i2pmb666 (SYSDATE, ls_err_msg, ls_id_prgm, ls_param, SYSDATE);
END;