Re: Problem with autonomous transaction

From: Daniel A. Morgan <damorgan_at_exesolutions.com>
Date: Fri, 25 Jan 2002 10:18:14 +0000
Message-ID: <3C5130E6.E2EEFCC2@exesolutions.com>

You have something messed up. AUTONOMOUS_TRANSACTION works flawlessly in 8.1.7 in my experience. Try running the following as a test:

CREATE TABLE t (
test_value VARCHAR2(30));

CREATE OR REPLACE PROCEDURE parent_block IS

BEGIN
   INSERT INTO t
   (test_value)
   VALUES
   ('Parent block insert');

child_block;

   ROLLBACK; EXCEPTION
   WHEN OTHERS THEN
      RAISE; END parent_block;

CREATE OR REPLACE PROCEDURE child_block IS

PRAGMA AUTONOMOUS_TRANSACTION; -- comment this out to see the difference

BEGIN
   INSERT INTO t
   (test_value)
   VALUES
   ('Child block insert');
   COMMIT; EXCEPTION
   WHEN OTHERS THEN
      RAISE; END child_block;
/

I use it in the class I teach to demonstrate how AUTONOMOUS_TRANSACTION works.

It hasn't failed me yet.

Daniel Morgan

Hans Kesting wrote:

> Hi,
>
> We do some processing using procedures stored in PL/SQL packages.
> During this processing we want to write to a logging table. Of course
> this must always be committed (so the trace is not lost when an error
> occurs) and this must be done independent of the main transaction.
>
> According to the documentation (8.1.7), this is what the pragma
> "autonomous_transaction" is designed for.
>
> But: it doesn't always work!
>
> On our development system (Oracle 8.1.6 on windows 2000) it does work
> as expected. Then we transferred everything to a freshly installed 8.1.7
> (also on Win2k),
> and there the autonomous transaction wasn't autonomous anymore!
> The commit closes the cursor in the main transaction so the next fetch
> returns a "fetch out of sequence" error.
>
> The application processes some input files (pre-stored into tables) to
> update the production tables. It uses three levels of packages:
> 1. a "master" package that calls several "process" packages
> 2. a set of "process" packages, one for each set of files
> 3. a "logger/utilities" package
>
> Within the logger package one procedure (the one that actually writes to the
> logging table) has the pragma autonomous_transaction (and commits the
> write).
> All processing is done on local tables, no distributed transactions are
> involved.
>
> On our 8.1.7 system, the cursor loop in the "process" package halts with
> a "fetch out of sequence" error.
>
> As I hope that it is not a bug, I suspect that there is a difference in
> installation
> between the two systems. The person that installed the 8.1.6 doesn't work
> here
> anymore, so we don't know what he did right, or what went wrong with the
> 8.1.7
> installation.
>
> Does anyone know how to get the autonomous transaction to work as
> advertised?
>
> Everything I could find about this was "AT's are the solution to your
> problem",
> instead of it being the cause of a problem!
>
> Hans Kesting
Received on Fri Jan 25 2002 - 04:18:14 CST

Re: Problem with autonomous transaction - it isn't