Hi,
The problem is fairly complex and I am unable to reproduce it by manually
feeding in the statements of the suspect transaction. Both statements hit triggers
which use autonomous transactions to do things. Its the second statement that fails
and I am unable to tell if something in the first trigger *primes* a tables, because
when you rerun the transaction, it does not error out. We are using a product to
replicate data from a mainframe to Oracle. The target tables in Oracle have triggers
on them. When the product processes the suspect transactions, an Oracle error is
raised. I can read the transaction in the Mainframe logs and I see that the
transactions fail every 100 or so transactions (and obviously I did not manually feed
in THAT many).
I think that I am going to look into dbms_pipe, I have used that in the past
to hone in on renegade sql:> Good idea. The triggers already both use autonomous
trans and I am writing out to a table whenever I enter and leave a block of code. The
last block visited befor error is a select statement only, so I am thinking that
something reallly strange is going on.
Thanks for the suggestion. I can get MUCH better control over things with
dbms_pipe without effecting the code in the trigger ( I think that the commits can) .
Darn, can't believe that I didn't think about it!
Thanks,
Hannah
> -----Original Message-----
> From: [EMAIL PROTECTED]@SUNGARD On Behalf Of "Stephane
>Faroult"<[EMAIL PROTECTED]>
> Sent: Tuesday, June 18, 2002 6:48 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: RE: Advance Oracle Trigger Writing Advice
>
> Hanna,
>
> I still have a fairly confused view of what your problem is and how you plan to
>solve it. Jean is right about the solution - autonomous transactions are the easier
>way (dbms_pipe is the other one) to ensure that you will not lose any track of your
>debugging information even if the transaction is rolled back.
> FYI, Oracle 9i provides you (it is documented with DDL triggers, but in fact works
>with any trigger) with a function which returns the text of the statement which fired
>the trigger. This function can be faked with previous versions (did it on 7.3, 8.0.5,
>8.1.5, 8.1.7). Can be helpful if identifying a faulty statement is what you are
>looking for. Bind variables can also be caught but it involves a bit of backbending.
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
