Re: Where is line 21? ++ USER_TRIGGERS vs USER

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 16 Nov 2004 05:59:23 -0500
Message-ID: <BuKdnfR9u6wWQATcRVn-qg@comcast.com>

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:1100596490.115349.50830_at_c13g2000cwb.googlegroups.com...
| DA Morgan wrote:
| > Mark C. Stock wrote:
| >
| > > "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in
| message
| > > news:1100511110.358057.263800_at_c13g2000cwb.googlegroups.com...
| > > | DA Morgan wrote:
| > > | > Ramon F Herrera wrote:
| > > | >
| > > | > > Hello:
| > > | > >
| > > | > > I keep on getting error messages like the following:
| > > | > >
| > > | > > ------------------
| > > | > > Record 7: Rejected - Error on table SHAWS.
| > > | > > ORA-00001: unique constraint (DATABASE.CACHE) violated
| > > | > > ORA-06512: at "DATABASE.SYNCSHAWS", line 21
| > > | > > ORA-04088: error during execution of trigger
| 'DATABASE.SYNCSHAWS'
| > > | > > -------------------

| <snip>

|


| >

| > And not to rub it in ... Oracle does not report errors where
| > they first originate but often later on in the code.

| This is of course true, I can't believe however that you don't start by
| looking at the reported error and then tracing the problem back.

| In this case I'd be looking at line 21 of the trigger
| DATABASE.SYNCSHAWS (terrible name for a schema by the way), seeing what
| that contained, tracking it back to the offending insert statement and
| looking at the data from record 7 and the existing state of the table
| SHAWS.

| I wouldn't just print out my text file and gaze at it waiting for
| inspiration. I also wouldn'ty assume that the code contained in the
| version of the text file I had found on disk was the same as the code
| being executed by the RDBMS.

Let me rephrase my question/comment.... "Boy, I feel stupid -- I really should know that trigger source now shows up in USER_SOURCE, and I even see TRIGGER listed in the 8i docs as a value for TYPE, but for the life of me I can't remember or figure out when the feature actually started working -- could somebody please help me?" In other words, Niall, thanks for clarifying.

Regarding looking at the line reported (more for the benefit of newbies monitoring this) -- like any parser/compiler/etc., Oracle reports the error where it got confused, not necessarily where the developer made the mistake. If you make a basic syntax mistake, the error location is fairly accurate, but if you miss a closing token (parens, quotes, etc.) or SQL keyword, the error shows up where Oracle finally gave up trying to decipher your code. So, per Niall's advice, go to the database source and start at that line, walking backwards until you trip on your error, trying to understand what caused Oracle to get confused.

++ mcs Received on Tue Nov 16 2004 - 04:59:23 CST

Re: Where is line 21? ++ USER_TRIGGERS vs USER_SOURCE