How to resume a failed statement in procedure

[ckhchong]

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,

[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

[ckhchong]

Actually I have a exception at the bottom of my procedure:


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 will raised an exception, and the error messages will be capture in an exception tables, but it will not resume the process once the exception been raise. If I include the exception code 'table_does_not_exist ' into the same block of the execute immediate, the error will not capture in my exception tables (because it skip the bottom block of my exception), how do I resume the process and keep my exception handling package in use state in my procedure?

thanks

best regards,

[ales]

Nothing difficult, I think. Just call the exception handler instead of "null". If you do not use the RAISE statement the procedure continues executing on the next line.

...
begin
EXECUTE IMMEDIATE sql_stmt;
exception
-- when table does not exist, just ignore it and continue
when table_does_not_exist then
ErrorPkg.HandleAll(FALSE);
end;
...

I believe you should read something about Exception handling (http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#i7014) in PL/SQL.
HTH,