Finding out all procedures , functions in a tree like structure in sequence

>
My requirement when i pass master package (pack1) as input , i should get all chain of procedures in the dictionary upto any level
>
Assuming still that you want 'chain of procedures' and not procedures/functions within packages I have written a quick and dirty (meaning it could still have bugs) 'reverse' version of what utldeptree does: that is mine shows what objects a given object depends on. Here is the code - it uses the same table as utldeptree but creates a new procedure and view

-- the procedure
CREATE procedure deptree_fill_reverse (type char, schema char, name char) is
  obj_id number;
  obj_level number;
  obj_count number;
begin
  delete from deptree_temptab;
  commit;
  select object_id into obj_id from all_objects
    where owner        = upper(deptree_fill_reverse.schema)
    and   object_name  = upper(deptree_fill_reverse.name)
    and   object_type  = upper(deptree_fill_reverse.type);
  insert into deptree_temptab
    values(obj_id, 0, 0, deptree_seq.nextval);
  commit;
  obj_level := 0;
  loop
    select count(*) into obj_count from public_dependency pd
       where object_id in (select object_id
                           from deptree_temptab
                           where nest_level = obj_level);
    if obj_count = 0 then
       exit;
    end if;
    insert into deptree_temptab
      select pd.referenced_object_id, dt.seq#,
        obj_level + 1, deptree_seq.nextval
       from public_dependency pd, deptree_temptab dt
       where (dt.nest_level = obj_level) and
             (dt.object_id = pd.object_id);
    commit;
    obj_level := obj_level + 1;
  end loop;
exception
  when no_data_found then
    raise_application_error(-20000, 'ORU-10013: ' ||
      type || ' ' || schema || '.' || name || ' was not found.');
end;
-- the view
CREATE OR REPLACE VIEW IDEPTREE_REVERSE AS 
  select max(substr(lpad(' ', 6 * dtr.nest_level) || to_char(dtr.nest_level) || '. ' || uo.object_name, 1, 100)) object_name,  
max(uo.object_type) object_type, 
max(uo.status) object_status  
from   (select nest_level, object_id, referenced_object_id, rownum seq_num  
           from deptree_temptab   
           connect by prior seq# = referenced_object_id  
           start with nest_level = 0) dtr, 
       all_objects uo   
where dtr.object_id = uo.object_id  
group by dtr.seq_num  

You use it the same way you use the original. So using your sample packages as an example

execute deptree_fill_reverse('PACKAGE BODY', 'SCOTT', 'PACK2');

select * from ideptree_reverse;

OBJECT_NAME,OBJECT_TYPE,OBJECT_STATUS
0. PACK2,PACKAGE BODY,VALID
      1. PACK2,PACKAGE,VALID
      1. PACK4,PACKAGE,VALID

execute deptree_fill_reverse('PACKAGE BODY', 'SCOTT', 'PACK1');

select * from ideptree_reverse;

OBJECT_NAME,OBJECT_TYPE,OBJECT_STATUS
0. PACK1,PACKAGE BODY,VALID
      1. PACK1,PACKAGE,VALID
      1. PACK3,PACKAGE,VALID
      1. PACK2,PACKAGE,VALID

If you need procedure/function references within packages then in 11g you can use the new PL/SCOPE functionality and get the equivalent of a cross reference.

Seems a lot of developers don't yet know about this. See Chap 8 Using PL/Scope in the Advanced Appications Developer's Guide
http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_plscope.htm
>
8 Using PL/Scope

PL/Scope is a compiler-driven tool that collects data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assigment) and the location of each usage in the source code.

PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.

PL/Scope is intended for application developers, and will usually be used in the environment of a development database.
>
These are the topics and then include an example
>
Specifying Identifier Collection
PL/Scope Identifier Data for STANDARD and DBMS_STANDARD
How Much Space is PL/Scope Data Using?
Viewing PL/Scope Data
Identifier Types that PL/Scope Collects
Usages that PL/Scope Reports
Sample PL/Scope Session
>
You would then query the data using the new static data dictionary views that are created.

>
We would still have to figure out what package procedure that line belongs to.
>
The information needed for a full cross-reference is all in the views but it takes some getting used to to put the pieces together the way you want.

You only need to set things up once. I use some custom views and procedures (returning cursors) to make things simpler and generally produce an actual report (Crystal or Business Objects).

The trick is to have a full report that relies more on package/procedure/function names rather than line numbers since line numbers will change everywhere in an instant but the architectural location doesn't jump around. I use the report as a master reference to locate the proper 'module' and the real-time views to get to the actual line number if needed.

See the Sample PL/SCOPE session in the doc
>
5. Find all local variables:
SQL> SELECT a.NAME variable_name,
b.NAME context_name,
a.SIGNATURE
FROM USER_IDENTIFIERS a, USER_IDENTIFIERS b
WHERE a.USAGE_CONTEXT_ID = b.USAGE_ID
AND a.TYPE = 'VARIABLE'
AND a.USAGE = 'DECLARATION'
AND a.OBJECT_NAME = 'PACK1'
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.OBJECT_TYPE = b.OBJECT_TYPE
AND (b.TYPE = 'FUNCTION' or b.TYPE = 'PROCEDURE')
ORDER BY a.OBJECT_TYPE, a.USAGE_ID;

VARIABLE_NAME CONTEXT_NAME SIGNATURE
---------------------------------------------------------------
A F1 2268998957D20FACD63493B7A77BC55B
PR1 P1 174C2528B929953F4FE2A43DEBA2B5D0


6. Find all usages performed on the local variable A:
SQL> SELECT USAGE, USAGE_ID, OBJECT_NAME, OBJECT_TYPE
FROM USER_IDENTIFIERS
WHERE SIGNATURE='2268998957D20FACD63493B7A77BC55B'
ORDER BY OBJECT_TYPE, USAGE_ID;

USAGE USAGE_ID OBJECT_NAME OBJECT_TYPE
------------------------------------------------------
DECLARATION 4 PACK1 PACKAGE BODY
ASSIGNMENT 5 PACK1 PACKAGE BODY
REFERENCE 6 PACK1 PACKAGE BODY
The usages performed on the local identifier A are the identifier declaration (USAGE_ID 6), an assignment (USAGE_ID 8), and a reference (USAGE_ID 9).

7. From the declaration of the local identifier A, find its type:
SQL> SELECT a.NAME, a.TYPE
FROM USER_IDENTIFIERS a, USER_IDENTIFIERS b
WHERE a.USAGE = 'REFERENCE'
AND a.USAGE_CONTEXT_ID = b.USAGE_ID
AND b.USAGE = 'DECLARATION'
AND b.SIGNATURE = '2268998957D20FACD63493B7A77BC55B'
AND a.OBJECT_TYPE = b.OBJECT_TYPE
AND a.OBJECT_NAME = b.OBJECT_NAME;

NAME TYPE
--------------------------------
NUMBER DATATYPE STANDARD
Note:
This query produces the output shown only if your database has PL/Scope identifier data for the packages STANDARD and DBMS_STANDARD. For more information, see PL/Scope Identifier Data for STANDARD and DBMS_STANDARD.

8. Find out where the assignment to local identifier A occurred:
SQL> SELECT LINE, COL, OBJECT_NAME, OBJECT_TYPE
FROM USER_IDENTIFIERS
WHERE SIGNATURE='666CEC3A2180DF4013CEBE330A8CE747'
AND USAGE='ASSIGNMENT';

LINE COL OBJECT_NAME OBJECT_TYPE
------------------------------------------------
3 7 PACK1 PACKAGE BODY