Oracle Pl/SQL Exception Flow

Question

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.

Answer 1

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;
/