Re: Problem with autonomous transaction - it isn't

After searching in Metalink (and the reaction of Jonathan) I found the problem:
Bug 1375214 fits the problem:
a cursor-loop on a "FOR UPDATE" cursor, which calls the "autonomous transaction"
procedure. Without the "for update" it works as expected.

This bug is supposed to be present in 8.1.5 to 8.1.7, fixed in 9i. I have not yet
found evidence of fixes for the "older" versions.

Hans Kesting

"Hans Kesting" <hkesting_at_intapps.nl> wrote in message news:3c569dc5$0$227$4d4ebb8e_at_news.nl.uu.net...
> Daniel,
>
> I tried your procedures, and they worked (I hadn't expected otherwise).
> I even put it in a package (my procedure *is* in a package), and it still
> worked.
>
> However, my procedure still doesn't work!
> Here is my code:
>
> --------------------------------------------------------------------------

--


> ----

> PROCEDURE WriteToLog(p_dump CHAR, p_code NUMBER, p_msg CHAR) IS

>     PRAGMA AUTONOMOUS_TRANSACTION;

>     my_msg VARCHAR2(100);

> BEGIN

>     IF g_run_nr IS NULL THEN

>         SELECT seq_logrun.NEXTVAL INTO g_run_nr FROM dual;

>     END IF;

>

>     SELECT seq_logid.NEXTVAL INTO g_lognr FROM dual;

>     my_msg := substr(p_msg,1,100);

>     INSERT INTO LOG_INTF (id, run_nr, dump_rec, moduul, datum, errorcode,

> omschrijving)

>     VALUES (g_lognr, g_run_nr, p_dump, g_module, SYSDATE, p_code, my_msg);

>     COMMIT;

> END WriteToLog;

> --------------------------------------------------------------------------

--


> ------

>

> Basically there is no important difference:

> - PRAGMA AUTONOMOUS_TRANSACTION defined in the correct spot

> - insert into a table

> - commit to end the transaction

>

> I ran this on the same database, under the same username, as your

procedure.


> Would the sequence-select interfere?

>

> Hans Kesting

>

>

> "Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message

> news:0$--$%%%%--%-%$_%$@news.noc.cabal.int...

> > 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

> >

> > ========= WAS CANCELLED BY =======:

> > From: "Daniel A. Morgan" <damorgan_at_exesolutions.com>

> > Control: cancel <3C5130E6.E2EEFCC2_at_exesolutions.com>

> > Subject: cmsg cancel <3C5130E6.E2EEFCC2_at_exesolutions.com>

> > Date: Mon, 28 Jan 2002 01:41:10 GMT

> > Message-ID: <cancel.3C5130E6.E2EEFCC2_at_exesolutions.com>

> > 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!news.fh-hannover.de!fu-berlin.de!newsfeed01.sul.t-onli


>

ne.de!t-online.de!fr.clara.net!heighliner.fr.clara.net!news-spur1.maxwell.sy


>

r.edu!news.maxwell.syr.edu!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.


> microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller

> > Xref: news.uni-stuttgart.de control:40722265

> >

> > This message was cancelled from within The Unacanceller's glorious new

> software, Lotus 1-2-3 For Rogue Cancellers.

>

>