question about pl/sql exception
exception, oracle, plsql, transactions
Solution
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.
Problem
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?