This discussion is archived
2 Replies Latest reply: Apr 18, 2013 8:22 PM by sb92075 RSS

Error handling

587126 Newbie
Currently Being Moderated
Hi all,

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 ?
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;
/ 
Thanks
Raghu
  • 1. Re: Error handling
    jeneesh Guru
    Currently Being Moderated
    Raghu wrote:
    Hi all,

    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 ?
    On exception, ROLLBACK happens at transaction level.

    When you run 3 procedures individually, they are 3 seperate transactions. So the exception in the third procedure will effect only its transaction, ie., third transaction.

    When you call 3 procedures in an anonymous block, it is a single transaction - Excetion will rollback all the changes done by the three procedures (if there is no intermediate COMMIT).

    If you dont want to happen this, put intermediate commmits - Normally that is not a good practice..

    Edited by: jeneesh on Apr 19, 2013 9:15 AM
    You will be interested to read about cocurrency and consistency - http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm
  • 2. Re: Error handling
    sb92075 Guru
    Currently Being Moderated
    what is a session?
    what is a transaction?
    who can see uncommitted DML?
    does ERROR result in ROLLBACK?


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ

    post was nice start but does not actually SHOW any results

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points