Массовый сбор данных Oracle с лимитом и для всех не обрабатывает все записи правильно

Question

Мне нужно обработать около 60 тыс. Записей таблицы Oracle с помощью хранимой процедуры. Обработка заключается в том, что для каждой такой строки мне нужно удалить и обновить строку во второй таблице и вставить строку в третью таблицу.

Использование циклического перемещения курсора занимает около 6-8 часов. Если я переключаюсь на Bulk Collect with Limit, время выполнения сокращается, но обработка идет неправильно. Ниже приведен вариант процедуры массового сбора.

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;

Приблизительно для 20 тысяч таких записей первая операция удаления выполняется правильно, но последующее обновление и вставка не обрабатываются. Для оставшихся 40 тыс. Записей все три операции выполнены правильно.

Я что-то пропустил? Также, какое максимальное значение LIMIT я могу использовать с Bulk Collect?

Answer 1

Вы должны попробовать использовать предложение FORALL SAVE EXCEPTIONS, что-то вроде (не проверено):

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;