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.
|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|
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 lineUsages 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_idUsing 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
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.
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.
READ more about PL/SQL
READ more about PL/Scope
Photography by Dmitri Popov, Unsplash