Autonomous transaction within distributed transactions

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

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?

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

Yes, that is.

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

Oracle consider a procedure as one transaction

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.

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.

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).

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

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;
/

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.