PL/SQL (MOSC)

MOSC Banner

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center