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

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'
> | > > -------------------
> | > >
> | > > However, when I take a look at the file that contains
> | > > the SQL/PL code (in this case, a trigger) line 21 doesn't
> | > > seem to be the right line. Is there some sort of magic
> | > > delta number to be added/substracted?
> | > > Perhaps I should remove the empty lines in my source code
> | > > and count again?
> | > >
> | > > -Ramon
> | >
> | > No. It is either 21 or one of the 20 lines before it.
> |
> | Hmmm
> |
> | I must be missing something here,
> |
> | I've always found that
> |
> | Select line,text from user_source
> | where type = 'TRIGGER'
> | and name = '<your trigger name>'
> | and line = 21
> | order by line;
> |
> | useful for this sort of query?
> |
> | actually I normally do
> |
> | and line between x-3 and x+ 3 where x is the line number you want to
> | avoid the problems of receiving
> |
> | LINE TEXT
> | ==== =========
> | 21 COMMIT;
> |
> | 1 Row selected.
> |
> | in return :) .
> |
> | Niall
> |
>
> how about a little Remedial DBA, if you don't mind
>
> when did trigger code start getting listed in USER_SOURCE and not just the
> TRIGGER_BODY of USER_TRIGGERS?
>
> ++ mcs

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

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)