Number of lines deleted - FORALL BULK COLLECT with LIMIT
Hello,
Need your help gays ..
I need to delete lines from a big table that are not related to other tables (parent key constraint), so I used FORALL BULK COLLECT with LIMIT, and then I need to show the number of all rows successfully deleted (variable l_cnt_ok showing the wrong value)
Please find below the example of my code :
CREATE OR REPLACE FUNCTION delete_from_tab (p_date DATE) RETURN PLS_INTEGER IS --PRAGMA AUTONOMOUS_TRANSACTION; TYPE ARRAY_TAB IS TABLE OF big_table.item_name%TYPE; v_tab_array ARRAY_TAB; CURSOR cur IS SELECT item_name FROM big_table WHERE create_date < p_date; errors NUMBER; dml_errors EXCEPTION; l_cnt_error number := 0; l_cnt_ok number := 0; PRAGMA exception_init(dml_errors, -24381); BEGIN OPEN cur; LOOP FETCH cur BULK COLLECT INTO v_tab_array LIMIT 1000; BEGIN FORALL i IN 1..v_tab_array.COUNT SAVE EXCEPTIONS DELETE FROM