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.
>----- Original Message ----- >From: Remacle Jean <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 17 Jun 2002 22:48:18 > >Hi Hanna, > >If you are running 8I and up you can use autonomous >transactions. >Which permits you to commit a procedure without >affecting the calling >procedure's transaction status. >Meaning that you can commit a debuging/tracing >record before hitting the >commit in the calling trigger. > >And the best guidelines I found was in one of >Feurerstein's book. > >Jean Remacle > > > -----Original Message----- > From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED]] > Sent: lundi 17 juin 2002 20:43 > To: Multiple recipients of list >ORACLE-L > Subject: RE: Advance Oracle >Trigger Writing Advice > > Hi Chris, > > Thanks, > > But unfortunately I only >can see the error when the >record is inserted using a third party replication >product. Even then, if I >restart replication, then the record gets processes >without error. I was >hoping to somehow step through the trigger every >time it was fired, >regardless of what session or process cause it to >fire. > > Thnaks, > > Hannah > > > > -----Original Message----- > > From: >[EMAIL PROTECTED]@SUNGARD On Behalf Of >"Grabowy, Chris" <[EMAIL PROTECTED]> > > Sent: Monday, June 17, 2002 2:08 >PM > > To: Multiple recipients of list >ORACLE-L > > Subject: RE: Advance Oracle >Trigger Writing Advice > > > > I don't have any of that, but I >was just wondering if you >knew that you can > > use DBMS_OUTPUT in a trigger? >Which obviously can be >helpful when debugging > > a trigger. > > > > As for best practices, I would >look at Steve Feuerstein's >books. Or his > > best practices presentation, etc >can be found at > > http://www.quest-pipelines.com/. > > > > HTH > > > > > -- > 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). >-- >Please see the official ORACLE-L FAQ: >http://www.orafaq.com >-- >Author: Remacle Jean > 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). >--------------------------------------------------- >----------------- >--------------------------------------------------- >----------------- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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).
