Bug 1375214 seems to fit - fixed in version 9.
There may be a backport to 8.1.7 by now
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
Hans Kesting wrote in message <3c5184d1$0$228$4d4ebb8e_at_news.nl.uu.net>...
>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 Sun Jan 27 2002 - 08:45:34 CST
