it is not as easy as it sounds since there could be white space, newlines, etc.

You'd have to reformat the text, glue a few lines of code together, and use like.

something similar to:

ops$tkyte%ORA11GR2> create or replace procedure foo
  2  as
  3  begin
  4          delete
  5          from
  6          t
  7          where
  8          username = 'SCOTT';
  9  end;
 10  /

Procedure created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> variable x varchar2(80)
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :x := q'|DELETEFROMTWHEREUSERNAME='SCOTT'|'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select name, type, line,
  2         substr( big_txt, instr( big_txt, :x ), 40 ) || '...' snippet
  3    from (
  4  select name, type, line,
  5         lag(txt,3) over (partition by name, type order by line) ||
  6         lag(txt,2) over (partition by name, type order by line) ||
  7         lag(txt,1) over (partition by name, type order by line) ||
  8             txt ||
  9         lead(txt,1) over (partition by name, type order by line) ||
 10         lead(txt,2) over (partition by name, type order by line) ||
 11         lead(txt,3) over (partition by name, type order by line) big_txt
 12    from (
 13  select name, type, line,
 14         upper(replace( replace( replace( translate( text, chr(13)||chr(10)||chr(9), '   ' ), ' ', ' ~'), '~ ', ''), ' ~', '')) txt
 15    from user_source
 16         )
 17             )
 18   where instr( big_txt, :x ) > 0
 19  /

NAME                           TYPE               LINE SNIPPET
------------------------------ ------------ ---------- -------------------------------------------
FOO                            PROCEDURE             5 DELETEFROMTWHEREUSERNAME='SCOTT';...
FOO                            PROCEDURE             6 DELETEFROMTWHEREUSERNAME='SCOTT';END;...
FOO                            PROCEDURE             7 DELETEFROMTWHEREUSERNAME='SCOTT';END;...

i turn whitespace (end of line, tabs - anything else you can think of) into a space, turn multiple spaces into "nothing" and upper case the string.

using lag and lead - we concatenate the prior and latter three lines into one - to pick up anything that spans up to seven lines.

then we look for instr's - yes, you'll get multiple hits - but it would see ok to do that since you are just scanning.


you could further refine this to add a where clause on user dependencies to search only code that COULD have a delete if you like...