RE: RE: Advance Oracle Trigger Writing Advice

Hi Hannah,

I think you could use a simpler feature to brute-force a trace of your trigger's execution path. The SYS.DBMS_SYSTEM.KSDWRT procedure provides output facilities for the alert.log and session trace files. SYS.DBMS_SYSTEM.KSDWRT(1,'I am here') would write that message to the server process' trace file. SYS.DBMS_SYSTEM.KSDWRT(2,'This message goes to the alert log') does what the message describes, although I myself would pollute the alert log with debug messages.

Just curious on the mainframe tool you are suing. Could it Databridge?

Tony Aponte

-----Original Message-----
Sent: Tuesday, June 18, 2002 8:38 AM
To: Multiple recipients of list ORACLE-L

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: root_at_fatcity.com_at_SUNGARD On Behalf Of "Stephane Faroult"<sfaroult_at_oriolecorp.com>
> 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: johanna.doran_at_sungard.com

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: ListGuru_at_fatcity.com (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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Aponte, Tony
  INET: AponteT_at_hsn.net

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: ListGuru_at_fatcity.com (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).