This discussion is archived
11 Replies Latest reply: Sep 6, 2012 7:29 PM by rp0428 RSS

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

ora_1978 Explorer
Currently Being Moderated
Example:

Say i have packages

pack1 - Master package
pack1proc1 - calls pack2proc1
pack1proc2 - calls pack3proc1

pack2
pack2proc1 - calls pack4proc1
pack2proc2 - calls pack4proc2
pack2proc3


pack3
pack3proc1

packn
.
.
.
.
-----

The above is just an example. My requirement when i pass master package (pack1) as input , i should get all chain of procedures in the dictionary upto any level. I know we have database dictionary user_dependencies, deptree, ideptree. But not sure of how to use that. Please give a query to get the chain of procedures.

Table is not needed. Only procedures and functions.

thanks,
Vinodh
  • 1. Re: Finding out all procedures , functions in a tree like structure in sequence
    Solomon Yakobson Guru
    Currently Being Moderated
    Not possible. Oracle stores object level dependencies only. Table is data dictionary object, table column isn't. Same way package is data dictionary object and package procedure/function isn't. So same way as, for example, you can see what tables view is dependent on but you can't see what table columns view depends on, you can see what other packages package is dependent on, but you can't see what other package procedures package is dependent on. All you can is parse package source (using DBA/ALL/USER_SOURCE), but it is quite complex undertaking.

    SY.
  • 2. Re: Finding out all procedures , functions in a tree like structure in sequence
    Hoek Guru
    Currently Being Moderated
    Hi Solomon,
    Table is data dictionary object, table column isn't.
    How do you mean?
    Don't we have USER_TAB_COLS and COLS and other DD-views available for column information?
    Or am I missing something?
  • 3. Re: Finding out all procedures , functions in a tree like structure in sequence
    Solomon Yakobson Guru
    Currently Being Moderated
    Hoek wrote:
    Don't we have USER_TAB_COLS and COLS and other DD-views available for column information?
    Or am I missing something?
    Try finding column in DBA/ALL/USER_OBJECTS.

    SY.
  • 4. Re: Finding out all procedures , functions in a tree like structure in sequence
    rp0428 Guru
    Currently Being Moderated
    >
    I know we have database dictionary user_dependencies, deptree, ideptree. But not sure of how to use that. Please give a query to get the chain of procedures.
    >
    Then you should already know that the comments at the start of the 'utldtree.sql' script has instructions for how to use it and has sample queries to get the chain. One of those sample queries is for a package body.
    >
    Rem
    Rem $Header: utldtree.sql,v 1.2 1992/10/26 16:24:44 RKOOI Stab $
    Rem
    Rem Copyright (c) 1991 by Oracle Corporation
    Rem NAME
    Rem deptree.sql - Show objects recursively dependent on given object
    Rem DESCRIPTION
    Rem This procedure, view and temp table will allow you to see all
    Rem objects that are (recursively) dependent on the given object.
    Rem Note: you will only see objects for which you have permission.
    Rem Examples:
    Rem execute deptree_fill('procedure', 'scott', 'billing');
    Rem select * from deptree order by seq#;
    Rem
    Rem execute deptree_fill('table', 'scott', 'emp');
    Rem select * from deptree order by seq#;
    Rem
    Rem execute deptree_fill('package body', 'scott', 'accts_payable');
    Rem select * from deptree order by seq#;
    Rem
    Rem A prettier way to display this information than
    Rem          select * from deptree order by seq#;
    Rem     is
    Rem select * from ideptree;
    Rem This shows the dependency relationship via indenting. Notice
    Rem that no order by clause is needed with ideptree.
    Rem RETURNS
    Rem
    Rem NOTES
    Rem Run this script once for each schema that needs this utility.
    Rem
    Rem MODIFIED (MM/DD/YY)
    Rem rkooi 10/26/92 - owner -> schema for SQL2
    Rem glumpkin 10/20/92 - Renamed from DEPTREE.SQL
    Rem rkooi 09/02/92 - change ORU errors
    Rem rkooi 06/10/92 - add rae errors
    Rem rkooi 01/13/92 - update for sys vs. regular user
    Rem rkooi 01/10/92 - fix ideptree
    Rem rkooi 01/10/92 - Better formatting, add ideptree view
    Rem rkooi 12/02/91 - deal with cursors
    Rem rkooi 10/19/91 - Creation
    >
    Did you notice this line?
    >
    Rem Run this script once for each schema that needs this utility.
    >
    That is how you implement it.

    And did you see these lines?
    >
    Rem execute deptree_fill('package body', 'scott', 'accts_payable');
    Rem select * from deptree order by seq#;
    >
    That is how you use it.

    That is about as simple as you can get.

    If I were you I would use the 'prettier' method that is also shown by example.
    >
    Rem A prettier way to display this information than
    Rem          select * from deptree order by seq#;
    Rem     is
    Rem select * from ideptree;
    Rem This shows the dependency relationship via indenting. Notice
    Rem that no order by clause is needed with ideptree.
  • 5. Re: Finding out all procedures , functions in a tree like structure in sequence
    Solomon Yakobson Guru
    Currently Being Moderated
    rp0428 wrote:
    Then you should already know that the comments at the start of the 'utldtree.sql' script has instructions for how to use it and has sample queries to get the chain. One of those sample queries is for a package body.
    And? It also says:
    Rem This procedure, view and temp table will allow you to see all
    Rem <font size=3 color=red>objects</font> that are (recursively) dependent on the given <font size=3 color=red>object</font>.
    While OP wants package procedure depndency on other package procedures. And since package procedure is not an object, 'utldtree.sql' will not help here.

    SY.
  • 6. Re: Finding out all procedures , functions in a tree like structure in sequence
    rp0428 Guru
    Currently Being Moderated
    >
    While OP wants package procedure depndency on other package procedures.
    >
    Perhaps - but that isn't what OP said
    >
    My requirement when i pass master package (pack1) as input , i should get all chain of procedures in the dictionary upto any level.
    >
    OP said 'chain of procedures' - no mention of package procedures.
  • 7. Re: Finding out all procedures , functions in a tree like structure in sequence
    rp0428 Guru
    Currently Being Moderated
    >
    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.
  • 8. Re: Finding out all procedures , functions in a tree like structure in sequence
    Solomon Yakobson Guru
    Currently Being Moderated
    rp0428 wrote:
    Seems a lot of developers don't yet know about this. See Chap 8 Using PL/Scope in the Advanced Appications Developer's Guide
    I don't have experience with PL/Scope, but as fas as I understand, it can tell where (line) in package standalone procedure/package procedure was called. We would still have to figure out what package procedure that line belongs to.

    SY.
  • 9. Re: Finding out all procedures , functions in a tree like structure in sequence
    rp0428 Guru
    Currently Being Moderated
    >
    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

  • 10. Re: Finding out all procedures , functions in a tree like structure in sequence
    Solomon Yakobson Guru
    Currently Being Moderated
    Again, it shows where in an object identifier is used. That's would give you info what package procedure is called from a standalone procedure (which is, I agree, much better than DBA_DEPENDENCIES). However, and I again I don't have practical experience with PL/Scope, as far as I understand it will not give you info other way around - what procedures are called from package procedure. It can tell you what procedures and on what line are called by a package and we will have to use that line number to determine what part of the package it belongs.

    SY.
  • 11. Re: Finding out all procedures , functions in a tree like structure in sequence
    rp0428 Guru
    Currently Being Moderated
    See Example 8-3 USAGE_CONTEXT_ID and USAGE_ID. That example doesn't show line numbers (it could) but it shows the hierarchical query to show the indented nested call and reference chain.

    Bravid's reply in this thread from last year should give you a better example using packaged procedures.
    Re: how to find dependency procedure names within a  package

    ------ ADDED modified version of Bravid's query to show line numbers but couldn't get the result to format properly in the forum. Use this query after creating the packages in my reply above
    ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
    
    alter package pack4 compile
    
    alter package pack3 compile
    
    alter package pack2 compile
    
    alter package pack1 compile
    
    select * from user_identifiers
    
    WITH v AS (
      SELECT    Line,
                Col,
                INITCAP(NAME) Name,
                LOWER(TYPE)   Type,
                LOWER(USAGE)  Usage,
                USAGE_ID,
                USAGE_CONTEXT_ID, line lineno
        FROM USER_IDENTIFIERS
          WHERE Object_Name = 'PACK1'
            AND Object_Type = 'PACKAGE BODY'
    )
    SELECT RPAD(LPAD(' ', 2*(Level-1)) ||
                     Name, 20, '.')||' '||
                     RPAD(Type, 20)||
                     RPAD(Usage, 20)
                     IDENTIFIER_USAGE_CONTEXTS, lineno
      FROM v
      START WITH USAGE_CONTEXT_ID = 0
      CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID
      ORDER SIBLINGS BY Line, Col
    /