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
WHERE owner = USER
AND object_name =
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
AS (SELECT DISTINCT owner
FROM all_identifiers has_exc
WHERE has_exc.owner = USER
AND has_exc.usage = 'DECLARATION'
AND has_exc.TYPE = 'EXCEPTION'),
AS (SELECT DISTINCT owner
FROM all_identifiers with_rh
WHERE with_rh.owner = USER
AND with_rh.usage = 'REFERENCE'
AND with_rh.TYPE = 'EXCEPTION')
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/
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
PRAGMA EXCEPTION_INIT (
quest.com) is Quest
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).
Let’s see what PL/Scope has to say about
the e_bad_data identifier:
READ more about PL/SQL
READ more about PL/Scope
Oracle Database 11g
the helper package for this column
SEP TEMBER/OC TOBER 2010