Help! ORA-00164 - autonomous trans problem in 8.1.7 NOT in 8.1.5

From: Doug C <dcowles_at_i84.net>
Date: Thu, 31 May 2001 19:16:11 -0700
Message-ID: <F001.0031747D.20010531182046@fatcity.com>

Recently upgraded a development database from 8.1.5 to 8.1.7 with the u080105 script. There is now a certain type of code that errors out with a ORA-00164 autonomous transaction disallowed within distributed transaction. But the same code works on 8.1.5, as if a piece of functionality was actually cut out by the upgrade.

There's a metalink hit on a forum where an Oracle rep makes the following statement:

"Autonomous transaction are not supported in a distributed transaction.
Database links and autonomous transactions are two database technologies that do in fact not work together. "

Also..

"Autonomous transaction are not supported in a distributed transaction. In 8i
this will be the case. We are considering extending autonomous transactions to be able to be used even in a distributed transaction, in the future releases. "

Ok.. so it sounds like it shouldn't work in *ANY* Oracle release.. but it works in 8.1.5 -

Here's a basic piece of the code that *works* in 8.1.5 and does *not* in a LATER release ... 8.1.7

The database link is clearly the "distributed" problem that is bothering it..

CREATE OR REPLACE PROCEDURE vwmtest AS

v_date DATE;

PROCEDURE write_log IS

        pragma AUTONOMOUS_TRANSACTION;
        BEGIN
                insert into ofs_log values

('VWMTEST','D',0,SYSDATE,SYSDATE,0,0,0,0,1,'F','B','3'); ----- just a log table - we want to make an entry regardless of whether the parent transaction completes.
commit;
EXCEPTION
        WHEN OTHERS THEN dbms_output.put_line('OThers failure in write_log'); END write_log;

BEGIN -- the source of the problem

        SELECT  super_date INTO v_date
        from AREMOTETABLE_at_ADATABASELINK
        where part = 54;

WRITE_LOG;               -- the autonomous transaction

EXCEPTION
        WHEN OTHERS
        THEN dbms_output.put_line('Others error in vwmtest');

END vwmtest;

I've left some of the table definitions out but the concept is clear. Despite the info I found on Oracle's knowledge baset.. This WORKS on a PRIOR release.. 8.1.5...

Any ideas?

Thanks,
Doug

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Doug C
  INET: dcowles_at_i84.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu May 31 2001 - 21:16:11 CDT