2 Replies Latest reply: Apr 18, 2013 10:22 PM by sb92075 RSS

    Error handling

    587126
      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
          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
            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