Skip to Main Content
  • Questions

  • How to find the list of procedure inside the package which contain specific text ?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 31, 2019 - 2:10 am UTC

Last updated: August 05, 2019 - 9:06 am UTC

Version: oracle 12c

Viewed 1000+ times

You Asked

How to find the list of procedure inside the package which contain specific text ?

example : Table : emp has following columns
empno
ename
sal
job
comm
deptno


my requirement is I need to find list of procedure inside the package where "deptno" column is used.


suppose I have package which contain 10 procedures out 10 only procedures inside the package
where "deptno" column is used.

pkg_emp_detail

proc1
proc1
proc3
...
...
...
...
...
proc10


I want query which will display only 2 proc of package

say proc1 and proc2 where "deptno" column is used.



and Connor said...

We don't expose down the that level, but you could do some basic data mining of the source code. For example, lets say my code is:

SQL> create or replace
  2  package PKG is
  3    type t is table of number;
  4
  5    g1 int;
  6
  7    function F return number;
  8
  9    procedure P(p in number,
 10                q in out scott.emp.deptno%type);
 11
 12    cursor c is select * from dual;
 13  end;
 14  /

Package created.

SQL> create or replace
  2  package body PKG is
  3    type t is table of number;
  4
  5    g int;
  6
  7    function F return number is
  8    begin
  9      null;
 10      null;
 11      null;
 12      return 1;
 13    end;
 14
 15    procedure P(p in number,
 16                q in out scott.emp.deptno%type) is
 17    begin
 18      for i in ( select * from scott.emp )
 19      loop
 20         null;
 21      end loop;
 22
 23      select deptno into q from scott.emp;
 24
 25    end;
 26
 27  begin
 28    select max(deptno)
 29    into   g
 30    from scott.emp;
 31
 32  end;
 33  /

Package body created.

SQL> create or replace
  2  package PKG2 is
  3    type t is table of number;
  4
  5    g1 int;
  6
  7    function F1 return number;
  8
  9    procedure P2(p in number,
 10                q in out scott.emp.deptno%type);
 11
 12    cursor c is select * from dual;
 13  end;
 14  /

Package created.

SQL> create or replace
  2  package body PKG2 is
  3    type t is table of number;
  4
  5    g int;
  6
  7    function F1
  8    return number is
  9    begin
 10      null;
 11      null;
 12      null;
 13      return 1;
 14    end;
 15
 16    procedure P2(p in number,
 17                q in out scott.emp.deptno%type) is
 18    begin
 19      for i in ( select * from scott.emp )
 20      loop
 21         null;
 22      end loop;
 23
 24      select deptno into q from scott.emp;
 25
 26    end;
 27
 28  begin
 29    select max(deptno)
 30    into   g
 31    from scott.emp;
 32
 33  end;
 34  /

Package body created.

SQL>
SQL>


I could write something to assign (loosely) each line of code to its parent subroutine, eg

SQL> with t as
  2  ( select
  3    case
  4      when line = 1 then name
  5      when ltrim(lower(text)) like 'function%' then regexp_substr(substr(ltrim(text),10),'[[:alnum:]]+')
  6      when ltrim(lower(text)) like 'procedure%' then regexp_substr(substr(ltrim(text),11),'[[:alnum:]]+')
  7    end tag,
  8    u.*
  9  from user_source u where name in ( 'PKG','PKG2')
 10  )
 11  select last_value(tag ignore nulls) over ( partition by name, type order by line ) as routine,
 12         text
 13  from   t;

ROUTINE    TEXT
---------- ------------------------------------------------------------
PKG        package PKG is
PKG          type t is table of number;
PKG
PKG          g1 int;
PKG
F            function F return number;
F
P            procedure P(p in number,
P                        q in out scott.emp.deptno%type);
P
P            cursor c is select * from dual;
P          end;
PKG        package body PKG is
PKG          type t is table of number;
PKG
PKG          g int;
PKG
F            function F return number is
F            begin
F              null;
F              null;
F              null;
F              return 1;
F            end;
F
P            procedure P(p in number,
P                        q in out scott.emp.deptno%type) is
P            begin
P              for i in ( select * from scott.emp )
P              loop
P                 null;
P              end loop;
P
P              select deptno into q from scott.emp;
P
P            end;
P
P          begin
P            select max(deptno)
P            into   g
P            from scott.emp;
P
P          end;
PKG2       package PKG2 is
PKG2         type t is table of number;
PKG2
PKG2         g1 int;
PKG2
F1           function F1 return number;
F1
P2           procedure P2(p in number,
P2                       q in out scott.emp.deptno%type);
P2
P2           cursor c is select * from dual;
P2         end;
PKG2       package body PKG2 is
PKG2         type t is table of number;
PKG2
PKG2         g int;
PKG2
F1           function F1
F1           return number is
F1           begin
F1             null;
F1             null;
F1             null;
F1             return 1;
F1           end;
F1
P2           procedure P2(p in number,
P2                       q in out scott.emp.deptno%type) is
P2           begin
P2             for i in ( select * from scott.emp )
P2             loop
P2                null;
P2             end loop;
P2
P2             select deptno into q from scott.emp;
P2
P2           end;
P2
P2         begin
P2           select max(deptno)
P2           into   g
P2           from scott.emp;
P2
P2         end;


Now I can query that to locate relevant rows

SQL> with t as
  2  ( select
  3    case
  4      when line = 1 then name
  5      when ltrim(lower(text)) like 'function%' then regexp_substr(substr(ltrim(text),10),'[[:alnum:]]+')
  6      when ltrim(lower(text)) like 'procedure%' then regexp_substr(substr(ltrim(text),11),'[[:alnum:]]+')
  7    end tag,
  8    u.*
  9  from user_source u where name in ( 'PKG','PKG2')
 10  )
 11  select *
 12    from (
 13    select last_value(tag ignore nulls) over ( partition by name, type order by line ) as routine,
 14           name,
 15           type,
 16           text
 17    from   t
 18  )
 19  where  lower(text) like '%deptno%';

ROUTINE    NAME       TYPE                 TEXT
---------- ---------- -------------------- ------------------------------------------------------------
P          PKG        PACKAGE                            q in out scott.emp.deptno%type);
P          PKG        PACKAGE BODY                       q in out scott.emp.deptno%type) is
P          PKG        PACKAGE BODY             select deptno into q from scott.emp;
P          PKG        PACKAGE BODY           select max(deptno)
P2         PKG2       PACKAGE                            q in out scott.emp.deptno%type);
P2         PKG2       PACKAGE BODY                       q in out scott.emp.deptno%type) is
P2         PKG2       PACKAGE BODY             select deptno into q from scott.emp;
P2         PKG2       PACKAGE BODY           select max(deptno)


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

vicky, August 01, 2019 - 4:37 pm UTC

Thanks Connor McDonald for response
Connor McDonald
August 05, 2019 - 8:57 am UTC

Glad we could help

using PLSCOPE analysis

Rajeshwaran Jeyabal, August 02, 2019 - 7:23 am UTC

How about using PLSCOPE feature like this (Introduced in Oracle 11g and enhanced in Oracle 12.2 for plscope_settings = statements:all)?
demo@PDB1> alter package pkg compile plscope_settings='identifiers:all,statements:all';

Package altered.

demo@PDB1> alter package pkg compile body plscope_settings='identifiers:all,statements:all';

Package body altered.

demo@PDB1> alter package pkg2 compile plscope_settings='identifiers:all,statements:all';

Package altered.

demo@PDB1> alter package pkg2 compile body plscope_settings='identifiers:all,statements:all';

Package body altered.

demo@PDB1> col routine format a5
demo@PDB1> select t1.owner,t1.name,t1.type,t1.object_name,t1.object_type,t1.usage, t2.text,t2.routine
  2  from all_identifiers t1 ,
  3       ( select owner,name,type,line,text,
  4                  last_value( txt2 ignore nulls) over( partition by name,type order by line) as routine
  5              from (
  6              select owner,name,type,line,text,
  7                  case when line = 1 then name
  8                       when lower(trim(text)) like 'function%' then regexp_substr( lower(trim(text)) ,'[[:alnum:]]+',9,1)
  9                       when lower(trim(text)) like 'procedure%' then regexp_substr( lower(trim(text)) ,'[[:alnum:]]+',11,1)
 10                  end txt2
 11              from all_source
 12              where name in ('PKG','PKG2')
 13              and owner = user
 14                   ) ) t2
 15  where t1.signature =( select s1.signature
 16                                  from all_identifiers s1
 17                                  where owner = 'SCOTT'
 18                                  and type ='COLUMN'
 19                                  and object_name = 'EMP'
 20                                  and object_type ='TABLE'
 21                                  and name = 'DEPTNO' )
 22  and t1.object_name = t2.name(+)
 23  and t1.object_type = t2.type (+)
 24  and t1.owner = t2.owner (+)
 25  and t1.line = t2.line(+)
 26  order by 1,4     ;

OWNER NAME    TYPE    OBJEC OBJECT_TYPE     USAGE           TEXT                                                   ROUTI
----- ------- ------- ----- --------------- --------------- ------------------------------------------------------------ -----
DEMO  DEPTNO  COLUMN  PKG   PACKAGE         REFERENCE                        q in out scott.emp.deptno%type);              p
DEMO  DEPTNO  COLUMN  PKG   PACKAGE BODY    REFERENCE              select deptno into q from scott.emp;                    p
DEMO  DEPTNO  COLUMN  PKG   PACKAGE BODY    REFERENCE            select max(deptno)                                   p
DEMO  DEPTNO  COLUMN  PKG   PACKAGE BODY    REFERENCE                        q in out scott.emp.deptno%type) is            p
DEMO  DEPTNO  COLUMN  PKG2  PACKAGE BODY    REFERENCE                        q in out scott.emp.deptno%type) is            p2
DEMO  DEPTNO  COLUMN  PKG2  PACKAGE         REFERENCE                        q in out scott.emp.deptno%type);              p2
DEMO  DEPTNO  COLUMN  PKG2  PACKAGE BODY    REFERENCE            select max(deptno)                                   p2
DEMO  DEPTNO  COLUMN  PKG2  PACKAGE BODY    REFERENCE              select deptno into q from scott.emp;                    p2
SCOTT DEPTNO  COLUMN  EMP   TABLE           DECLARATION

9 rows selected.

demo@PDB1>

without plscope just scanning through ALL_SOURCE can return false positivies.
in this below package body procedure P3 was added with comments /* scott.deptno */
demo@PDB1>  create or replace
  2     package body PKG2 is
  3       type t is table of number;
  4
  5       g int;
  6
  7       function F1
  8       return number is
  9       begin
 10         null;
 11         null;
 12         null;
 13         return 1;
 14       end;
 15
 16       procedure P2(p in number,
 17                   q in out scott.emp.deptno%type) is
 18       begin
 19         for i in ( select * from scott.emp )
 20         loop
 21            null;
 22         end loop;
 23
 24         select deptno into q from scott.emp;
 25
 26       end;
 27
 28      procedure p3
 29      as
 30      begin
 31             for x in ( select * from dual /* scott.deptno */ )
 32             loop
 33                     null;
 34             end loop;
 35      end;
 36
 37     begin
 38       select max(deptno)
 39       into   g
 40       from scott.emp;
 41
 42     end;
 43     /

Package body created.

demo@PDB1> col routine format a5
demo@PDB1> col name format a5
demo@PDB1> col type format a15
demo@PDB1> col text format a60
demo@PDB1> with t as
  2     ( select
  3       case
  4         when line = 1 then name
  5         when ltrim(lower(text)) like 'function%' then regexp_substr(substr(ltrim(text),10),'[[:alnum:]]+')
  6         when ltrim(lower(text)) like 'procedure%' then regexp_substr(substr(ltrim(text),11),'[[:alnum:]]+')
  7       end tag,
  8       u.*
  9     from user_source u where name in ( 'PKG','PKG2')
 10     )
 11     select *
 12       from (
 13       select last_value(tag ignore nulls) over ( partition by name, type order by line ) as routine,
 14              name,
 15              type,
 16              text
 17       from   t
 18     )
 19     where  lower(text) like '%deptno%';

ROUTI NAME  TYPE            TEXT
----- ----- --------------- ------------------------------------------------------------
P     PKG   PACKAGE                          q in out scott.emp.deptno%type);
P2    PKG2  PACKAGE                          q in out scott.emp.deptno%type);
P     PKG   PACKAGE BODY                     q in out scott.emp.deptno%type) is
P     PKG   PACKAGE BODY           select deptno into q from scott.emp;
P     PKG   PACKAGE BODY         select max(deptno)
P2    PKG2  PACKAGE BODY                     q in out scott.emp.deptno%type) is
P2    PKG2  PACKAGE BODY           select deptno into q from scott.emp;
P2    PKG2  PACKAGE BODY                for x in ( select * from dual /* scott.deptno */ ) <<==== This shouldnot be returned.
P2    PKG2  PACKAGE BODY         select max(deptno)

9 rows selected.

demo@PDB1>

however using plscope that got eliminated.
demo@PDB1> alter package pkg2 compile body plscope_settings='identifiers:all,statements:all';

Package body altered.

demo@PDB1> select t1.owner,t1.name,t1.type,t1.object_name,t1.object_type,t1.usage, t2.text,t2.routine
  2  from all_identifiers t1 ,
  3       ( select owner,name,type,line,text,
  4                  last_value( txt2 ignore nulls) over( partition by name,type order by line) as routine
  5              from (
  6              select owner,name,type,line,text,
  7                  case when line = 1 then name
  8                       when lower(trim(text)) like 'function%' then regexp_substr( lower(trim(text)) ,'[[:alnum:]]+',9,1)
  9                       when lower(trim(text)) like 'procedure%' then regexp_substr( lower(trim(text)) ,'[[:alnum:]]+',11,1)
 10                  end txt2
 11              from all_source
 12              where name in ('PKG','PKG2')
 13              and owner = user
 14                   ) ) t2
 15  where t1.signature =( select s1.signature
 16                                  from all_identifiers s1
 17                                  where owner = 'SCOTT'
 18                                  and type ='COLUMN'
 19                                  and object_name = 'EMP'
 20                                  and object_type ='TABLE'
 21                                  and name = 'DEPTNO' )
 22  and t1.object_name = t2.name(+)
 23  and t1.object_type = t2.type (+)
 24  and t1.owner = t2.owner (+)
 25  and t1.line = t2.line(+)
 26  order by 1,4     ;

OWNER NAME       TYPE            OBJEC OBJECT_TYPE     USAGE           TEXT                                            ROUTI
----- ---------- --------------- ----- --------------- --------------- ------------------------------------------------------- -----
DEMO  DEPTNO     COLUMN          PKG   PACKAGE BODY    REFERENCE                        q in out scott.emp.deptno%type) is     p
DEMO  DEPTNO     COLUMN          PKG   PACKAGE BODY    REFERENCE              select deptno into q from scott.emp;             p
DEMO  DEPTNO     COLUMN          PKG   PACKAGE BODY    REFERENCE            select max(deptno)                         p
DEMO  DEPTNO     COLUMN          PKG   PACKAGE         REFERENCE                        q in out scott.emp.deptno%type);       p
DEMO  DEPTNO     COLUMN          PKG2  PACKAGE BODY    REFERENCE                        q in out scott.emp.deptno%type) is     p2
DEMO  DEPTNO     COLUMN          PKG2  PACKAGE         REFERENCE                        q in out scott.emp.deptno%type);       p2
DEMO  DEPTNO     COLUMN          PKG2  PACKAGE BODY    REFERENCE              select deptno into q from scott.emp;             p2
DEMO  DEPTNO     COLUMN          PKG2  PACKAGE BODY    REFERENCE            select max(deptno)                         p2
SCOTT DEPTNO     COLUMN          EMP   TABLE           DECLARATION

9 rows selected.

demo@PDB1>

Connor McDonald
August 05, 2019 - 9:06 am UTC

Nice input
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database