Oracle Bulk Collect with Limit and For All Not Processing All Records Correctly

Question

I need to process close to 60k records of an Oracle table through a stored procedure. The processing is that for each such row, I need to delete and update a row in a second table and insert a row in a third table.

Using cursor looping, the procedure takes around 6-8 hours to complete. If I switch to Bulk Collect with Limit, the execution time is reduced but processing is not correct. Following is the bulk collect version of the procedure

create or replace procedure myproc()
is
    cursor c1 is select col1,col2,col3 from tab1 where col4=3;
    type t1 is table of c1%rowtype;
    v_t1 t1;
begin
    open c1;
    loop
        fetch c1 bulk collect into v_t1 limit 1000;
        exit when v_t1.count=0;
        forall i in 1..v_t1.count
            delete from tab2 where tab2.col1=v_t1(i).col1;
        commit;
        forall i in 1..v_t1.count
            update tab2 set tab2.col1=v_t1(i).col1 where tab2.col2=v_t1(i).col2;
        commit;
        forall i in 1..v_t1.count
            insert into tab3 values(v_t1(i).col1,v_t1(i).col2,v_t1(i).col3);
        commit;
    end loop;
    close c2;
end;

For around 20k of these records, the first delete operation is processed correctly but subsequent update and insert is not processed. For the remaining 40k records all three operations are processed correctly.

Am I missing something? Also what is the maximum LIMIT value I can use with Bulk Collect?

So you know you iterate over the main loop 60 times, and you know that you do 60k deletes but 40k updates and inserts? How are you checking/counting? — Alex Poole, May 20 '16 at 19:18
The table tab3 is a log table that is cleared prior to execution of the procedure; so if there are 40k records in that table after execution of the procedure, it means 40k inserts were successful. The table (tab1) has close to 2*60k records prior to execution of the procedure and after execution, the record count is 60k which means 60k deletes were successful. — Soumyorup Dey, May 20 '16 at 20:46
OK, so is tab2.col1 unique? I'm not trying to be difficult, just trying to think what you might have overlooked. — Alex Poole, May 20 '16 at 20:49

tbone · Answer 1 · 2016-05-20T20:28:43.283

You should try using SAVE EXCEPTIONS clause of FORALL, something like (untested):

create or replace procedure myproc
as
    cursor c1 is select col1,col2,col3 from tab1 where col4=3;
    type t1 is table of c1%rowtype;
    v_t1 t1;
    dml_errors EXCEPTION;
    PRAGMA exception_init(dml_errors, -24381);
    l_errors number;
    l_errno    number;
    l_msg    varchar2(4000);
    l_idx    number;
begin
    open c1;
    loop
        fetch c1 bulk collect into v_t1 limit 1000;
        -- process v_t1 data
        BEGIN
            forall i in 1..v_t1.count SAVE EXCEPTIONS
                delete from tab2 where tab2.col1=v_t1(i).col1;
            commit;
        EXCEPTION
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    l_idx   := sql%bulk_exceptions(i).error_index;
                    -- log these to a table maybe, or just output 
                end loop;
        END;

        exit when c1%notfound;
    end loop;
    close c2;
end;

So you mean to say, those 20k records are not processed bevause of some exception that I need to catch and log/print to debug the issue? But if there indeed is an exception, shouldn't Toad (where I am executing the stored proc) give me the message and abruptly terminate the execution? — Soumyorup Dey, May 20 '16 at 20:49
No, I'm just saying that its helpful when trying to debug whats happening. We don't have specific data so we're guessing a bit. Also, its possible that you are not updating/inserting as many rows as you think, not necessarily that the operation isn't happening (it can run the statement and insert or update 0 rows). To know that, you'd add SQL%ROWCOUNT immediately after each forall statement. — tbone, May 21 '16 at 00:16

Oracle Bulk Collect with Limit and For All Not Processing All Records Correctly

1 Answers1