How Oracle Database rollback to the beginning of the PL/SQL block, but not the earlier DML instructions (I think all this come under one single transaction). Because when I try to ROLLBACK creating an exception handler all the instructions until last COMMIT are rolled back.
create table mytable (num int not null primary key);
insert into mytable values(1); // My ROLLBACK, rollbacks to here.
begin // Oracle exception handler rollbacks to here.
insert into mytable values(3);
begin
insert into mytable values(2);
insert into mytable values(1);
end;
/* Incase I try to ROLLBACK all the updates including the first insert is gone.*/
--exception when dup_val_on_index then
--rollback;
end;
Final table data:
1) Incase of oracle handling exception
mytable
_______
1
2) Incase of oracle handling exception
mytable
_______
So how is ROLLBACK in Oracle exception handler different from my ROLLBACK.
This is how Oracle works. Your first INSERT was done correctly (i.e. inserted "1" into a table).
Then you ran an anonymous PL/SQL block which inserts "3", then "2", and fails when trying to insert "1" due to primary key violation.
If unhandled exception occurs during execution of that PL/SQL block (which is what you say that "Oracle exception handler rollbacks to here"), Oracle rolls back to the beginning of the PL/SQL block.
When you utilize EXCEPTION handler and issue ROLLBACK, then it is YOU who decide what to do if something happens, and that is to revert all changes to previous COMMIT which was an implicitly done COMMIT after executing the CREATE TABLE statement, so following INSERT "1" is also rolled back.