ORA-00164: Autonomous Transaction Disallowed Within Distributed Transaction

From: David Ward <dave_at_nottelling.com>
Date: Fri, 08 Nov 2002 05:11:46 GMT
Message-ID: <3DCB4792.E6354979@nottelling.com>

Hi,

I have been getting error 'ORA-00164: Autonomous Transaction Disallowed Within Distributed Transaction' in my PL/SQL code. I have found exactly why it appears,but have not been able to find a work around.

I need to get information through a dblink that calls a stored procedure, and returns various information to my procedure. Before and after this call, I may have pending incomplete transactions, so I am unable to do a commit before or after the dblink call.

After the dblink call I have been using the pragma autonomous transaction, so that I can find a value for a field at the time of the last commit. I am unable to get the value before the dblink call, as the code is called within a proprietary package.

To try to overcome/investigate the problem, I have written the sample code below, with the output as shown after it:

declare
v varchar2(1000);
-- procedure x has autonomous trans

procedure x is

    PRAGMA AUTONOMOUS_TRANSACTION;
  begin

       pkg_pipe.putline('x started');
  null;
exception

   when others then

       pkg_pipe.putline('errorx');
       pkg_pipe.putline(sqlerrm);

end;

• procedure y tries to close the database, and then call x procedure y is begin begin pkg_pipe.putline('closing mydblink');
  • either of the following 2 lines produce the same results.
  • dbms_session.close_database_link('mydblink.world'); EXECUTE IMMEDIATE 'alter session close database link mydblink.world'; pkg_pipe.putline('closing all done'); exception when others then pkg_pipe.putline('errory'); pkg_pipe.putline(sqlerrm); end; pkg_pipe.putline('x start'); x(); exception when others then pkg_pipe.putline('errory'); pkg_pipe.putline(sqlerrm); end;
• procedure z is just a call to y, to see if a caller or two will catch the error. procedure z is begin pkg_pipe.putline('y start'); y(); exception when others then pkg_pipe.putline('errorz'); pkg_pipe.putline(sqlerrm); end;
• main program, reset/rollback, then view the calculated column, then try update, then
• try to do autonomous transaction in a round about way. begin rollback; pkg_pipe.enable('mypipe');
  • the package below will use a defined database link, and format the info as needed. v := pkg_get_dblink.myDetails (123456,sysdate); pkg_pipe.putline('return from mydblink ' || v); pkg_pipe.putline('z starting'); z(); pkg_pipe.putline('end transu5 done'); exception when others then pkg_pipe.putline('main program'); pkg_pipe.putline(sqlerrm); end;

Now once this runs, I get the following output. You will note that none of the exception handlers will catch the ORA-00164, and that I'm unable to close the database (in use).

This is the output from the pipe, so that I can see things real time.

14:34:34 x start
14:35:45 getting Details on object 123456
14:35:45 return from mydblink No Data returned from mydblink
14:35:46 z starting
14:35:46 y start
14:35:46 closing mydblink
14:35:46 errory
14:35:46 ORA-02080: database link is in use
14:35:46 x start

Now in SQL*Plus , you get the ERROR, no exception handler appears to catch it.

unixerror says:
oerr ora 02080
02080, 00000, "database link is in use"

// *Cause: a transaction is active or a cursor is open on the database
// linkgiven in the alter session close database link <link> command.
// *Action: commit or rollback, and close all cursors

Remember I can't commit, or rollback, there may be incomplete/pending updates before and after the query.

Asking around, here is one response I got, but I was hoping that there was something that could be done. Darn Oracle8.1.7+ !!!

"Capturing the error __WILL NOT__ solve the problem. You will likely cause even worse things to happen! You must close the database link __BEFORE__ the autonomous transaction is started; if the database link is active (ie transaction is actually open) you are screwed -- there is nothing that you can do."

Is there a way around the Database link is in use, or some other way to close the link, or is there a way to capture the 00164 error?

Thanks,

Darren Received on Thu Nov 07 2002 - 23:11:46 CST