RE: Viewing the trigger source code - Piggyback Qu.

Charu,
Allthough the trigger text is stored as a long, you can take the long field, chop it at the linefeed characters and print it out (with line numbers). The lines you get will match the line numbers in the error message. Somewhere I have a pl/sql anonymous block that does that using instr.
chaim

"Charu Joshi" <joshic_at_mahindrabt.com>@fatcity.com on 08/01/2003 08:20:19 AM

Please respond to ORACLE-L_at_fatcity.com

Sent by: ml-errors_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

Is there any we way can see the 'line number' along with the trigger code text. For ex. with stored procs, packages etc. we can get the exact line number for a line of code. This helps greatly when any error occurs, it mentions the line number which you can jump to. Can we not do the same about triggers?

Thanks & Regards,
Charu.

-----Original Message-----
Behalf Of
Jesse, Rich
Sent: Friday, August 01, 2003 1:11 AM
To: Multiple recipients of list ORACLE-L

If you look at the source of the ALL_TRIGGERS view (at least in 8i), you'll
see that the CREATE ANY TRIGGER priv is needed for a schema to see triggers
of another schema. Since this probably isn't what you want, you have some
options:

1. GRANT SELECT ON DBA_TRIGGERS TO your_schema;
2. Reverse engineer the ALL_TRIGGERS view, removing the restriction and calling it something else like OUR_TRIGGERS. Put this view in a non-SYS/non-SYSTEM DBA schema of yours and GRANT SELECT where necessary.
3. Modify the ALL_TRIGGERS view directly, removing the restriction and waiting for the appropriate rant from Mladen about not modifying the data dictionary. And I'd agree with him -- don't do this.

Enjoy!
Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA

> -----Original Message-----
> From: IT - Database (Do Not Use)
> [mailto:dbamail_at_police.edmonton.ab.ca]
> Sent: Thursday, July 31, 2003 2:59 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Viewing the trigger source code
>
>
> My brain is not working today....I cannot figure out how to
> allow one user
> to look at the source code (i.e. trigger_body) for a trigger
owned by
> another user. The fact that there is an all_triggers view leads
me to
> believe that it must be possible....but I can't figure out
> how to do it and
> I have RTFM and I can't find any references there either.
> Can anyone help
> me out?
>
> TIA
> Debbie

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051
http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting
services
------------------------------------------------------------------
---
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.net
--
Author: Charu Joshi
  INET: joshic_at_mahindrabt.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
-- 
Author: 
  INET: Chaim.Katz_at_Completions.Bombardier.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).