Oracle Magazine - September/October 2010

PL/SQL 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
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

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'),
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

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.

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. W

ables 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
PRAGMA EXCEPTION_INIT (
BEGIN
RAISE e_bad_data;

EXCEPTION
WHEN e_bad_data
THEN
END plscope_demo_proc;

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).