-- bizarre-ex.sql
-- last modified: 1-30-13

--------
-- signature: procedure: exc1: void -> void
-- purpose: expects no input, returns nothing,
--    put prints to the screen demonstrating how an exception raised by
--    an inner block can be handled by the outer block containing it

create or replace procedure exc1 as
    temp NUMBER := 0;
begin
    -- putting a block INSIDE a block

    begin
        -- statement deliberately raises a no_data_found exc

        select 1
        into temp
        from dual
        where 1 = 2;

    exception
        when no_data_found then
            dbms_output.put_line('In inner block exception');
            raise;   -- yes, I am throwing an exception in here!
    end; -- of inner block
    
    dbms_output.put_line('After inner block');

exception
    when no_data_found then
        dbms_output.put_line('In outer block exception');
end; -- of procedure exc1
/
show errors   

set serveroutput on

prompt ****************************
prompt TESTING exc1 
prompt ****************************

prompt notice how the outer block's line "after inner block" is not reached

exec exc1

prompt comment out the raise; line in the inner block to see what happens
prompt    when the inner block doesn't raise an exception, but just handles
prompt    it...