Re: Question on Rollback

From: Hakan Eren <nvc_at_iprimus.ca>
Date: Mon, 11 Oct 1999 16:17:58 -0400
Message-ID: <380245F6.53187D4F@iprimus.ca>

Hi,

Is it possible to create p1 without errors in Oracle8 (Without p2). As far as I know You cannot do that
in Oracle7. If it is possible I think that may cause some deadlock problems, for example:

SQLWKS> CREATE OR REPLACE PROCEDURE p1
     2> IS
     3> BEGIN
     4>   p2;
     5> END p1;
     6> /

MGR-00072: Warning: PROCEDURE P1 created with compilation errors.

SQLWKS> CREATE OR REPLACE PROCEDURE p2
     2> IS
     3> BEGIN
     4>   p1;
     5> END p2;
     6> /

MGR-00072: Warning: PROCEDURE P2 created with compilation errors. SQLWKS> ALTER PROCEDURE p1 COMPILE;
ALTER PROCEDURE p1 COMPILE
*
ORA-04045: errors during recompilation/revalidation of SCOTT.P2 ORA-04020: deadlock detected while trying to lock object SCOTT.P1 SQLWKS> ALTER PROCEDURE p2 COMPILE;
ALTER PROCEDURE p2 COMPILE
*
ORA-04045: errors during recompilation/revalidation of SCOTT.P1 ORA-04020: deadlock detected while trying to lock object SCOTT.P2 SQLWKS> ALTER PROCEDURE p1 COMPILE;
ALTER PROCEDURE p1 COMPILE
*
ORA-04045: errors during recompilation/revalidation of SCOTT.P2 ORA-04020: deadlock detected while trying to lock object SCOTT.P1 SQLWKS> SELECT * FROM user_errors;

NAME                           TYPE         SEQUENCE   LINE       POSITION

TEXT

------------------------------ ------------ ---------- ---------- ----------
--------------------------------------------------------------------------------

P1                             PROCEDURE             1          4          3
PLS-00201: identifier 'P2' must be declared
P1                             PROCEDURE             2          4          3
PL/SQL: Statement ignored
P2                             PROCEDURE             1          4          3
PLS-00905: object SCOTT.P1 is invalid
P2                             PROCEDURE             2          4          3

PL/SQL: Statement ignored
4 rows selected.

Which seems logical to me.

Thomas Kyte wrote:

> 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 - 15:17:58 CDT