Given a simple SP like;
CREATE OR REPLACE PROCEDURE TEST1
AS
BEGIN
EXECUTE IMMEDIATE 'truncate table missingtable';
dbms_output.put_line('here');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
I never get to the output statement, I thought control returned to the same block, which is the only block.. and yes, missingtable reports a -942 if I try to truncate it.
it's a logic problem, in fact the exception happens but you coded to raise an exception only if the return code is different of 942 which is the the error happening.
if you want to continue to the dbms_output in you first block you need an inner exception
CREATE OR REPLACE PROCEDURE TEST1
AS
BEGIN
BEGIN
EXECUTE IMMEDIATE 'truncate table missingtable';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
ELSE
NULL;
END IF;
END;
dbms_output.put_line('here');
END;
/