question about pl/sql exception

Question

hi all. the following text is an excerpt of oracle documentation Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1) :

Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to OUT parameters (unless they are NOCOPY parameters). Also, if a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram.

note the bold text, does that true? i'm curious about that, so i wrote the following example to test it.

-- create a test table
CREATE TABLE e AS SELECT * FROM HR.EMPLOYEES;   

-- create p1 which will update a row in table e
CREATE OR REPLACE PROCEDURE p1
IS
    ex EXCEPTION;
    row e%ROWTYPE;
BEGIN
    select * into row from e where employee_id=100;

    row.employee_id := 100;
    row.first_name := 'yang';

    -- update
    UPDATE e set ROW = row where employee_id = 100;
    -- and raise an error
    RAISE ex;
END;


BEGIN
    -- call the upper procedure
    p1;
END;

-- test whether update success
select * from e where employee_id=100;

-- the upper query gives me
Steven

so my question is: am i right?

Answer 1

Hi Yousui,

See this question on SO: Does Oracle roll back the transaction on an error?

In your case the procedure P1 will either succeed or fail and roll back its changes. Why does it look like the statement from the documentation states the opposite (p1 fails in the middle of the procedure and leaves unfinished work)?

The answer lies in the sentence just before your quote:

Remember, if it cannot find a handler for a raised exception, PL/SQL returns an unhandled exception error to the host environment, which determines the outcome. For example, in the Oracle Precompilers environment, any database changes made by a failed SQL statement or PL/SQL block are rolled back.

What this means it that when a procedure fails, if the raised exception is unhandled, the incomplete work will be rolled back. However, if the exception is catched and not re-raised the incomplete work will be left as is.

We can show this behaviour by putting a WHEN OTHERS block (and not re-raising an exception -- of course it's a really really bad idea see below why) in your example:

SQL> BEGIN
  2     -- call the upper procedure
  3     p1;
  4  EXCEPTION
  5     WHEN OTHERS THEN
  6        dbms_output.put_line('log error...');
  7  END;
  8  /

log error...

PL/SQL procedure successfully completed

SQL> select employee_id, first_name from e where employee_id = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 yang

You really never want to do this: we left unfinished work, the error is logged and by not re-raising it we have a potentially serious bug. Furthermore, silently ignoring exceptions is a recipe for disasters.

Answer 2

"Also, if a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram."

The above quote specifically refers to stored subprograms, but the following chunk of code is an anonymous block, not a stored subprogram

BEGIN
    -- call the upper procedure
    p1;
END;

As such, the quote does not apply. The failure of the top level anonymous block is the one that does the rollback (just as any other SQL statement) Testing with the following code indicates that, by the time the SERVERERROR trigger is fired (ie BEFORE returning to the host), the INSERTs of values 1 and 10 have already been rolled back (because a re-insert of the 1 doesn't fail on duplicate key or deadlock).

drop table test_se_auto_tbl;

create table test_se_auto_tbl (id number(2) primary key, val varchar2(20));

create or replace trigger test_se_auto_trg after servererror on schema 
begin
  for c_rec in (select id, val from test_se_auto_tbl) loop
dbms_output.put_line(c_rec.id||':'||c_rec.val);
  end loop;
  dbms_output.put_line('Trigger');
  insert into test_se_auto_tbl values (1,'test ');
end;
/

begin
  insert into test_se_auto_tbl values (1,'test ');
  insert into test_se_auto_tbl values (10,'test 10');
  insert into test_se_auto_tbl values (100,'test 100');
end;
/

select id, val from test_se_auto_tbl;

Another scenario supporting this hypothesis. In this case the anonymous PL/SQL block is called within an EXECUTE IMMEDIATE nested inside another PL/SQL block. Although the exception is captured by the outer block, the insert has already been rolled back as EXECUTE IMMEDIATE runs an atomic statement.

DECLARE
  v_num NUMBER;
begin
   begin
     execute immediate 
         'declare 
           v_num number(2); 
         begin 
           insert into dummy values (1);
           dbms_output.put_line(101);
           v_num := 100;
         end;';
   exception
      when others then null;
   end;
   select count(*) into v_num from dummy;
   dbms_output.put_line(v_num);
end;
/