select * from v$version -- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod create table test_tcl_tab (id number,name varchar2(100 byte)); / create or replace package test_tcl_pkg is procedure insert_1; procedure insert_2; procedure insert_3; end test_tcl_pkg; / create or replace package body test_tcl_pkg is procedure insert_1 is begin insert into test_tcl_tab(id,name) values(1,'Raghu'); end insert_1; procedure insert_2 is begin insert into test_tcl_tab(id,name) values(2,'Tedla'); end insert_2; procedure insert_3 is begin insert into test_tcl_tab(id,name) values(2,'Ted'); end insert_3; end test_tcl_pkg; / select * from test_tcl_tab; exec test_tcl_pkg.insert_1; exec test_tcl_pkg.insert_2; exec test_tcl_pkg.insert_3; declare begin test_tcl_pkg.insert_1; test_tcl_pkg.insert_2; test_tcl_pkg.insert_3; end; /
Raghu wrote:On exception, ROLLBACK happens at transaction level.
I am confused about how errors are handled by Oracle.
If I call each procedure individually, first two are successful and third fails with primary key violation error, when I do select it returns 2 records.
I have done rollback after confirming it returns 2 records.
But, when I call all 3 procedures in anonymous block, the block fails with primary key violation error for 3rd procedure and select does not return any data.
Why does Oracle work differently when each procedure is called individually and all are in called in a single script ?