Solved

Autonomous transaction within distributed transactions

Posted on 2004-08-18
11
1,503 Views
Last Modified: 2011-05-23
I have the following program structure
procedure main( )
---
----
----
chid_procedure1( );
child_procedure2( );
commit;
Exception
      when others then
          log_error(errmsg);
          rollback;
End;
------------
Procedure child_procedure 1
----
----
Exception
   When others then
            RAise;
end;
-----------
procedure log_error (errmsg varchar2)
is
pragma autonomous_transaction;
  begin
       insert into error_log(......);
       commit;
  end;


When I try to execute the main procedure I am gettin the following errors
ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required
ORA-00164: autonomous transaction disallowed within distributed transaction.

Could somebody tell me why I am getting this error?

Thanks,
Nila
0
Comment
Question by:nila
11 Comments
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
the error message  is exactly what it said:

you have performed distributed transactions while you enable autonomous transactions, which is illegal.

check your procedure log_error, see if you have distriobuted transactions, meaning you have done DML over db_links
0
 

Expert Comment

by:s__g
Comment Utility
1. What is the Oracle version you are using ?
2. Is any of the code using a database link? Possibly in the INSERT trigger of one of the tables?
0
 

Author Comment

by:nila
Comment Utility
yes. I had  was using DB link inside the log_error procedure. Now I took that off. even then I am getting this error.

I am using DB links in all the child procedures. These links are basically need because these procedures are isnerting data into remote tables. But I am sure that in the log_error procedure there are no links.

So,In the code sample I gave above, child_procedure1 and child_procedure2 are basically using table@DB structurs in the insert statements.

Is this the cause of the error?

Thanks,
Nila

0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
Yes, that is.

the reason is that you are wrapping the log_error proc inside MAIN procedure.

Oracle consider a procedure as one transaction
0
 

Author Comment

by:nila
Comment Utility
so then here is my situation.

The main procedure calls each child procedure.
Each child procedure might inturn call another child procedure but thats not mandatory.
level1- Main
              level2 - Child procedure1
                                                level 3 - Child procedure2
              level2 child procedure3
              level2 child procedure 4

Each procedure is used to isnsert data into tables on remote DB.  The hierarchy is based on the parent chidl relationship of the tables. For example if child procedure1 is inserting data into item table then child procedure2 is insertinf data into item_charge table which has item.id as the FK.

Now if there occurs an eception in child procedure2 then I should be able to propogate it to the main procedure and roll back the entire insert stmts in the hierarchy of the procedure calls. and also I should be able to write to a table some error messages etc.

since when there is an exception we are rolling back the whole hierachy of stmts, I wanted to make a procedure to log errors and make it autonomous. so that the insert into log table is committed but the hierachy of insert stmts are rolled back.

Now how do I do this. Your help will be much appreciated.

Thanks,
Nila

ps: if you want I could post the eaxct coe that I am using.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
you have a couple of workarounds, but before that,

Here is Our Oracle TOM KYTE says in his book:

If you are using oracle versions prior to 8.1.7, don't use autonomous transaction in distributed transactions , it just does not support.

In oracle9i release 2, your code will work fine.


now onto your solutions:

1. Use UTL_FILE to write a file instead,

2. execute procedures and log error in the same database. I don' t quite understand why you do all your transactions remotely.
0
 

Author Comment

by:nila
Comment Utility
I am using oracle 9i release 2

I can not use UTL_File package for certain DB serever access issues.

I am executing the procedures and log_error procedure on the same DB. But the procedures are used to insert data into remote DB.

The scenario is like I want to select data from DB1 and insert into DB2. There is a link from DB1 to DB2. The procedures are sitting on DB1 but have insert statements that say insert into table1@DB2 (select * from t1@DB1).
0
 
LVL 1

Expert Comment

by:JanMah
Comment Utility
Hi Nila,

This is a known issue with oracle 8.1.7.4.1 and before. I am not sure if this is fixed in 9i.

I understand from the code, that you need to log the exception in a table and what that to be in seperate transaction. You could avoid the autonomous transaction to do that.

Raise the error in the a procedure and return the error code and message to the main procedure.
There based on the error (or success) log the error and after rolling back the transaction. As it is in a single transaction, you need not have seperate commit in each procedures. After rolling back the transaction in main procedure, log the error in a table and commit the transaction. (this will be only for the log)

Hope you are clear.

JanMah
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
I agree, please try something like this:
the procedure main definition:


procedure main( )
---
----
----
chid_procedure1( );
child_procedure2( );
Exception
     when others then
        RAISE;
     End;
End;
/


then when you use this in your application, you could do this:

declare
errmsg varchar2(500);
begin
main();
log_error(errmsg);
end;
/
0
 
LVL 6

Expert Comment

by:Jankovsky
Comment Utility
There is possible workaround through java stored procedure, creating new  independent connection through JDBC thin driver and log that way. But it's a bit complexed solution and it can decrease performance of the task.
0
 
LVL 9

Accepted Solution

by:
pratikroy earned 500 total points
Comment Utility
Hi Nila, Could you try having the rollback statement before you call the log_error (autonomous transaction).

You have mentioned that other child procedures have distributed transactions as well. You need to rollback/commit the distributed transactions before calling an autonomous transactions.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now