| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with autonomous transaction - it isn't
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:
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.
Received on Tue Jan 29 2002 - 07:07:54 CST
 |
 |