3 Replies Latest reply on Oct 4, 2013 1:39 PM by BluShadow

    PL/Scope

    Igor S.

      Hello experts,

       

      I am trying to understand what PL/Scope does and how could it help me with programming. From what I understand it looks for all PL/SQL identifiers (variables, loops, in/out parameters etc...) and logs them in a table.

       

      What extra information can I get from turning PL/Scope on? and how dangerous can this be since I saw a warning on some site that you might need to restart the database or drop it:

       

      Oracle PL/SCOPE

      Do not to try this with any database you are not prepared to drop

       

      Best regards,

      Igor

        • 1. Re: PL/Scope
          BluShadow

          Plenty of examples in the documentation:

           

          Using PL/Scope

           

          Example:

           

          ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'
          /

           

          create or replace package test_pkg as
            a number;
            procedure xyz(b in number, c in varchar2);
          end;
          /

           

          create or replace package body test_pkg as
            procedure xxx(x number) is
              a number;
            begin
              a := a * 2;
            end;
            procedure xyz(b in number, c in varchar2) is
              l_b number := b;
            begin
              xxx(l_b);
            end;
          end;
          /

           

          col plscope_settings format a30

          select name, type, plscope_settings
          from   user_plsql_object_settings
          where  name='TEST_PKG'
          and    type in ('PACKAGE','PACKAGE BODY')
          /

           

          select object_name, object_type, name, signature, type, line, col, usage, usage_id, usage_context_id
          from   user_identifiers
          where  name like '%'
          and    usage='DECLARATION'
          order by object_name, object_type, usage_id
          /


          select object_name, object_type, name, signature, type, line, col, usage, usage_id, usage_context_id
          from   user_identifiers
          where  name like '%'
          --and    usage='DECLARATION'
          order by object_name, object_type, usage_id
          /


          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

           


          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.

           

          Not sure about the need to drop a database though.  :-/

          1 person found this helpful
          • 2. Re: PL/Scope
            padders

            Daniel Morgan (of Morgan's Library) appears to be talking about recompiling the Oracle built-in packages (e.g. sys.standard) with PL/Scope switched on and how Oracle allegedly recommended this at some point.

             

            I haven't tried the above but I use it for my own packages and I am not aware of any issues with using it, however if you wanted to be 100% sure you should check on Metalink.

            1 person found this helpful
            • 3. Re: PL/Scope
              BluShadow

              IgorS. wrote:

               

              Hello experts,

               

              I am trying to understand what PL/Scope does and how could it help me with programming. From what I understand it looks for all PL/SQL identifiers (variables, loops, in/out parameters etc...) and logs them in a table.

               

              What extra information can I get from turning PL/Scope on? and how dangerous can this be since I saw a warning on some site that you might need to restart the database or drop it:

               

              Oracle PL/SCOPE

              Do not to try this with any database you are not prepared to drop

               

              Best regards,

              Igor

               

              Just looked, and that Warning is about recompiling the standard package from within SYS schema with the PL/SCOPE identifiers:all option.

              I'd agree, that doesn't sound like a good idea.

               

              Read the documentation and use it for your own code, not for SYS stuff.