Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!c03.atl99!news.webusenet.com!fu-berlin.de!uni-berlin.de!adsl-133-52.init7.NET!not-for-mail From: Rene Nyffenegger Newsgroups: comp.databases.oracle.misc Subject: Re: Viewing code in stored procedures and functions Date: 3 Mar 2003 18:21:00 GMT Organization: NetArchitects Lines: 238 Message-ID: References: <1046343717.620180@news.thyssen.com> NNTP-Posting-Host: adsl-133-52.init7.net (213.144.133.52) X-Trace: fu-berlin.de 1046715660 59791991 213.144.133.52 (16 [82536]) User-Agent: slrn/0.9.7.4 (Win32) Xref: newsfeed1.easynews.com comp.databases.oracle.misc:94297 X-Received-Date: Mon, 03 Mar 2003 11:20:55 MST (news.easynews.com) > > "Rene Nyffenegger" wrote in message > news:b3t4me$1pkgn1$2@ID-82536.news.dfncis.de... >| >| > >| > "Chris ( Val )" wrote in message >| > news:b3sumq$1pnu9v$1@ID-110726.news.dfncis.de... >| >| >| >| "Chris ( Val )" wrote in message news:b3sp7g$1q1t95$1@ID- >| >| >| >| [snip question] >| >| >| >| Hi everyone, I found what I was after, and that was: ALL_SOURCE. >| >| >| >| I'm still interested in any good books though :-). >| > >| > DOH!. >| > >| > It didn't work after all. Silly me had an existing procedure >| > which wasn't in a package. That's where the view of the source >| > code came from. >| > >| > Any ideas ?. >| > >| > Cheers. >| > Chris Val >| >| >| Chris >| >| You were nonetheless right about xyz_SOURCE. >| >| select text from xyz_SOURCE where type = 'PACKAGE BODY' and name = '| name>' and so on. Unfortunatly, it's not possible look at a specific >| procedure/function within the package without parsing the text of the package >| yourself (that is, I wouldn't know of a possibility). So, you can only look at >| the entire package. > > Hi Rene. > > That is unfortunate, however I appreciate the help. > > Cheers. > Chris Val Hello Chris, I couldn't resist and wrote a little procedure that might get you started doing that: First, we need a helper Function and its associated helper type in order to split source lines into tokens: 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; / The interesting thing is off course show_proc_in_package the parameters of which are hopefully self explanatory. 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); <> 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; / Naturally, there is no checking for comment delimiters (/* ... */ and --) and overloading is not hanled either. Anyway, I hope you can use it. Let me know if you improve it. Rene Nyffenegger -- Projektleitung und Entwicklung in Oracle/C++/C# Projekten http://www.adp-gmbh.ch/cv.html