Oracle -trace and report on all function names and procedure names called in all a package

Question

I am using Oracle 11.2 and want to see the names of functions and procedures called within a package. I have tried DBMS.TRACE but i cannot see the function and procedure names- only sql or package name. How can i do this ?

Answer 1

If you want to list all procedures/functions that belong to a package, you should use the dictionary view ALL_PROCEDURES.

Example

 SQL> create or replace package cpl_rep.my_test_pkg
 as
 procedure testprc ( p1 number );
 function testfun ( p1 number ) return number;
 end;
 /

Package created.

SQL> create or replace package body cpl_rep.my_test_pkg
as
procedure testprc ( p1 number )
is
begin
null;
end testprc;
function testfun ( p1 number ) return number
is
begin
return null;
end testfun;
end;
/

Package body created.

SQL> desc all_procedures

SQL> desc all_procedures
 Name                                                                          Null?    Type
 ----------------------------------------------------------------------------- -------- ----------------------------------------------------
 OWNER                                                                                  VARCHAR2(128)
 OBJECT_NAME                                                                            VARCHAR2(128)
 PROCEDURE_NAME                                                                         VARCHAR2(128)
 OBJECT_ID                                                                              NUMBER
 SUBPROGRAM_ID                                                                          NUMBER
 OVERLOAD                                                                               VARCHAR2(40)
 OBJECT_TYPE                                                                            VARCHAR2(13)
 AGGREGATE                                                                              VARCHAR2(3)
 PIPELINED                                                                              VARCHAR2(3)
 IMPLTYPEOWNER                                                                          VARCHAR2(128)
 IMPLTYPENAME                                                                           VARCHAR2(128)
 PARALLEL                                                                               VARCHAR2(3)
 INTERFACE                                                                              VARCHAR2(3)
 DETERMINISTIC                                                                          VARCHAR2(3)
 AUTHID                                                                                 VARCHAR2(12)
 RESULT_CACHE                                                                           VARCHAR2(3)
 ORIGIN_CON_ID                                                                          NUMBER

SQL> select owner,object_name,procedure_name,object_id,subprogram_id, object_type
     from all_procedures where owner = 'CPL_REP' and object_name = 'MY_TEST_PKG' ;

OWNER                OBJECT_NAME                    PROCEDURE_NAME                  OBJECT_ID SUBPROGRAM_ID OBJECT_TYPE
-------------------- ------------------------------ ------------------------------ ---------- ------------- -------------
CPL_REP              MY_TEST_PKG                    TESTFUN                           1582551             2 PACKAGE
CPL_REP              MY_TEST_PKG                    TESTPRC                           1582551             1 PACKAGE
CPL_REP              MY_TEST_PKG                                                      1582551             0 PACKAGE

However, Oracle does not have a view called ALL_FUNCTIONS, therefore all the objects that belong to a package will always appear inside ALL_PROCEDURES.

Answer 2

I think DBMS_PROFILER is what you want - I think it still exists 11g.

I haven't used it since Oracle 8i, but from memory you wrap your code around calls to the package and then query plsql_profiler_data.

E.g:

BEGIN
   dbms_profiler.start_profiler ('MYTEST');
   MYPACKAGE.MYPROC;
   dbms_profiler.flush_data();
   bms_profiler.stop_profiler();
END;

Now query PLSQL_PROFILER_DATA. This gives source unit and line numbers, so you need to join to ALL_SOURCE and maybe ALL_PROCEDURES to get the actual code.