More often than not, I need to search dba_source for specific keywords. But, you ask, how about the context of that code?
The following script will display adajcent code lines, group them together, and if that wasn’t already delightful enough, visually separate code blocks for viewing pleasure.
set serveroutput on trimspool on feed off echo off recsep off verify off
def searching = SYSMAN
def margin = 3
declare
owner1 varchar2(100);
name1 varchar2(100);
type1 varchar2(100);
line1 varchar2(100);
ns varchar2(100);
ns1 varchar2(100);
blockdelim varchar2(100) := chr(13) || '--------------------' || chr(13);
begin
dbms_output.enable(null);
for r in (select rownum n, owner, name, type, line, text
from (select distinct s.*
from dba_source s,
(select owner, name, type, line - &margin l1, line + &margin l2
from dba_source
where upper(text) like upper('%&searching%')) l
where s.owner = l.owner
and s.name = l.name
and s.type = l.type
and s.line between l.l1 and l.l2
order by s.owner, s.type, s.name, s.line)) loop
ns := r.owner || r.type || r.name;
if r.n > 1 and
(
ns <> ns1
or r.line - line1 > 1
)
then
dbms_output.put_line(blockdelim);
end if;
dbms_output.put_line(rpad(r.owner, 15)
|| rpad(r.name, 30)
|| rpad(r.type, 20)
|| lpad(r.line, 6)
|| case when instr(upper(r.text), upper('&searching')) > 0 then ' * ' else ' ' end
|| rtrim(rtrim(r.text, chr(10)), chr(13))
);
owner1 := r.owner;
name1 := r.name;
type1 := r.type;
line1 := r.line;
ns1 := ns;
end loop;
end;
/
The output looks like this:
Kommentar verfassen