How to resume a failed statement in procedure

[ales]

You just need to catch the exception and ignore it:

CREATE OR REPLACE procedure SP_TRUNC 
AS 

-- an exception to catch not-existent table
table_does_not_exist	exception;
PRAGMA EXCEPTION_INIT (table_does_not_exist, -942);

sql_stmt varchar2(1024); 
v_ErrorSeq NUMBER; 

CURSOR cur01 is 
	select table_name from trunctab; 
	fch_sql cur01%ROWTYPE; 
BEGIN 
	OPEN cur01; 
	-- you do not need to use execute immediate to call a procedure
	SP_PROCDTL('START','SP_TRUNCTAB');
	LOOP 
		FETCH cur01 INTO fch_sql; 
		EXIT WHEN cur01%NOTFOUND; 
		sql_stmt := 'truncate table '||fch_sql.table_name; 
		begin
			EXECUTE IMMEDIATE sql_stmt; 
		exception
			-- when table does not exist, just ignore it and continue
			when table_does_not_exist then null;
		end;
	END LOOP; 
	-- you do not need to use execute immediate to call a procedure
	SP_PROCDTL('END','SP_TRUNCTAB');
	CLOSE cur01; 

	EXCEPTION 
	WHEN OTHERS THEN 
		ErrorPkg.HandleAll(FALSE); 
		RAISE; 

END; 
/

[ckhchong]

Great Input Ales! Thanks for your information.
Is there a way for me to resume the process for all the errors? (not only table not exists).

Best regards

[ales]

Sure, just use "when others then null;".
But this is a very bad practice. The procedure won't bother you with errors, but you might miss something important :-).
HTH