sqlplus ${DB_LOGIN} > $sqlFileOutput  2>&1 <<-ENDSQL
set sqlprompt ''
set sqlnumber off
set serveroutput on
set heading off
set feedback off
spool $tmpSpoolFile

whenever sqlerror exit failure

DEFINE SUCCESS            = 0
DEFINE FAILURE            = 1

--*******************************************************************
--Declare bind variables
--*******************************************************************
VARIABLE v_ins_rows  NUMBER
VARIABLE v_rej_rows  NUMBER

--*******************************************************************
--PL/SQL
--*******************************************************************
DECLARE

   x_RECORD_ALREADY_EXISTS EXCEPTION;
   PRAGMA EXCEPTION_INIT(x_RECORD_ALREADY_EXISTS, -1);
   x_PARTITION_DOES_NOT_EXIST EXCEPTION;
   PRAGMA EXCEPTION_INIT(x_PARTITION_DOES_NOT_EXIST, -14400);
   ex_dml_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);


   TYPE get_scripts_cur_typ IS REF CURSOR;
   cv_get_scripts get_scripts_cur_typ;

   v_FETCH_ROWS NUMBER := $COMMIT_CNT;

   TYPE FILL_DT_tab           IS TABLE OF PAS.PAS_SIG_SCRIPT_TMP.FILL_DT%TYPE   INDEX BY PLS_INTEGER;
   TYPE STORE_NBR_tab         IS TABLE OF PAS.PAS_SIG_SCRIPT_TMP.STORE_NBR %TYPE  INDEX BY PLS_INTEGER;
   TYPE RX_NBR_tab            IS TABLE OF PAS.PAS_SIG_SCRIPT_TMP.RX_NBR%TYPE   INDEX BY PLS_INTEGER;
   TYPE SIG_SEQ_NBR_tab       IS TABLE OF PAS.PAS_SIG_CD.SIG_SEQ_NBR%TYPE  INDEX BY PLS_INTEGER;
   TYPE INSERT_DT_TME_tab     IS TABLE OF PAS.PAS_SIG_SCRIPT_TMP.INSERT_DT_TME%TYPE  INDEX BY PLS_INTEGER;

   fill_dt_arr         FILL_DT_tab;
   store_nbr_arr       STORE_NBR_tab;
   rx_nbr_arr          RX_NBR_tab;
   sig_seq_nbr_arr     SIG_SEQ_NBR_tab;
   insert_dt_tme_arr   INSERT_DT_TME_tab;

 BEGIN

      --****************************************************************
      --Initialize bind variables
      --****************************************************************
      :v_rej_rows := 0;
      :v_ins_rows := 0;

      OPEN cv_get_scripts
       FOR 'SELECT FILL_DT,
            STORE_NBR ,
            RX_NBR,
            SIG_SEQ_NBR,
            SCT.INSERT_DT_TME
       FROM PAS.PAS_SIG_SCRIPT_TMP SCT, PAS.PAS_SIG_CD SC
       WHERE SCT.SIG_CD_DSC = SC.SIG_CD_DSC';

        LOOP
            FETCH cv_get_scripts BULK COLLECT INTO fill_dt_arr,store_nbr_arr, rx_nbr_arr, sig_seq_nbr_arr, insert_dt_tme_arr  LIMIT 100;

            BEGIN
                  FORALL i IN store_nbr_arr.FIRST .. store_nbr_arr.LAST SAVE EXCEPTIONS
                                INSERT INTO PAS.PAS_SIG_SCRIPT(
                                                               SIG_SCRIPT_SEQ_NBR,
                                                               FILL_DT,
                                                               STORE_NBR,
                                                               RX_NBR,
                                                               SIG_SEQ_NBR,
                                                               INSERT_DT_TME,
                                                               CUR_IND)
                                                        VALUES(
                                                               PAS.SIG_SCRIPT_SEQ_NBR.NEXTVAL,
                                                               fill_dt_arr(i),
                                                               store_nbr_arr (i),
                                                               rx_nbr_arr (i),
                                                               sig_seq_nbr_arr(i),
                                                               insert_dt_tme_arr(i),
                                                               'N');
          EXCEPTION

          WHEN x_RECORD_ALREADY_EXISTS OR x_PARTITION_DOES_NOT_EXIST OR ex_dml_errors THEN
          :v_rej_rows := :v_rej_rows + SQL%BULK_EXCEPTIONS.COUNT;

          WHEN OTHERS THEN
          ROLLBACK;
          DBMS_OUTPUT.PUT_LINE('ERROR IN BULK INSERT OPERATION: ' || SQLERRM);
          END;

          --*************************************************************
          --Gather metrics
          --*************************************************************
          IF (SQL%FOUND = TRUE) THEN
          :v_ins_rows := :v_ins_rows + SQL%ROWCOUNT;
          END IF;
          COMMIT;
          EXIT WHEN cv_get_scripts%NOTFOUND;
     END LOOP;
     CLOSE cv_get_scripts;

     COMMIT;

   EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
      ROLLBACK;
  END;
/

--*******************************************************************
--PL/SQL
--*******************************************************************
BEGIN
 --Display metrics
 DBMS_OUTPUT.put_line ('Inserted Rows:' || :v_ins_rows);
 DBMS_OUTPUT.put_line ('Rejected Rows:' || :v_rej_rows);

 END;
/

   ANALYZE TABLE PAS.PAS_SIG_SCRIPT ESTIMATE STATISTICS;

quit
ENDSQL