find string with quotes and double quotes in all oracle packages

Question

I am trying to find a query which will return all the string inside all the packages and procedures like like '2%'. For example in dynamic query

WHEN SUBSTR(S.project,0,1) LIKE ''2%'' THEN ''newProject''

and like in normal query

WHERE B.project LIKE '2%'

I tried with this query

select *
  from user_source u
 where u.type in ('FUNCTION', 'PACKAGE','PACKAGE BODY', 'TRIGGER','PROCEDURE')
   and upper(u.text) like upper('%LIKE%')

But I am unable to find a query which can give a proper result for like '2%' in both dynamic and static query.

Answer 1

Here's an example: when dealing with single quotes you have to escape, it is simpler to use the "q" quoting mechanism.

SQL> create or replace function f_test return number is
  2    l_cnt number;
  3  begin
  4    select count(*)
  5      into l_cnt
  6      from emp
  7      where empno like '7%';
  8    return l_cnt;
  9  end;
 10  /

Function created.

SQL> select f_test from dual;

    F_TEST
----------
        12

SQL> select *
  2  from user_source
  3  where lower(text) like '%' || q'[like '7%']' ||'%';

NAME       TYPE               LINE TEXT
---------- ------------ ---------- ------------------------------
F_TEST     FUNCTION              7     where empno like '7%';

SQL>