René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Getting the nth source code line of a trigger

While it is fairly simple to get the n th source line of a PL/SQL Procedure or Package, it is harder to to get it with a trigger. This is because the source of Procedures/Packages is stored in ALL_SOURCE, which contains a row for each source code line. The source code of triggers, however, is stored in a LONG in all_triggers. The following Procedure can be used to get the n th source line of a trigger:
create or replace function get_nth_line_in_trigger(tn in varchar2, n number) 
return varchar2 is
  x varchar2(4000);
  b number;
  c number;
begin
  select trigger_body into x from all_triggers where trigger_name = tn;
  b:=instr(x,chr(10),1,n);
  c:=instr(x,chr(10),1,n+1);
  return substr(x,b,c-b);
end;