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.
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.
Now let’s take a look at how we can use
PL/Scope to
Validate naming conventions •
Identify violations of best practices •
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 specifi-
cation, any schema with execute authority
on the package can directly read and write
the variable.
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
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 OU T, or FORMAL IN OU T.
Suppose I declare the package specification in Listing 5.
I can then use the query in Listing 6 to
identify violations of my parameter naming
convention in PLSCOPE_DEMO.
Note the use of usage_context_id to find
the name of the subprogram that “owns” the
parameter.
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
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;
