Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> REPOST: Re: Problem with autonomous transaction - it isn't
You have something messed up. AUTONOMOUS_TRANSACTION works flawlessly in 8.1.7
in my experience. Try running the following as a test:
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
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 04:18:14 CST