Compare Regular Cursor vs Bulk Collect

=====================================
General
=====================================
Overview: 
There is a table, which is source table.
The records from this table should be processed, and inserted into a target table.
Once done, records from source table are deleted.

This Package handles same task in two ways:
convertSdr2CdrCursor
convertSdr2CdrBulk

convertSdr2CdrCursor
The records are selected via regular cursor, and processed one by one.

convertSdr2CdrBulk

The records are selected via BULK COLLECT, and processed one by one.

Since the processing is done one by one, there is no real advantage for using BULK COLLECT.

=====================================
The time to execute in both options
=====================================

convertSdr2CdrCursorDelete 50000 records using regular cursor

Time to Execute: 999(sec), rows deleted: 50000
50000/999=50 rows/sec

convertSdr2CdrBulk
Delete 50000 records using BULK COLLECT
Time to Execute: 1249(sec), rows deleted: 60762

60762/1249 = 48 rows/sec

Both options were far superior to not using bind variables.
Without using bind variables, the time was 6 rows/sec.

=====================================
Code
=====================================

CREATE OR REPLACE PACKAGE BODY SDR_2_CDR_CONVERT_ALEC AS

PROCEDURE write_to_log(p_data in varchar2) is
  pragma autonomous_transaction;

begin
  insert into SGA_W_LOG (procedure_name, data, ts_last_modified) values ('ConvertSdr2Cdr', p_data, sysdate);
  commit;

end;

-------------------------------------------------------
-- Process using Regular Cursor
-------------------------------------------------------

PROCEDURE convertSdr2CdrCursor (pProductName in varchar2) is

  cursor SdrCrs(pProductName in varchar2) is
   SELECT  cdr_id,
           outgoing_call_direction
     from DEBUG_GEN_W_NEW_SDR_ALEC
     where rownum < 50000
     and product_name = pProductName
     and ((pProductName = 'MOCO' and event_name = 'IDP') OR (pProductName <> 'MOCO'))
     for update;

  Vsql         VARCHAR2(5000);
  v_sql_type1  VARCHAR2(5000);
  v_sql_type2  VARCHAR2(5000);
  
  v_start_date DATE;
  v_end_date   DATE;
  v_seconds    NUMBER;
  
BEGIN
  v_start_date := SYSDATE;
  write_to_log('start running convertSdr2CdrCursor procedure at '||TO_CHAR(v_start_date,'YYYYMMDD hh24:mi:ss') );
  
  v_sql_type1 := GenerateCdr(1);
  v_sql_type2 := GenerateCdr(2);

  -- for each SDR create the mapped CDRs
  for SdrRec in SdrCrs(pProductName) loop

    begin

    Vsql := v_sql_type1;

-- create the insert statement and execute it
    Vsql := 'insert into CDR_ALEC select ' || Vsql || ' from DEBUG_GEN_W_NEW_SDR_ALEC where cdr_id = :b1';
    EXECUTE IMMEDIATE Vsql USING SdrRec.cdr_id;


    -- Generate CDR for Leg2
    if SdrRec.outgoing_call_direction is not null then
       Vsql :=v_sql_type2;
       Vsql := 'insert into CDR_ALEC select ' || Vsql || ' from DEBUG_GEN_W_NEW_SDR_ALEC where cdr_id = :b1';
       EXECUTE IMMEDIATE Vsql USING SdrRec.cdr_id;
    end if;

    delete DEBUG_GEN_W_NEW_SDR_ALEC WHERE CURRENT OF SdrCrs;

    EXCEPTION
      WHEN OTHERS THEN
            write_to_log('Error for CDR_ID: '|| SdrRec.Cdr_Id || ' - ' ||sqlerrm);
            UPDATE DEBUG_GEN_W_NEW_SDR_ALEC
              SET ROAMING_TYPE ='9'
            WHERE CURRENT OF SdrCrs;
    END;
  END LOOP;

  COMMIT;
  
  v_end_date := SYSDATE;
  write_to_log('convertSdr2CdrCursor procedure completed at '||TO_CHAR(v_end_date,'YYYYMMDD hh24:mi:ss'));
  v_seconds := ROUND((v_end_date - v_start_date)*1440*60);
  write_to_log('Time to Execute: '||v_seconds||'(sec)');
  
end convertSdr2CdrCursor;


-------------------------------------------------------
-- Process using BULK COLLECT
-------------------------------------------------------
PROCEDURE convertSdr2CdrBulk(p_product_name IN VARCHAR2) IS

  TYPE sdr_record IS RECORD (
      sdr_rowid               ROWID,
      cdr_id                  DEBUG_GEN_W_NEW_SDR_ALEC.cdr_id%TYPE,
      outgoing_call_direction DEBUG_GEN_W_NEW_SDR_ALEC.outgoing_call_direction%TYPE
  );

  TYPE sdr_record_tab IS TABLE OF sdr_record;
  v_sdr_records   sdr_record_tab;

  CURSOR sdr_cur IS
  SELECT  ROWID, cdr_id, outgoing_call_direction
    FROM DEBUG_GEN_W_NEW_SDR_ALEC
   WHERE 1=1
     AND product_name = p_product_name
     AND ((p_product_name = 'MOCO' and event_name = 'IDP') OR (p_product_name <> 'MOCO'));
   
  v_sql                        VARCHAR2(5000);
  v_insert_sql                 VARCHAR2(5000);
  v_sql_type1                  VARCHAR2(5000);
  v_sql_type2                  VARCHAR2(5000);
  v_outgoingCallDirection      SDR_2_CDR_MAPPING_NEW.sdr_column_name%TYPE;

  v_start_date                DATE;
  v_end_date                  DATE;
  v_seconds                   NUMBER;
  v_cdr_id                    DEBUG_GEN_W_NEW_SDR_ALEC.cdr_id%TYPE;
  c_limit                     NUMBER := 50000;
  
BEGIN
  
  v_start_date := SYSDATE;
  write_to_log('Start running ConvertSdr2CdrBulk procedure at '||TO_CHAR(v_start_date,'YYYYMMDD hh24:mi:ss') );
  
  v_sql_type1 := GenerateCdr(1);
  v_sql_type2 := GenerateCdr(2);

  SELECT sdr_column_name INTO v_outgoingCallDirection
    FROM SDR_2_CDR_MAPPING_NEW
   WHERE leg_id = 2
    AND cdr_column_name = 'CALL_DIRECTION';

  write_to_log('Before Opening Cursor');
  
   OPEN sdr_cur;
   --Loop on Cursor
   LOOP
     BEGIN   
       FETCH sdr_cur 
         BULK COLLECT INTO v_sdr_records LIMIT c_limit;

         IF sdr_cur%NOTFOUND THEN
             write_to_log('No More Data to Fetch from DB');
         END IF;  
        
         write_to_log('v_sdr_records.COUNT : '||v_sdr_records.COUNT );
         write_to_log('Processing Data');
         --Loop on the Bulk records
         FOR indx IN 1 .. v_sdr_records.COUNT LOOP
          
           BEGIN
             -- create the insert statement and execute it
             v_sql := v_sql_type1;           
             v_insert_sql := 'INSERT INTO CDR_ALEC SELECT ' || v_sql || ' FROM DEBUG_GEN_W_NEW_SDR_ALEC WHERE cdr_id = :b1';
             v_cdr_id :=  v_sdr_records(indx).cdr_id;
             EXECUTE IMMEDIATE v_insert_sql USING v_cdr_id;

             --check whether to create 2nd CDR for outgoing leg
             IF v_sdr_records(indx).outgoing_call_direction IS NOT NULL THEN
               v_sql := v_sql_type2;
               v_insert_sql := 'INSERT INTO CDR_ALEC SELECT ' || v_sql || ' FROM DEBUG_GEN_W_NEW_SDR_ALEC WHERE cdr_id = :b1';               
               EXECUTE IMMEDIATE v_insert_sql USING  v_sdr_records(indx).cdr_id;
             END IF; 

             DELETE DEBUG_GEN_W_NEW_SDR_ALEC WHERE DEBUG_GEN_W_NEW_SDR_ALEC.rowid = v_sdr_records(indx).sdr_rowid;
           EXCEPTION
             WHEN OTHERS THEN
               write_to_log('ConvertSdr2CdrBulk Error for CDR_ID: '|| v_sdr_records(indx).cdr_id || ' Error Details: ' ||SQLERRM);
               UPDATE DEBUG_GEN_W_NEW_SDR_ALEC
               SET ROAMING_TYPE ='9'
             WHERE cdr_id =  v_sdr_records(indx).cdr_id;
           END;   
                

        END LOOP;
        EXIT WHEN sdr_cur%NOTFOUND;

     EXCEPTION
       WHEN OTHERS THEN
         write_to_log('Unexpected Error in ConvertSdr2CdrBulk: '||SQLERRM);          
     END;
     
     END LOOP;
     CLOSE sdr_cur;
     COMMIT;
  v_end_date := SYSDATE;
  write_to_log('ConvertSdr2CdrBulk procedure completed at '||TO_CHAR(v_end_date,'YYYYMMDD hh24:mi:ss'));
  v_seconds := ROUND((v_end_date - v_start_date)*1440*60);
  write_to_log('Time to Execute: '||v_seconds||'(sec)');
  

END ConvertSdr2CdrBulk;

END SDR_2_CDR_CONVERT_ALEC;