Exception Handling: Nested Blocks

Question: Can you use the same name for User-Defined Exceptions in nested blocks? A: Yes. But, oracle treat them as DIFFERENT exceptions. If exception e1 is raised and NOT CATCHED in the inner block, it WILL NOT be caught in the outer block EVEN if there is an exception handler with that name.

[ Oracle PL/SQL ]

Case 1: Exception caught in the internal block

set serveroutput on
DECLARE                -- outer block
 e1 exception;
 a integer;
BEGIN
  a :=2; 
  declare              -- inner block
    e1 exception;
    b integer;
  begin
    b := 2;
    raise e1;           -- exception e1 raised.
  exception
    when others then 
       dbms_output.put_line('error1 caught: inner block');
       dbms_output.put_line(sqlerrm);
  end;
  a :=3;
  raise e1;              -- exception e1 raised.
EXCEPTION
 when e1 then 
   dbms_output.put_line('error1 caught: outer block');
END;
/
anonymous block completed
error1 caught: inner block
User-Defined Exception
error1 caught: outer block

Case 2: Exception unhadled in the internal block is NOT caught in the external one

set serveroutput on
DECLARE                         -- outer block
 e1 exception;
 a integer;
BEGIN
  a :=2; 
  declare                -- inner block
    e1 exception;
    b integer;
  begin
    b := 2;
    raise e1;                   -- exception e1 raised
  exception
    when no_data_found then 
       dbms_output.put_line('error1 caught: inner block');
       dbms_output.put_line(sqlerrm);
  end;
  a :=3;
  raise e1;
EXCEPTION
 when e1 then 
   dbms_output.put_line('error1 caught: outer block');
END;
/

Error report:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 11
06510. 00000 -  "PL/SQL: unhandled user-defined exception"
*Cause:    A user-defined exception was raised by PL/SQL code, but
           not handled.
*Action:   Fix the problem causing the exception or write an exception
           handler for this condition. Or you may need to contact your
           application administrator or DBA.

Native Dynamic SQL x DBMS_SQL

[ Oracle PL/SQL ]

With Oracle 11g:

• Native dynamic SQL and DBMS_SQL package now support statments larger than 32Kb.
• EXECUTE IMMEDIATE, OPEN-FOR and DBMS_SQL.PARSE accept SQL statements in the form of CLOBs.

• DBMS_SQL.TO_REFCURSOR converts a DBMS_SQL cursor ID into a REF CURSOR.
• DBMS_SQL.to_cursor_number converts a REF CURSOR into a DBMS_SQL cursor ID.

DBMS_SQL.TO_REFCURSOR: From Cursor ID => to REF CURSOR.

set serveroutput on 
declare 
 lcursor number;                       -- for DBMS_SQL Cursor ID
 lreturn number;                       -- for DBMS_SQL Cursor ID
 
 lref_cursor sys_refcursor;            -- for REF CURSOR
 type        t_emptab is table of employees%rowtype;
 lemp_tab    t_emptab;

begin
  lcursor := dbms_sql.open_cursor;
  dbms_sql.parse(lcursor, 
                'Select * from employees',
                 DBMS_SQL.NATIVE);
  lreturn := dbms_sql.execute(lcursor);
  
                                        -- convert from dbms_sql Cursor ID to a REF CURSOR
  lref_cursor := dbms_sql.to_refcursor(lcursor);
  fetch lref_cursor bulk collect into lemp_tab;
  dbms_output.put_line('Employee count: '||lemp_tab.count);
  close lref_cursor;
end;
/
anonymous block completed
Elapsed: 00:00:00.050
Employee count: 107

DBMS_SQL.to_cursor_number: from REF CURSOR => to DBMS_SQL cursor ID.

declare 
  lref_cursor sys_refcursor;
  lcursor number;
  lcount  number := 0;
begin 
  OPEN lref_cursor FOR 'select * from employees';
                                          -- convert from REF CURSOR to a dbms_sql Cursor ID
  lcursor := dbms_sql.to_cursor_number(lref_cursor);

  while dbms_sql.fetch_rows(lcursor) > 0 LOOP
    lcount := lcount + 1;
  end loop;
  dbms_output.put_line('Employee count: '|| lcount);
  dbms_sql.close_cursor(lcursor);
end;
/
anonymous block completed
Elapsed: 00:00:00.024
Employee count: 107

Native Dynamic SQL: OPEN FOR, FETCH and CLOSE

[ Oracle PL/SQL ]

Native Dynamic SQL: OPEN FOR, FETCH and CLOSE

Using dynamic SQL with OPEN FOR, FETCH and CLOSE:

set serveroutput on
declare 
 -- define a type cursor. 
 -- declare a variable of type cursor. This variable will 
 -- execute the dynamic SQL stmt later (OPEN, LOOP-FETCH, CLOSE).
 type empcurtype is ref cursor;
 v_emp_c    empcurtype;
 emp_rec    employees%rowtype;
 v_stmt_str varchar2(200);
 v_e_job    employees.job_id%type;
begin
  -- dynamic SQL stmt. Note the placeholder (:j) for 
  -- a bind variable.
  v_stmt_str := 'SELECT * FROM employees '|| 
                'WHERE job_id = :j';
  
  -- Open the cursor. List the bind variables
  -- with the USING clause. Placeholders, in this
  -- case (single SQL stmt), are associated 
  -- BY POSITION, and NOT by name.
  OPEN v_emp_c FOR v_stmt_str USING 'IT_PROG';
  LOOP
    FETCH v_emp_c INTO emp_rec;
    exit when v_emp_c%notfound;
    dbms_output.put_line('Empid: '|| emp_rec.employee_id || 
                   '--'||emp_rec.first_name || ' ' || emp_rec.last_name );
  END LOOP;
  CLOSE v_emp_c;
end;

nonymous block completed
Empid: 103--Alexander Hunold
Empid: 104--Bruce Ernst
Empid: 105--David Austin
Empid: 106--Valli Pataballa
Empid: 107--Diana Lorentz

(cont..)