Problem with autonomous transaction - it isn't

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 - 10:20:05 CST