Re: Question on Rollback

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 11 Oct 1999 09:19:54 -0400
Message-ID: <SeIBOBTZGeXKA=SBxIc0YDB2NSWB@4ax.com>

A copy of this was sent to Norazman Abu Sahir <norazman_at_ti.com> (if that email address didn't require changing) On Mon, 11 Oct 1999 14:45:33 +0800, you wrote:

>Hi,
>
>I have 2 procedures, one calling another. What I tried to do is a
>cenario is like this.
>First procedure called, insert some record into a table.Transaction is
>not yet commit. Then there is a savepoint.
>Second procedure was called from first procedure to perform some other
>logic. Depending on second procedure, if the logic passed, second
>procedure will do commit on the first procedure and if not it will
>rollback to the savepoint stated on the first procedure.
>
>I don't want to consider doing this in one procedure (using whatever
>programming control ie if else etc), is this scenarion "can be done?"

yes -- this is a 'can do' thing.

>Can this be implement ? If yes, what are the practice you normally use?.
>Sorry, I'm new in Oracle, need to understand it.
>
>Azman

Here is an example:

tkyte_at_8.0> create table t ( x int );
Table created.

tkyte_at_8.0> create or replace procedure p1( p_x in number )   2 as
  3 begin

  4          insert into t values ( p_x );
  5          savepoint p1_savepoint;
  6          insert into t values ( p_x );
  7          p2( p_x );

  8 end;
  9 /
Procedure created.

tkyte_at_8.0> create or replace procedure p2( p_x in number )   2 is
  3 begin

  4          if ( p_x < 0 ) then
  5                  rollback to p1_savepoint;
  6          else
  7                  commit;
  8          end if;

  9 end;
 10 /
Procedure created.

tkyte_at_8.0> exec p1( 1 );

PL/SQL procedure successfully completed.

tkyte_at_8.0> 
tkyte_at_8.0> REM we expect 2 #1's in the table since 1 >= 0 and
tkyte_at_8.0> REM p2 will have committed
tkyte_at_8.0> select * from t;

         X
----------
         1
         1

tkyte_at_8.0> rollback;

Rollback complete.

tkyte_at_8.0> REM we STILL expect 2 #1's in the table since 1 >= 0
tkyte_at_8.0> REM and p2 will have committed
tkyte_at_8.0> select * from t;

         X
----------
         1
         1

tkyte_at_8.0>
tkyte_at_8.0> exec p1( -1 );

PL/SQL procedure successfully completed.

tkyte_at_8.0> REM we expect 2 #1's and only 1 -1 in the table since
tkyte_at_8.0> REM p2 rolled back to the savepoint and did not commit
tkyte_at_8.0> select * from t;

         X
----------
         1
         1
        -1

tkyte_at_8.0> rollback;

Rollback complete.

tkyte_at_8.0> REM now we expect only the 2 #1's to remain
tkyte_at_8.0> REM since p2 never did do the commit
tkyte_at_8.0> select * from t;

         X
----------
         1
         1

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Oct 11 1999 - 08:19:54 CDT