Re: Question on Rollback

From: Hakan Eren <nvc_at_iprimus.ca>
Date: Tue, 12 Oct 1999 12:09:50 -0400
Message-ID: <38035D4E.D5D5333C@iprimus.ca>

Hi Jonathan,

I know you can do that with packages. In fact you don't need packages. The following trick will do it:

SQLWKS> create procedure p2 IS
     2> BEGIN
     3>   NULL;
     4> END p2;
     5>

Statement processed.

SQLWKS> create procedure p1 IS
     2> BEGIN
     3>   p2;
     4> END p1;
     5>

Statement processed.

SQLWKS> create or replace procedure p2 IS
     2> BEGIN
     3>   p1;
     4> END p2;
     5>

Statement processed.
SQLWKS> select * from user_errors

     2>
NAME                           TYPE         SEQUENCE   LINE

POSITION TEXT

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

---

---

0 rows selected.

My point (that you missed) How Thomas was able to create p1 without errors? Probably he had a procedure( p2) at this time. I think it was not an important procedure as you may understand from the name.
If you look at the example that he has given in his post you will see he was able to create p1 without errors.

Hakan

Jonathan Lewis wrote:

> create or replace package pp as procedure p1; procedure
> p2;end;/ create or replace package body pp as procedure p1 is
> begin p2; end; procedure p2 is begin p1;
> end; end;/ Don't invoke either procedure as this stands,you may have
> to abort the database andreboot the system (if you are on an NT
> box).
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Hakan Eren wrote in message
> <380245F6.53187D4F_at_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;
>
Received on Tue Oct 12 1999 - 11:09:50 CDT