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@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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
