question about pl/sql exception

Question

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

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.