I don't know.... I've never tried.  It seems that if you have to search for dead code, well, you only need to do that once (so what if it goes slow once) since you'll obviously put into place the necessary documentation framework in the future to avoid the issue :)

Guess if I had to do it, I would put the names of the procedures/functions into a collection or temporary table and then select out everything that wasn't in the rest (instead of running a query per function/procedure, run a single query that returns likely ones)

but again, I just plain wouldn't do it - dead code?  if you have a serious problem with dead code over and over and over and over and over again, you need to fix the cause - not the effect.  Your approach is fairly hit or miss as it is (procedure names embedded in other procedure names - you look across ALL packages instead of just one)...


But, if I had to do this for whatever reason, I'd go for the "query" route:

ops$tkyte@ORA10GR2> create or replace package foo
  2  as
  3          procedure p1;
  4          procedure p2;
  5  end;
  6  /

Package created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace package body foo
  2  as
  3
  4  procedure p1
  5  is
  6  begin
  7          p2;
  8  end;
  9
 10  procedure p2
 11  is
 12  begin
 13          p1;
 14  end;
 15
 16  procedure p3
 17  is
 18  begin
 19          null;
 20  end;
 21
 22  end;
 23  /

Package body created.

ops$tkyte@ORA10GR2> with subroutines
  2  as
  3  (
  4  select name, substr( text, 1, instr( text, '(')-1 ) proc_name
  5    from (
  6  select name,
  7         trim( replace( replace(
  8             lower( translate(text,chr(13)||chr(10),'  ')),
  9             'procedure ', '' ),
 10             'function ', '' ) ) ||
 11          '(' text
 12    from user_source
 13   where (lower(text) like '%procedure %'
 14          or
 15          lower(text) like '%function %')
 16     and type = 'PACKAGE BODY'
 17         )
 18  ),
 19  packages
 20  as
 21  (
 22  select name, lower(text) text
 23    from user_source
 24   where type in ( 'PACKAGE BODY', 'PACKAGE' )
 25  )
 26  select s.*
 27    from subroutines S left outer join packages P
 28      on (     p.text like '%' || s.proc_name || '%'
 29           and p.text NOT like '%end ' || s.proc_name || '%'
 30           and p.text NOT like '%procedure ' || s.proc_name || '%'
 31           and p.text NOT like '%function ' || s.proc_name || '%'
 32         )
 33   where p.name is null
 34  /

NAME                           PROC_NAME
------------------------------ ----------
FOO                            p3