Oracle PL/SQL how do you output how many inserts have been made in a FORALL statement

Question

What's the best way of getting and outputting how many rows have been inserted in the FORALL statement I have below. I've seen the SQL%BULK_ROWCOUNT but I'm not sure how that would work in the below statement.

is it

DBMS_OUTPUT.('rows inserted '||SQL%BULK_ROWCOUNT||'');

Does the above need to go in another FORALL statement? For the code below how would I achieve this?

DECLARE
  TYPE t_arc_act_plus_trigger1 IS TABLE OF arc_act_plus_triggers1%ROWTYPE;
  v_arc_act_plus_triggers1 t_arc_act_plus_trigger1;

  CURSOR c_arc_act_plus_triggers1 IS
      SELECT /*+ PARALLEL */ apt.*
        FROM act_plus_triggers1 apt
       WHERE NOT EXISTS
                (SELECT 1
                   FROM act_plus_triggers_copy1 aptc
                  WHERE aptc.surr_id = apt.surr_id)
         AND apt.status IN ('EXT', 'EXP');
BEGIN

    OPEN c_arc_act_plus_triggers1;
    LOOP
        FETCH c_arc_act_plus_triggers1 BULK COLLECT INTO v_arc_act_plus_triggers1 LIMIT 10000;  -- limit to 10k to avoid out of memory

        FORALL i IN 1..v_arc_act_plus_triggers1.COUNT
        INSERT /*+ APPEND_VALUES */ INTO arc_act_plus_triggers1 values v_arc_act_plus_triggers1(i);  


        Com0932.get_parameter ('ACT_ARCHIVE_TRIGGER_STOP_YN',l_STOP_PROGRAM_YN);         
        IF l_STOP_PROGRAM_YN = 'Y' THEN
            p_location('insert_into_arc_act_plus - STOP_PROGRAM_YN flag = '||l_STOP_PROGRAM_YN||' so ROLLBACK'); 
            ROLLBACK;
            EXIT;
        END IF; 

        -- **************************************************
        -- Output how many records have been inserted here???
        -- **************************************************

        -- commit after every 10000 records into arc_act_plus_triggers1   
        COMMIT;     
        EXIT WHEN c_arc_act_plus_triggers1%NOTFOUND;


    END LOOP;
    CLOSE c_arc_act_plus_triggers1;

END;

Answer 1

I haven't checked as I have nothing to test against so please forgive any 'missing semi-colon type errors' and I'm afraid I'm not in a position to performance check this.

Your code seems to select which rows to insert to the archive table based on there non-existence in the archive. Therefore simply use an INSERT based on a SELECT limited by a suitable ROWNUM value. Once you commit then the next time round the loop it wont try getting already archived rows as you just committed them.

I think this should be as quick if not quicker than bulkifying the inserts with the advantage that its simpler - Occams Razor and all that.

DECLARE
  l_commit_count NUMBER := 10000;
  l_rows_copied NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Started at '||TO_DATE(SYSDATE, 'DD_MON_YYY HH24:MI:SS');
    LOOP
        INSERT /*+APPEND */
        INTO c_arc_act_plus_triggers1
        SELECT /*+ PARALLEL */ apt.*
            FROM act_plus_triggers1 apt
           WHERE NOT EXISTS
                    (SELECT 1
                       FROM act_plus_triggers_copy1 aptc
                      WHERE aptc.surr_id = apt.surr_id)
             AND apt.status IN ('EXT', 'EXP')
             AND rownum < l_commit_count;

        COMMIT;         
        l_rows := l_rows + SQL%ROWCOUNT;
        EXIT WHEN SQL%ROWCOUNT < 1;
    END LOOP        
    DBMS_OUTPUT.PUT_LINE('Finished at '||TO_DATE(SYSDATE, 'DD_MON_YYY HH24:MI:SS');
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_rows)||' rows copied to the archive table');
END;