Solved

Autonomous transaction within distributed transactions

Posted on 2004-08-18
11
1,524 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
ID: 11836010
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
ID: 11836022
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
ID: 11836101
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
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
ID: 11836116
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
ID: 11836181
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 11836326
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
ID: 11836564
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
ID: 11837499
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
ID: 11837616
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
ID: 11839744
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
ID: 11840001
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 recover a database from a user managed backup

813 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

13 Experts available now in Live!

Get 1:1 Help Now