How to resume a failed statement in procedure
I have a procedure as below:
CREATE OR REPLACE procedure SP_TRUNC
AS
sql_stmt varchar2(1024);
v_ErrorSeq NUMBER;
CURSOR cur01 is
select table_name from trunctab;
fch_sql cur01%ROWTYPE;
BEGIN
OPEN cur01;
EXECUTE IMMEDIATE 'BEGIN SP_PROCDTL(''START'',''SP_TRUNCTAB''); END;';
LOOP
FETCH cur01
INTO fch_sql;
EXIT WHEN cur01%NOTFOUND;
sql_stmt := 'truncate table '||fch_sql.table_name;
EXECUTE IMMEDIATE sql_stmt;
END LOOP;
EXECUTE IMMEDIATE 'BEGIN SP_PROCDTL(''END'',''SP_TRUNCTAB''); END;';
CLOSE cur01;
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(FALSE);
RAISE;
END;
/
This procedure will truncate 10 tables, when the truncate process reach to table 7th and the error happend due to table not exists, table 8th, 9th 10th will not be truncated, how do I resume truncating the rest of the tables if one of the table failed to truncated.
Thanks for any advise give,
best regards,