Zoom In on Your Code

Use PL/Scope to analyze your PL/SQL.

By Steven Feuerstein

September/October 2010

Since the first release of PL/SQL, Oracle has provided a set of data dictionary views that enable developers to obtain information about their PL/SQL program units. These views help us understand and analyze our code. Oracle Database 11g delivers an even more powerful analytical tool with PL/Scope, a tool invoked by the compiler to collect information about all the identifiers in your PL/SQL program unit. This information is then made available through a set of static data dictionary views. The views help us understand the different ways a variable is used in a declaration, definition, reference, call, or assignment as well as the location of each usage in the program unit’s source code.

With PL/Scope, developers can now perform code analysis of a quality and quantity never before possible. This column explores some of the ways you can use PL/Scope to better understand your application code base.

ALTER SESSION SET
plscope_settings='IDENTIFIERS:ALL'
/

There are two possible values: IDENTIFIERS:ALL or IDENTIFIERS:NONE. The default setting is IDENTIFIERS:NONE (identifier gathering is disabled). You can see the value of plscope_settings for any program unit with a query against one of the *_PLSQL_OBJECT_SETTINGS views.

You can obtain all the identifier information for a given program unit with the following query:

SELECT *
  FROM all_identifiers ai
 WHERE ai.owner = USER
   AND ai.object_type = '<program_type>'
   AND ai.object_name = '<program_name>'
ORDER BY line

• ASSIGNMENT. Indicates that on the line of code specified by this row in ALL_IDENTIFIERS, the value of the identifier can be modified. PL/Scope records an assignment when the identifier is used on the left of an assignment operator, appears in the INTO clause of a fetch, or is passed as an actual parameter of mode OUT or IN OUT.

• CALL. Indicates that a function or a procedure is called or that a call is made to a SQL statement.

• DECLARATION. Indicates that the identifier is declared.

• REFERENCE. Indicates that an identifier is used in the program without a change in its value. Examples include raising an exception, passing the identifier to an IN or IN OUT mode parameter of a subprogram or USING clause of EXECUTE IMMEDIATE, and using the identifier in a %TYPE declaration.

• DEFINITION. Tells the compiler how to implement or use a previously declared identifier. The following identifier types will have a DEFINITION row in ALL_IDENTIFIERS: FUNCTION, OBJECT, PACKAGE, PROCEDURE, TRIGGER, and EXCEPTION.

These usages make it easy to retrieve detailed information about program units.

If I want to see all the declared variables in a program unit, I can execute this query:

SELECT ai.object_name
     , ai.object_type
     , ai.name variable_name
     , ai.name context_name
  FROM all_identifiers ai
 WHERE ai.owner = USER AND
       ai.TYPE = 'VARIABLE' AND
       ai.usage = 'DECLARATION'
ORDER BY ai.object_name,
ai.object_type, ai.usage_id

Code Listing 1: Defining the plscope_demo package

CREATE OR REPLACE PACKAGE plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER
                         , param2 IN employees.last_name%TYPE
                          );
END plscope_demo;
/
CREATE OR REPLACE PACKAGE BODY plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER
                         , param2 IN employees.last_name%TYPE
                          )
   IS
      c_no_such   CONSTANT NUMBER := 100;
      l_local_variable     NUMBER;
   BEGIN
      IF param1_in > l_local_variable
      THEN
         DBMS_OUTPUT.put_line (param2);
      ELSE
         DBMS_OUTPUT.put_line (c_no_such);
      END IF;
   END my_procedure;
END plscope_demo;
/

You can then execute a hierarchical query, specifying the usage_context_id column as the parent of a row in the ALL_IDENTIFIERS view, to see the hierarchy of identifiers shown in Listing 2.

Code Listing 2: Querying against ALL_IDENTIFIERS view to see the hierarchy of identifiers

WITH plscope_hierarchy
        AS (SELECT line
                 , col
                 , name
                 , TYPE
                 , usage
                 , usage_id
                 , usage_context_id
              FROM all_identifiers
             WHERE     owner = USER
                   AND object_name = 'PLSCOPE_DEMO'
                   AND object_type = 'PACKAGE BODY')
SELECT    LPAD (' ', 3 * (LEVEL - 1))
       || TYPE
       || ' '
       || name
       || ' ('
       || usage
       || ')'
          identifier_hierarchy
  FROM plscope_hierarchy
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
ORDER SIBLINGS BY line, col
PACKAGE PLSCOPE_DEMO (DEFINITION)
   PROCEDURE MY_PROCEDURE (DEFINITION)
      FORMAL IN PARAM1_IN (DECLARATION)
         SUBTYPE INTEGER (REFERENCE)
      FORMAL IN PARAM2 (DECLARATION)
      CONSTANT C_NO_SUCH (DECLARATION)
         CONSTANT C_NO_SUCH (ASSIGNMENT)
         NUMBER DATATYPE NUMBER (REFERENCE)
      VARIABLE L_LOCAL_VARIABLE (DECLARATION)
         NUMBER DATATYPE NUMBER (REFERENCE)
      FORMAL IN PARAM1_IN (REFERENCE)
      VARIABLE L_LOCAL_VARIABLE (REFERENCE)

PROCEDURE plscope_demo_proc
IS
  plscope_demo_proc   NUMBER;
BEGIN
  DECLARE
    plscope_demo_proc   EXCEPTION;
  BEGIN
    RAISE plscope_demo_proc;
  END;
  plscope_demo_proc := 1;
END plscope_demo_proc;

This is very confusing, but it is certainly valid PL/SQL code. And it would be very difficult to distinguish between usages of the same name with a search through the ALL_SOURCE view.

PL/Scope makes it easy to distinguish between different identifiers of the same name through its ALL_IDENTIFIERS SIGNATURE column. Each distinct identifier has its own signature value, a 32-byte string that is unique within and across program units. (USAGE_ID values, in contrast, are unique only within a program unit.) To demonstrate this, I run the query in Listing 3.

Code Listing 3: Distinguishing between identifiers with the same name

SELECT line
     , TYPE
     , usage
     , signature
  FROM all_identifiers
 WHERE     owner = USER
       AND object_name = 'PLSCOPE_DEMO_PROC'
       AND name = 'PLSCOPE_DEMO_PROC'
ORDER BY line
LINE  TYPE       USAGE        SIGNATURE
1     PROCEDURE  DEFINITION   51B3B5C5404AE8307DA49F42E0279915
1     PROCEDURE  DECLARATION  51B3B5C5404AE8307DA49F42E0279915
3     VARIABLE   DECLARATION  021B597943C0F31AD3938ACDAAF276F3
6     EXCEPTION  DECLARATION  98E0183501FB350439CA44E3E511F60C
8     EXCEPTION  REFERENCE    98E0183501FB350439CA44E3E511F60C
11    VARIABLE   ASSIGNMENT   021B597943C0F31AD3938ACDAAF276F3

Note that each signature appears twice in the output. For the program unit itself, there are DEFINITION and DECLARATION rows. For a variable, constant, exception, and so on, there will be a DECLARATION row and then other rows with the same signature, indicating lines on which that particular identifier is used in some way.

Using the signature, I can easily filter my query to find only those lines of code that use a particular identifier, regardless of possible multiple uses of the same name. The query in Listing 4, for example, asks to see all assignments and references to the PLSCOPE_DEMO_PROC variable.

Code Listing 4: Querying all assignments and references to the PLSCOPE_DEMO_PROC variable

SELECT usg.line
     , usg.TYPE
     , usg.usage
  FROM all_identifiers dcl,
      all_identifiers usg
 WHERE
    dcl.owner = USER
 AND dcl.object_name = 'PLSCOPE_DEMO_PROC'
 AND dcl.name = 'PLSCOPE_DEMO_PROC'
 and dcl.usage = 'DECLARATION'
 and dcl.type = 'VARIABLE'
 and usg.signature = dcl.signature
 and usg.usage <> 'DECLARATION'
ORDER BY line

Now let’s take a look at how we can use PL/Scope to

• Validate naming conventions
• Identify violations of best practices

For example, I follow these conventions when naming my parameters:

IN parameters: end with _in
OUT parameters: end with _out
IN OUT parameters: end with _io

To verify that a program unit conforms to these rules, I will look for rows with a usage of DECLARATION and a type of FORMAL IN, FORMAL OUT, or FORMAL IN OUT.

Suppose I declare the package specification in Listing 5.

Code Listing 5: Creating the package specification for plscope_demo

CREATE OR REPLACE PACKAGE plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER, param2 IN DATE);
   FUNCTION my_function (param1    IN INTEGER
                       , in_param2 IN DATE
                       , param3_in IN employees.last_name%TYPE
                        )
      RETURN VARCHAR2;
END plscope_demo;

I can then use the query in Listing 6 to identify violations of my parameter naming convention in PLSCOPE_DEMO.

Code Listing 6: Querying to find naming violations

SELECT prog.name subprogram, parm.name parameter
  FROM all_identifiers parm, all_identifiers prog
 WHERE     parm.owner = USER
       AND parm.object_name = 'PLSCOPE_DEMO'
       AND parm.object_type = 'PACKAGE'
       AND prog.owner = parm.owner
       AND prog.object_name = parm.object_name
       AND prog.object_type = parm.object_type
       AND parm.usage_context_id = prog.usage_id
       AND parm.TYPE IN ('FORMAL IN', 'FORMAL IN OUT', 'FORMAL OUT')
       AND parm.usage = 'DECLARATION'
       AND ( (parm.TYPE = 'FORMAL IN'
              AND LOWER (parm.name) NOT LIKE '%\_in' ESCAPE '\')
            OR (parm.TYPE = 'FORMAL OUT'
                AND LOWER (parm.name) NOT LIKE '%\_out' ESCAPE '\')
            OR (parm.TYPE = 'FORMAL IN OUT'
                AND LOWER (parm.name) NOT LIKE '%\_io' ESCAPE '\'))
ORDER BY prog.name, parm.name

Note the use of usage_context_id to find the name of the subprogram that “owns” the parameter.

Variables declared in the specification of a package. This is always dangerous. If the variable is declared in the package specification, any schema with execute authority on the package can directly read and write the variable.

Exception declared but not raised in a program unit. Programmers can declare their own exception in a block of code. However, if that exception is not raised in that same block, it cannot be handled in any outer block.

To check for variables declared in the package specification, I need to identify variables declared in the package specification. That’s an easy one:

SELECT object_name, name, line
  FROM all_identifiers ai
 WHERE ai.owner = USER
     AND ai.TYPE = 'VARIABLE'
     AND ai.usage = 'DECLARATION'
     AND ai.object_type = 'PACKAGE'

To check for exceptions declared but not raised, it is first important to understand the kinds of usages that can occur in a program unit for an exception. Consider the following procedure definition:

PROCEDURE plscope_demo_proc
IS
   e_bad_data   EXCEPTION;
   PRAGMA EXCEPTION_INIT (
                e_bad_data, -20900);
BEGIN
   RAISE e_bad_data;
EXCEPTION
   WHEN e_bad_data
   THEN
      log_error ();
END plscope_demo_proc;
Let’s see what PL/Scope has to say about the e_bad_data identifier:
SELECT line
     , TYPE
     , usage
  FROM all_identifiers
 WHERE owner = USER
   AND object_name =
              'PLSCOPE_DEMO_PROC'
   AND name = 'E_BAD_DATA'
ORDER BY line
/
LINE  TYPE       USAGE
-----  ------------  ---------------
3     EXCEPTION  DECLARATION
4     EXCEPTION  ASSIGNMENT
6     EXCEPTION  REFERENCE
8     EXCEPTION  REFERENCE   

From this I can deduce that the EXCEPTION_INIT statement is treated as an assignment to the exception (assigning an error number to that named exception) and that both the RAISE statement and the WHEN clause are considered references.

I can, therefore, use the query in Listing 7 to identify all subprograms in which an exception is declared but not referenced.

Code Listing 7: Querying all subprograms in which an exception is declared but not referenced

WITH subprograms_with_exception
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers has_exc
             WHERE     has_exc.owner = USER
                   AND has_exc.usage = 'DECLARATION'
                   AND has_exc.TYPE = 'EXCEPTION'),
     subprograms_with_raise_handle
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers with_rh
             WHERE     with_rh.owner = USER
                   AND with_rh.usage = 'REFERENCE'
                   AND with_rh.TYPE = 'EXCEPTION')
SELECT *
  FROM subprograms_with_exception
MINUS
SELECT *
  FROM subprograms_with_raise_handle

I have encapsulated many of the queries shown in this article into a single helper package, available at oracle.com/technology/oramag/10-sep/o50plsql.zip.