DML : Exception Handling

When an exception occurs in a PL/SQL block, Oracle doesn't rollback any of the changes made by the DML statements in that block. We need to decide on the rollback explicitly.


Example:

-- Before DML

select * from scott.emp where deptno=10;

-- DML with in PL/SQL Block

Begin

update scott.emp set sal=sal*1.1 where deptno=10;

dbms_output.put_line('Number of employees got the pay hike:'||SQL%ROWCOUNT);

-- raise an exception

raise no_data_found;

Exception

when no_data_found then
dbms_output.put_line('Check the values for the update statement: Values got updated');

End;
/


-- After DML in PL/SQL Block 

select * from scott.emp where deptno=10;


Points to remember:


1. when an exception is raised,we must perform a rollback or commit (usually a rollback)

2. If an exception propagates past the outermost block (goes unhandled),
    then in most host execution environments for PL/SQL like SQL*Plus,
    an unqualified rollback is automatically executed.

3. If an exception is handled and we don't rollback the changes in the exception block, all the dml's get    committed. (Use Rollback in the Exception Block to revert the affects of DML statements in the block)