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
>
>
>
========= WAS CANCELLED BY =======:
From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Control: cancel <1012142653.18135.1.nnrp-12.9e984b29_at_news.demon.co.uk>
Subject: cmsg cancel <1012142653.18135.1.nnrp-12.9e984b29_at_news.demon.co.uk>
Date: Mon, 28 Jan 2002 00:01:53 GMT
X-NNTP-Posting-Host: pqcfdvxx.demon.co.uk:191.114.253.205
X-Trace: news.demon.co.uk 131724017nnrp-47:0238 NO-IDENT pqcfdvxx.demon.co.uk:191.114.253.205
X-Complaints-To: abuse_at_demon.net
Message-ID: <cancel.1012142653.18135.1.nnrp-12.9e984b29_at_news.demon.co.uk>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1
Path: news.uni-stuttgart.de!cert.uni-stuttgart.de!news.belwue.de!news.uni-ulm.de!rz.uni-karlsruhe.de!blackbush.xlink.net!blackbush.de.kpnqwest.net!newsfeed01.sul.t-online.de!t-online.de!fr.clara.net!heighliner.fr.clara.net!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40720460
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers.
Received on Sun Jan 27 2002 - 08:45:34 CST
