This discussion is archived
14 Replies Latest reply: Nov 21, 2012 12:22 AM by BluShadow RSS

Identify variables in a package

user12057782 Newbie
Currently Being Moderated
Hi
Is there a way to identify the variables used in packages by using data dictionary?
Plz help
  • 1. Re: Identify variables in a package
    Nimish Garg Guru
    Currently Being Moderated
    try user_source data dictionary
  • 2. Re: Identify variables in a package
    avish16 Explorer
    Currently Being Moderated
    try this -

    select * from dba_source
    where owner like '' --<owner name>
    and type ='' --<object type>
    and name ='' --<object name>
  • 3. Re: Identify variables in a package
    Manik Expert
    Currently Being Moderated
    you have to include text column for search criteria.
    SELECT *
      FROM dba_source
     WHERE TYPE = 'PACKAGE' AND OWNER = 'username' AND UPPER (text) LIKE '%ABD%';  -- replace ABD with your variable name or code from the package you are searching
    Cheers,
    Manik.
  • 4. Re: Identify variables in a package
    BluShadow Guru Moderator
    Currently Being Moderated
    user12057782 wrote:
    Hi
    Is there a way to identify the variables used in packages by using data dictionary?
    Plz help
    Your question is ambiguous.

    What do you mean by "variables used in" ?

    e.g.

    do you want to identify which variables are declared but not used at run-time?
    do you want to identify which variables are actually used at run-time?
    do you just want to identify all delcared variables in a package?

    The data dictionary stores objects, so the whole package itself is considered a single object. There is no seperate entries created for all the variables in a package as they are instantiated in memory at run time, not at compile time.

    Please explain what your requirement is exactly, including your database version, an example of some code/data, what output you are expecting, and what logic you are wanting.

    {message:id=9360002}
  • 5. Re: Identify variables in a package
    971895 Journeyer
    Currently Being Moderated
    we can't find the variable .there is no options in oracle.
    every one posted use user_source it will give the arguments of the procedure or function or package.
  • 6. Re: Identify variables in a package
    BluShadow Guru Moderator
    Currently Being Moderated
    968892 wrote:
    we can't find the variable .there is no options in oracle.
    every one posted use user_source it will give the arguments of the procedure or function or package.
    No, it'll give the source code for the whole package, including arguments, variables, procedures, functions etc. etc.
  • 7. Re: Identify variables in a package
    971895 Journeyer
    Currently Being Moderated
    That is know.. we can't select variables what it's declare in proc,function and package...
  • 8. Re: Identify variables in a package
    973492 Newbie
    Currently Being Moderated
    hi BlueShadow,

    can u explain below queries and how do we obtain the results of them/

    how do you want to identify which variables are declared but not used at run-time?
    how do you want to identify which variables are actually used at run-time?
    how do you just want to identify all delcared variables in a package?


    Thanks.
  • 9. Re: Identify variables in a package
    BluShadow Guru Moderator
    Currently Being Moderated
    Well actually you can, if the session settings are set appropriately before compilation...




    SQL> ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'
      2  /
    
    Session altered.
    
    SQL>
    SQL> create or replace package test_pkg as
      2    a number;
      3    procedure xyz(b in number, c in varchar2);
      4  end;
      5  /
    
    Package created.
    
    SQL>
    SQL> create or replace package body test_pkg as
      2    procedure xxx(x number) is
      3      a number;
      4    begin
      5      a := a * 2;
      6    end;
      7    procedure xyz(b in number, c in varchar2) is
      8      l_b number := b;
      9    begin
     10      xxx(l_b);
     11    end;
     12  end;
     13  /
    
    Package body created.
    
    SQL>
    SQL> col plscope_settings format a30
    SQL>
    SQL> select name, type, plscope_settings
      2  from   user_plsql_object_settings
      3  where  name='TEST_PKG'
      4  and    type in ('PACKAGE','PACKAGE BODY')
      5  /
    
    NAME                           TYPE         PLSCOPE_SETTINGS
    ------------------------------ ------------ ------------------------------
    TEST_PKG                       PACKAGE      IDENTIFIERS:ALL
    TEST_PKG                       PACKAGE BODY IDENTIFIERS:ALL
    
    SQL>
    SQL> select object_name, object_type, name, signature, type, line, col, usage, usage_id, usage_context_id
      2  from   user_identifiers
      3  where  name like '%'
      4  and    usage='DECLARATION'
      5  order by object_name, object_type, usage_id
      6  /
    
    OBJECT_NAME                    OBJECT_TYPE   NAME                           SIGNATURE                        TYPE               LINE       COL        USAGE       USAGE_ID   USAGE_CONTEXT_ID
    ------------------------------ ------------- ------------------------------ -------------------------------- ------------------ ---------- ---------- ----------- ---------- ----------------
    TEST_PKG                       PACKAGE       TEST_PKG                       9306828BF5331E358BA8BAFAAAE82FAA PACKAGE                     1          9 DECLARATION          1                0
    TEST_PKG                       PACKAGE       A                              3C51449E543716C2B4245DA30B60D2D7 VARIABLE                    2          3 DECLARATION          2                1
    TEST_PKG                       PACKAGE       XYZ                            7B68CB02088068A02EAA21681B653E0D PROCEDURE                   3         13 DECLARATION          4                1
    TEST_PKG                       PACKAGE       B                              339534609DE9CD72895D627E83397DB9 FORMAL IN                   3         17 DECLARATION          5                4
    TEST_PKG                       PACKAGE       C                              CC8D5978DD3B5533883E260B05AF4906 FORMAL IN                   3         30 DECLARATION          7                4
    TEST_PKG                       PACKAGE BODY  XXX                            52AC818080DFE37A4D310F63AB487A3C PROCEDURE                   2         13 DECLARATION          2                1
    TEST_PKG                       PACKAGE BODY  X                              2CE601BB0B7BB274F475F21CC0C9FE9F FORMAL IN                   2         17 DECLARATION          4                3
    TEST_PKG                       PACKAGE BODY  A                              975D65648BA09BA90C3AA4F5827C78E0 VARIABLE                    3          5 DECLARATION          6                3
    TEST_PKG                       PACKAGE BODY  B                              0AC648EA8BCCC16C6D51FEB2D83F1669 FORMAL IN                   7         17 DECLARATION         11               10
    TEST_PKG                       PACKAGE BODY  C                              DAB9D00A711403FDCF18E6516C701005 FORMAL IN                   7         30 DECLARATION         13               10
    TEST_PKG                       PACKAGE BODY  L_B                            2A30BE437AA9E02638708DBA63C5D46D VARIABLE                    8          5 DECLARATION         15               10
    
    11 rows selected.
    
    SQL>
    For further reference, see PL/SQL Scope in the documentation: http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_plscope.htm
  • 10. Re: Identify variables in a package
    BluShadow Guru Moderator
    Currently Being Moderated
    970489 wrote:
    hi BlueShadow,

    can u explain below queries and how do we obtain the results of them/

    how do you want to identify which variables are declared but not used at run-time?
    how do you want to identify which variables are actually used at run-time?
    For the above two, it's not easy to determine which variables are actually used at run time, especially if people create dynamic code within the PL/SQL. To the best of my knowledge (without digging more into the manuals) I'm not sure there's anything to store any sort of dependency for each of the variables as it gets compiled.
    Edit to Add: Looking at that link I provided to the manual, it may be possible to see where variables are used and assigned values etc. so determining which variables are used and not used should be a simple process of elimination. I'll let you have a go for yourself and figure that one out.
    how do you just want to identify all delcared variables in a package?
    I just showed that in my previous post.

    Edited by: BluShadow on 20-Nov-2012 10:53
  • 11. Re: Identify variables in a package
    BluShadow Guru Moderator
    Currently Being Moderated
    Additional example... to see all the stuff going on...
    SQL> select object_name, object_type, name, signature, type, line, col, usage, usage_id, usage_context_id
      2  from   user_identifiers
      3  where  name like '%'
      4  --and    usage='DECLARATION'
      5  order by object_name, object_type, usage_id
      6  /
    
    OBJECT_NAME                    OBJECT_TYPE   NAME                           SIGNATURE                        TYPE               LINE       COL        USAGE       USAGE_ID   USAGE_CONTEXT_ID
    ------------------------------ ------------- ------------------------------ -------------------------------- ------------------ ---------- ---------- ----------- ---------- ----------------
    TEST_PKG                       PACKAGE       TEST_PKG                       9306828BF5331E358BA8BAFAAAE82FAA PACKAGE                     1          9 DECLARATION          1                0
    TEST_PKG                       PACKAGE       A                              3C51449E543716C2B4245DA30B60D2D7 VARIABLE                    2          3 DECLARATION          2                1
    TEST_PKG                       PACKAGE       NUMBER                         47BFC756469F1D97B6C84EF73A9C5D48 NUMBER DATATYPE             2          5 REFERENCE            3                2
    TEST_PKG                       PACKAGE       XYZ                            7B68CB02088068A02EAA21681B653E0D PROCEDURE                   3         13 DECLARATION          4                1
    TEST_PKG                       PACKAGE       B                              339534609DE9CD72895D627E83397DB9 FORMAL IN                   3         17 DECLARATION          5                4
    TEST_PKG                       PACKAGE       NUMBER                         47BFC756469F1D97B6C84EF73A9C5D48 NUMBER DATATYPE             3         22 REFERENCE            6                5
    TEST_PKG                       PACKAGE       C                              CC8D5978DD3B5533883E260B05AF4906 FORMAL IN                   3         30 DECLARATION          7                4
    TEST_PKG                       PACKAGE       VARCHAR2                       FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE          3         35 REFERENCE            8                7
    TEST_PKG                       PACKAGE BODY  TEST_PKG                       9306828BF5331E358BA8BAFAAAE82FAA PACKAGE                     1         14 DEFINITION           1                0
    TEST_PKG                       PACKAGE BODY  XXX                            52AC818080DFE37A4D310F63AB487A3C PROCEDURE                   2         13 DECLARATION          2                1
    TEST_PKG                       PACKAGE BODY  XXX                            52AC818080DFE37A4D310F63AB487A3C PROCEDURE                   2         13 DEFINITION           3                2
    TEST_PKG                       PACKAGE BODY  X                              2CE601BB0B7BB274F475F21CC0C9FE9F FORMAL IN                   2         17 DECLARATION          4                3
    TEST_PKG                       PACKAGE BODY  NUMBER                         47BFC756469F1D97B6C84EF73A9C5D48 NUMBER DATATYPE             2         19 REFERENCE            5                4
    TEST_PKG                       PACKAGE BODY  A                              975D65648BA09BA90C3AA4F5827C78E0 VARIABLE                    3          5 DECLARATION          6                3
    TEST_PKG                       PACKAGE BODY  NUMBER                         47BFC756469F1D97B6C84EF73A9C5D48 NUMBER DATATYPE             3          7 REFERENCE            7                6
    TEST_PKG                       PACKAGE BODY  A                              975D65648BA09BA90C3AA4F5827C78E0 VARIABLE                    5          5 ASSIGNMENT           8                3
    TEST_PKG                       PACKAGE BODY  A                              975D65648BA09BA90C3AA4F5827C78E0 VARIABLE                    5         10 REFERENCE            9                8
    TEST_PKG                       PACKAGE BODY  XYZ                            7B68CB02088068A02EAA21681B653E0D PROCEDURE                   7         13 DEFINITION          10                1
    TEST_PKG                       PACKAGE BODY  B                              0AC648EA8BCCC16C6D51FEB2D83F1669 FORMAL IN                   7         17 DECLARATION         11               10
    TEST_PKG                       PACKAGE BODY  NUMBER                         47BFC756469F1D97B6C84EF73A9C5D48 NUMBER DATATYPE             7         22 REFERENCE           12               11
    TEST_PKG                       PACKAGE BODY  C                              DAB9D00A711403FDCF18E6516C701005 FORMAL IN                   7         30 DECLARATION         13               10
    TEST_PKG                       PACKAGE BODY  VARCHAR2                       FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE          7         35 REFERENCE           14               13
    TEST_PKG                       PACKAGE BODY  L_B                            2A30BE437AA9E02638708DBA63C5D46D VARIABLE                    8          5 DECLARATION         15               10
    TEST_PKG                       PACKAGE BODY  NUMBER                         47BFC756469F1D97B6C84EF73A9C5D48 NUMBER DATATYPE             8          9 REFERENCE           16               15
    TEST_PKG                       PACKAGE BODY  L_B                            2A30BE437AA9E02638708DBA63C5D46D VARIABLE                    8          5 ASSIGNMENT          17               15
    TEST_PKG                       PACKAGE BODY  B                              0AC648EA8BCCC16C6D51FEB2D83F1669 FORMAL IN                   8         19 REFERENCE           18               17
    TEST_PKG                       PACKAGE BODY  XXX                            52AC818080DFE37A4D310F63AB487A3C PROCEDURE                  10          5 CALL                19               10
    TEST_PKG                       PACKAGE BODY  L_B                            2A30BE437AA9E02638708DBA63C5D46D VARIABLE                   10          9 REFERENCE           20               19
    
    28 rows selected.
    Here you can see which declaration of a variable or parameter is being references by the assignments etc. by looking at the usage_id and usage_context_id

    There we go... I've learnt something new myself today... and very quickly and simply by just looking in the documentation.
  • 12. Re: Identify variables in a package
    Manik Expert
    Currently Being Moderated
    super Blu... u rock :)
  • 13. Re: Identify variables in a package
    rp0428 Guru
    Currently Being Moderated
    >
    Edit to Add: Looking at that link I provided to the manual, it may be possible to see where variables are used and assigned values etc. so determining which variables are used and not used should be a simple process of elimination.
    >
    Clearly you can't determine statically what variables are used and assigned values at runtime.

    Any code having a simple IF statement may, or may not execute the code in the IF block (or an ELSE block if one exists). And that code may 'use' a variable IF IT WERE EXECUTED. But you will not necessarily be able to determine statically that the block WILL be executed on any given pass through the code.

    The value of the IF condition may be determined by data values that don't even exist at the time you perform your static test.
  • 14. Re: Identify variables in a package
    BluShadow Guru Moderator
    Currently Being Moderated
    rp0428 wrote:
    >
    Edit to Add: Looking at that link I provided to the manual, it may be possible to see where variables are used and assigned values etc. so determining which variables are used and not used should be a simple process of elimination.
    >
    Clearly you can't determine statically what variables are used and assigned values at runtime.

    Any code having a simple IF statement may, or may not execute the code in the IF block (or an ELSE block if one exists). And that code may 'use' a variable IF IT WERE EXECUTED. But you will not necessarily be able to determine statically that the block WILL be executed on any given pass through the code.

    The value of the IF condition may be determined by data values that don't even exist at the time you perform your static test.
    Absolutely, but you could certainly use it to determine which declarations are completely redundant in a piece of code that's compiled. As you say, whether the assignments ever get used at run-time is another matter and almost impossible to determine. ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points