Re: Viewing code in stored procedures and functions

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 3 Mar 2003 18:21:00 GMT
Message-ID: <b406eb$1p0mjn$1@ID-82536.news.dfncis.de>

>
> "Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message
> news:b3t4me$1pkgn1$2_at_ID-82536.news.dfncis.de...

>|
>| >
>| > "Chris ( Val )" <chrisval_at_bigpond.com.au> wrote in message
>| > news:b3sumq$1pnu9v$1_at_ID-110726.news.dfncis.de...
>| >|
>| >| "Chris ( Val )" <chrisval_at_bigpond.com.au> wrote in message news:b3sp7g$1q1t95$1_at_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 = '<package
>| 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);

  <<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;
/  

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

Received on Mon Mar 03 2003 - 12:21:00 CST