Oracle PL/SQL exception handling: throwing and catching exceptions

Thrower

create or replace package tq84_thrower as

    exc_1       exception;
    exc_2       exception;
    exc_3       exception;

    pragma exception_init(exc_1, -20000); -- Note exc_1 and exc_2 are both associated
    pragma exception_init(exc_2, -20000); -- with the same error code.
    pragma exception_init(exc_3, -20100); --

    procedure raise_exc_1;
    procedure raise_exc_2;
    procedure raise_exc_3;

    procedure raise_exc_i;

    procedure raise_app_error(error_code number);

end tq84_thrower;
/

create or replace package body tq84_thrower as

    --    Not associated with an error code via
    --    exception_init:
    exc_i exception;


    procedure raise_exc_1 is -- {
    begin
        raise exc_1;
    end raise_exc_1; -- }

    procedure raise_exc_2 is -- {
    begin
        raise exc_2;
    end raise_exc_2; -- }

    procedure raise_exc_3 is -- {
    begin
        raise exc_3;
    end raise_exc_3; -- }

    procedure raise_exc_i is -- {
    begin
        raise exc_i;
    end raise_exc_i; -- }

    procedure raise_app_error(error_code number) is -- {
    begin
        raise_application_error(-error_code, 'Request to raise error ' || error_code);
    end raise_app_error; -- }


end tq84_thrower;
/

Catcher

create or replace package tq84_catcher as

    procedure go;

end tq84_catcher;
/

create or replace package body tq84_catcher as

    procedure try(what varchar2, num number) is begin -- {


       if    what  = 'exc' then

             if num = 1 then tq84_thrower.raise_exc_1; elsif
                num = 2 then tq84_thrower.raise_exc_2; elsif
                num = 3 then tq84_thrower.raise_exc_3; elsif
                num = 0 then tq84_thrower.raise_exc_i; else
                             raise_application_error(-20801, 'Unknown num: ' || num);
             end if;

       elsif what  = 'raise' then

             tq84_thrower.raise_app_error(num);

       else

             raise_application_error(-20800, 'Unknown what: ' || what);

       end if;


    exception 
    
        when tq84_thrower.exc_1 then
             dbms_output.put_line('  Error exc_1 (' || what || '/' || num || ') ' || sqlerrm || '[' || sqlcode || ']');

--  Commented out to prevent
--   PLS-00484: redundant exceptions 'EXC_1' and 'EXC_2' must appear in same exception handler
-- 
--      when tq84_thrower.exc_2 then
--           dbms_output.put_line('  Error exc_2 (' || what || '/' || num || ') ' || sqlerrm || '[' || sqlcode || ']');

        when tq84_thrower.exc_3 then
             dbms_output.put_line('  Error exc_3 (' || what || '/' || num || ') ' || sqlerrm || '[' || sqlcode || ']');

        when others then
             dbms_output.put_line('  Error other (' || what || '/' || num || ') ' || sqlerrm || '[' || sqlcode || ']');

    end try; -- }

    procedure go is begin -- {


        try('exc'   ,     1);  --  Error exc_1 (exc/1) ORA-20000: [-20000]
        try('exc'   ,     2);  --  Error exc_1 (exc/2) ORA-20000: [-20000]
        try('exc'   ,     3);  --  Error exc_3 (exc/3) ORA-20100: [-20100]
                             
        try('raise' , 20000);  --  Error exc_1 (raise/20000) ORA-20000: Request to raise error 20000[-20000]
        try('raise' , 20100);  --  Error exc_3 (raise/20100) ORA-20100: Request to raise error 20100[-20100]
        try('raise' , 20200);  --  Error other (raise/20200) ORA-20200: Request to raise error 20200[-20200]


    end go; -- }


end tq84_catcher;
/

See also