When we use pragma exception_init (ABCDXYZ, -2000n )
which table stores this mapping in the database?

Thanks

Hi, Tom,
As always, thank you for everything you do for us. I hate to bother you, but I did not find an answer anywhere yet. In your book you listed some caveats, but in my case it is probably something different.

I created a simple test package with 3 procedures. One of them PRAGMA AUTONOMOUS_TRANSACTION. When I run the package  in one session everything is fine. After it completed, I go to another session and try to execute it and I am getting ORA-00054:

Session 1 (everything is fine):
13:02:53 SQL> show user
USER is "GRO_TEST2"
13:07:51 SQL> exec vera_test.main;
1
1

PL/SQL procedure successfully completed.

Session 2:

13:02:23 SQL> show user
USER is "GRO_TEST2"
13:08:31 SQL> exec vera_test.main;
BEGIN vera_test.main; END;

*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "IPVFB_TEST2.VERA_TEST", line 10
ORA-06512: at "IPVFB_TEST2.VERA_TEST", line 61
ORA-06512: at line 1

Line 10 is: EXECUTE IMMEDIATE 'TRUNCATE TABLE v'; and this line belongs to "normal" procedure, not Autonomous. Line 61 is a call to this "normal" procedure.

I am trying to create mechanism of checking on status of a huge process. Right now it commits for each row and user can always see how many cases and customers were processed, but finally I was allowed to get rid of commits and truncates inside of loops so I replaced database tables with PL/SQL tables, "query" them and commit only once - at the end of transaction. Tom! It used to run for 12-30 something hours, well, my first draft runs for 5 hours in absolutely identical environment! However, since user still would like to have a mechanism of checking on the process, I am trying to implement Autonomous Transaction and from the very first try I encountered the above problem. 
I do not use datanase links.
My questions are:
--Why Oracle gives me ORA-00054?
--Is Autonomous Transaction the only way to keep user   updated on transaction status?
--Is it the best way?

I have
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.2.1       Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

Thank you again

 

One more thing, Tom.
The table I got an error about is locked by my first session even though it does not do anything with it and within the first session I can truncate it whatever I want, but from another session.

Test example:

Session 1:

SQL> SHOW USER
USER is "GRO_TEST2"
SQL> DESC VERA_TEST_TBL;
 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                                       NUMBER

SQL> DESC VERA_TEST_AUTH;
 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                                       NUMBER

SQL> CREATE OR REPLACE PACKAGE VERA_TEST
  2  AS
  3  PROCEDURE normal_proc;
  4  PROCEDURE pragma_proc;
  5  PROCEDURE nornal_proc1;
  6  PROCEDURE MAIN;
  7  v_global_id NUMBER;
  8  END;
  9  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY vera_test
  2  AS
  3  PROCEDURE normal_proc
  4  AS
  5  BEGIN
  6  EXECUTE IMMEDIATE
  7  'TRUNCATE TABLE vera_test_tbl';
  8  insert into vera_test_tbl (id) values(1);
  9  SELECT id INTO v_global_id FROM vera_test_tbl;
 10  END;
 11  PROCEDURE pragma_proc
 12  AS
 13  PRAGMA AUTONOMOUS_TRANSACTION;
 14  BEGIN
 15  DELETE FROM vera_test_auth;
 16  INSERT INTO vera_test_auth VALUES(v_global_id);
 17  DELETE FROM vera_test_tbl;
 18  COMMIT;
 19  END;
 20  PROCEDURE nornal_proc1
 21  AS
 22  v_id NUMBER;
 23  v_cnt NUMBER;
 24  BEGIN
 25  
 26  SELECT id INTO v_id FROM vera_test_auth;
 27  DBMS_OUTPUT.PUT_LINE (v_id);
 28  END;
 29  PROCEDURE MAIN
 30  AS
 31  BEGIN
 32  normal_proc;
 33  pragma_proc;
 34  nornal_proc1;
 35  END;
 36  END;
 37  /

Package body created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC vera_test.MAIN;
1

PL/SQL procedure successfully completed.
-----------------------------------

SESSION 2:

SQL> SHOW USER
USER is "GRO_TEST2"
SQL> EXEC VERA_TEST.MAIN;
BEGIN VERA_TEST.MAIN; END;

*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "IPVFB_TEST2.VERA_TEST", line 6
ORA-06512: at "IPVFB_TEST2.VERA_TEST", line 32
ORA-06512: at line 1
------------------------------
Tom, you said:
but in any case -- you are using the wrongest approach for what you are trying 
to do.
I think you mean I should use TYPEs, not PL/SQL tables? I will, but right now I am trying to move all the logic out of database tables truncating and commiting and demonstrate my team that their approach of commiting in order to not generate rollback was wrong. 
Thank you for pointing out at the Appendix! 

Tom,
I got you. You are an ace. Everybody knows it, but its worth repeating!

Hi Tom,

I have a situation in which i would like to raise an error using Raise_application_error but before the i want to insert a row into a error table and commit. i tried with a plsql block inside procedure but i am getting the following error.

ORA-06550: line 5, column 13:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here 

following is the code:

declare
  v number;
begin
  --doing some processing here and fetching some values in v  
   IF v = 99 THEN 
    declare
      pragma Autonomous_Transaction;
    begin
         insert into t_error values('error found value :'||v);
      commit;
    end;

    raise_application_error(-20006,'done');
  ELSE 
    -- some processing goes here

  END IF;


end;
SQL> /
      pragma Autonomous_Transaction;
             *
ERROR at line 7:
ORA-06550: line 7, column 14:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here

But if i create a procedure p1 it works fine. for eg. 
SQL>
Wrote file afiedt.buf

  1   create or replace procedure p1 as
  2     pragma Autonomous_Transaction;
  3   v number;
  4  begin
  5        insert into t_errors values('error found value :'||v);
  6     commit;
  7*  end;
SQL> /

Procedure created.


SQL> ed
Wrote file afiedt.buf

  1   declare
  2     v number default 99;
  3   begin
  4     --doing some processing here and fetching some values in v
  5      IF v = 99 THEN
  6       p1;
  7       raise_application_error(-20006,'done');
  8     ELSE
  9       -- some processing goes here
 10      null;
 11     END IF;
 12*  end;
SQL> /
 declare
*
ERROR at line 1:
ORA-20006: done
ORA-06512: at line 7


SQL> select * From t_errors;

V
------------------------------------------------
error found value :

SQL>

Please explain this behavior. I have a restriction that i cannot create a procedure p1 in the real env. There is any other wayout to this problem.

We are facing this problem when migrating from Informix to Oracle. The Informix code is written in the following way

Procedure "informix".abc
.....

IF some_variable = some_value THEN
  Update some_table..
  RAISE EXCEPTION -746,0,"Cann't ....."
ELSE
  some_processing
END IF

END;

Thanks and Regards

Vikas Sharma
 

Hi Tom,

Thanks for the reply.

Following is code of informix. Looking into it i can understand how transaction is going on. In my understanding the IF (chk_date IS NULL) is not null then it is executing the update statement below. I am not sure wehther this statment will be commited in informix or not(should commit otherwise it should not be there), But it will not commit in Oracle even when the control comes back to calling env.

CREATE PROCEDURE "informix".sabc_rt(new_date DATE,new_rate LIKE yc.rate)
DEFINE delta_rt_chg LIKE yc.libor_rt_chg;
DEFINE prev_rate LIKE yc.rate;
DEFINE next_rate LIKE yc.rate;
DEFINE chk_date LIKE yc.date_dt;
DEFINE del_type LIKE yc.rec_type;
SET DEBUG FILE TO "/tmp/debug1.txt";
TRACE ON;
LET del_type = "D";
SELECT MAX(yc.date_dt) INTO chk_date
FROM yc yc
WHERE yc.date_dt > new_date
AND yc.rec_type IS NULL;

IF (chk_date IS NULL) THEN

-- Some Processing Goes here..........

ELSE
-- LET chk_date = new_date;
UPDATE yield SET yc.rec_type = del_type
WHERE yc.date_dt = new_date;
TRACE "Trying TO INSERT the date that less than a last one : " || new_date;
RAISE EXCEPTION -746,0,"Trying TO INSERT the date that less than a last one !";
END IF;
TRACE OFF;
END PROCEDURE;

Please suggest.

Thanks and regards,

Vikas Sharma