TECHNOLOGY: PL/SQL

As Published In
Oracle Magazine
September/October 2010

  

Zoom In on Your Code

By Steven Feuerstein Oracle ACE Director

 

Use PL/Scope to analyze your PL/SQL.

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. 

Enabling PL/Scope

To use PL/Scope, you must first set the PL/SQL compiler to analyze the identifiers of your program when it is compiled. You do this by changing the value of the plscope_settings initialization parameter at the session level:

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. 

The ALL_IDENTIFIERS View

When PL/Scope is enabled and your program unit is compiled, the ALL_IDENTIFIERS view is populated with information about all the identifiers found in that unit. (ALL_IDENTIFIERS displays information about the identifiers in the stored objects accessible to the current user.) Table 1 presents a brief description of key columns in the ALL_IDENTIFIERS view.

 

Name Significance
OWNER The owner of the program unit containing the identifier
NAME The name of the identifier
TYPE The type of the identifier, such as FORALL OUT (an out argument), CONSTANT, PACKAGE, or RECORD
SIGNATURE A unique string for each distinct identifier, across all program units, useful for distinguishing between different identifiers that happen to have the same name
OBJECT_NAME The name of the program unit containing the identifier
OBJECT_TYPE The type of the program unit containing the identifier, such as PACKAGE, TRIGGER, or PROCEDURE
USAGE The type of usage of the identifier (such as a declaration or an assignment)
USAGE_ID A sequentially generated integer value for an identifier, unique within its program unit
USAGE_CONTEXT_ID A foreign key reflexive back to USAGE_ID; in essence, the parent of this identifier appearance (for example, the context of a variable’s declaration is the name of the subprogram in which the variable is declared)
LINE The number of the line on which the identifier appears
COL The column position in the line at which the identifier appears

Table 1: ALL_IDENTIFIERS view columns and descriptions

 

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

  

Usages Tracked by PL/Scope

PL/Scope stores detailed information about how each identifier is used in your code. Each row in the ALL_IDENTIFIERS view refers to a specific line of code in the specified program unit. These are the usages tracked by PL/Scope in the USAGE column of ALL_IDENTIFIERS:

  • 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

  

Using Usage IDs to Understand Identifier Hierarchy

A package may contain one or more subprograms; a subprogram may have one or more parameters. You can use PL/Scope to expose this hierarchy. Suppose, for example, you have defined a package as shown in Listing 1.

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)

  

Using a Signature to Differentiate Between Identifiers

It’s always been possible to search for occurrences of strings in source code by querying the contents of the ALL_SOURCE view. It is also possible to use the same name for different elements in your code. You can, for example, use the name of a subprogram for the name of a variable within that subprogram. Here’s an example:

  

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  

 

Validate Naming Conventions

 

With PL/Scope, I can clearly and easily distinguish between types of identifiers (variables, constants, parameters, and so on). I can, therefore, also check to see if the names of identifiers for each of these types conform to my naming conventions.

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. 

Identify Violations of Best Practices

PL/Scope offers a nice way to complement the compile-time warnings framework of PL/SQL with additional best-practice rules of your own. Here are two scenarios in which you can check with PL/Scope that would be difficult to do with a simplistic scanning of source code:

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.

Next Steps


 READ more about PL/SQL

 READ more about PL/Scope
 

DOWNLOAD
 Oracle Database 11g
 the helper package for this column

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


Steven Feuerstein (steven.feuerstein@quest.com) is Quest Software’s PL/SQL evangelist. He has published 10 books on Oracle’s programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O’Reilly Media).

 

 

Send us your comments