Re: Question on Rollback

A copy of this was sent to Hakan Eren <nvc_at_iprimus.ca> (if that email address didn't require changing) On Mon, 11 Oct 1999 16:17:58 -0400, you wrote:

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

My example is not recursive -- p1 calls p2. p2 does not call p1.  

In the example, p1 will compile with errors. P2 will successfully compile. Simply running p1 will cause it to auto-compile itself and execute sucessfully.  

I should have created p2 before p1 to avoid confusing people but the example works 'as is' in all releases of Oracle.  

Mine has a successfully compiled message after p1 since I ran the script >1 time (and p2 did exist on the 2'cnd and n'th runs)  

>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

--
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 Tue Oct 12 1999 - 07:47:23 CDT