create or replace type table_of_vc as table of Varchar2(4000);
/
create or replace Function SplitLine(
p_line in Varchar2,
p_delim in table_of_vc default table_of_vc(' '),
p_min_length in Number default 3)
return table_of_vc
is
/* v_delim_len enthaelt nach jedem Split die Laenge des verwendeten
Delimiters */
v_delim_len number;
v_split_pos number;
v_split_from_pos number := 1;
v_split_str varchar2(4000);
v_ret table_of_vc := table_of_vc();
begin
begin
select
pos , len into v_split_pos, v_delim_len
from (
select
len,
pos,
row_number () over (order by pos) r
from (
select
length(column_value) len,
instr(p_line,column_value,1) pos
from
table(p_delim)
) where pos > 0
) where r = 1;
exception
when no_data_found then
v_split_pos := 0;
when others then
null;
return v_ret;
end;
while v_split_pos > 0 loop
v_split_str := substr(p_line, v_split_from_pos, v_split_pos-v_split_from_pos);
if length(v_split_str) >= p_min_length then
v_ret.extend;
v_ret(v_ret.count) := v_split_str;
end if;
v_split_from_pos := v_split_pos + v_delim_len;
begin
select
pos , len into v_split_pos, v_delim_len
from (
select
len,
pos,
row_number () over (order by pos) r
from (
select
length(column_value) len,
instr(p_line,column_value,v_split_from_pos) pos
from
table(p_delim)
) where pos > 0
) where r = 1;
exception
when no_data_found then
v_split_pos := 0;
end;
end loop;
v_split_str := substr(p_line,v_split_from_pos);
if length (v_split_str) >= p_min_length then
v_ret.extend;
v_ret(v_ret.count) := v_split_str;
end if;
return v_ret;
end SplitLine;
/
create or replace procedure show_proc_in_package(
proc_name in varchar2,
pack_name in varchar2,
owner_name in varchar2 default user)
as
v_proc_name varchar2(30);
v_pack_name varchar2(30);
v_owner varchar2(30);
v_proc_name_expected boolean := false;
v_in_proc boolean := false;
begin
v_proc_name := upper( proc_name);
v_pack_name := upper( pack_name);
v_owner := upper(owner_name);
<<lines>>
for l in (select
text source_line
from
all_source
where
type = 'PACKAGE BODY' and
name = v_pack_name and
owner = v_owner
order by
line
) loop
for t in (select
upper(column_value) token
from
table(cast(
SplitLine(
l.source_line,
table_of_vc(' '),
1)
as table_of_vc))
) loop
if t.token = 'PROCEDURE' or
t.token = 'FUNCTION' then
if v_in_proc then
exit lines;
end if;
v_proc_name_expected := true;
else
if v_proc_name_expected = true then
if t.token = v_proc_name then
v_in_proc := true;
end if;
end if;
v_proc_name_expected := false;
end if;
end loop;
if v_in_proc then
dbms_output.put_line(substr(l.source_line,1,length(l.source_line)-1));
end if;
end loop;
end;
/