Doug,
This is a case where something slipped by in one
release, but was caught in a later release (shades of
Personal Oracle 8.0.3 which had complete EE
functionality by mistake). 8i doesn't support
autonomous transactions within distributed
transactions, but it wasn't officially prevented with
the ORA-164 error until 8.1.6+.
Supposedly this functionality will be available in 9i,
but not necessarily the first release.
HTH,
-- Anita
--- Doug C <[EMAIL PROTECTED]> wrote:
> 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).
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
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).
