Hi All,
I am doing a daily archive job . I am using the BULK COLLECT and FORALL to delete the rows from the table. All goes well as long as there are no exceptions .
My Code is as Below
BEGIN
SELECT KEY BULK COLLECT INTO <VARAIBLE> FROM <TABLE> WHERE DATE <=<INPUT VALUE> ;
FORALL INX IN 1...<variable>.count SAVE EXCEPTIONS;
DELETE FROM <TABLE> where KEY=<variable>(INX);
log_del_rec_count:=SQL%ROWCOUNT;
EXCEPTION
WHEN BULK_EXCPTIO_ERR
FOR I IN 1.. , ... LOOP
<log error>
END LOOP;
END;
AS long as there are no failures the value in log_del_rec_count matches the value of records delete. But one the failure occurs the rows returned are 0. Why does this happen. Should I use a FOR loop instead of FOR ALL .
Thanks & Regards
Hower