Oracle PL/Scope could be useful

Background

PL/Scope is functionality in PL/SQL that collects details of identifiers like variables, parameters, function calls, etc from compiled PL/SQL packages and then stores the details in the database. These details can then be viewed using the ALL_IDENTIFIERS view.


For further information on PL/Scope see this article by Steven Feuerstein and the Oracle Documentation. It can be enabled for a session by executing the following:

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL' /

To disable PL/Scope use:

ALTER SESSION SET  plscope_settings='IDENTIFIERS:NONE' /

The data in the view can then be used for various analysis and quality checks.

Sample usage - Variable declared but never used

To find if any variables have been created in PL/SQL code but are never used. For some large packages it is easy to lose track of variables that may have been removed from functionality but their declarations remain. The following SQL example will identify any occurrences in the package MY_BIG_PACKAGE where a variable has been declared but not used on any other line in the package.

select *
from all_identifiers ai
where ai.owner = 'SCHEMA_OWNER' and
ai.type = 'VARIABLE' and
ai.object_name = 'MY_BIG_PACKAGE' and
not exists
(select 'x'
from all_identifiers ai2
where ai2.signature = ai.signature and
ai2.line != ai.line)

Sample usage - Conforming to naming standards

This example will find any variables in the package MY_BIG_PACKAGE that do not start with either of my standard prefixes v_ for standard variables or gv_ for global variables.

Note that the even though the code has the value in lower-case the value will be stored as upper-case when selecting ALL_IDENTIFIERS

select *
from all_identifiers ai
where ai.owner = 'SCHEMA_OWNER' and
ai.object_name = 'MY_BIG_PACKAGE' and;
ai.type = 'VARIABLE' and
ai.usage = 'DECLARATION' and
(ai.name not like 'V/_%' ESCAPE '/' and
ai.name not like 'GV/_%' ESCAPE '/')

This example can then be extended to look at many other standards like constants, parameters, cursors and even the procedure names themselves within the same package.

SELECT *
FROM all_identifiers ai
WHERE ai.owner = 'SCHEMA_OWNER' AND
ai.object_name = 'MY_BIG_PACKAGE' AND
((ai.type = 'VARIABLE' AND
ai.usage = 'DECLARATION' AND
-- variables start with v_ or gv_
(ai.name NOT LIKE 'V/_%' ESCAPE '/' AND 
ai.name NOT LIKE 'GV/_%' ESCAPE '/')) OR
-- cursor names start with c_
(ai.type = 'CURSOR' AND
ai.usage = 'DECLARATION' AND
ai.name NOT LIKE 'C/_%' ESCAPE '/') OR
(ai.type = 'FORMAL IN' AND -- parameters
ai.usage = 'DECLARATION' AND
-- parameters start with p_
(ai.name NOT LIKE 'P/_%' ESCAPE '/' AND
-- cursor parameters start with cp_
ai.name NOT LIKE 'CP/_%' ESCAPE '/' ))OR
-- constants start with cst_
(ai.type = 'CONSTANT' AND
ai.usage = 'DECLARATION' AND
ai.name NOT LIKE 'CST/_%' ESCAPE '/') OR
-- procedure names start with 3 letters then p_
(ai.type = 'PROCEDURE' AND
ai.usage = 'DECLARATION' AND
ai.name NOT LIKE '___P/_%' ESCAPE '/'))


Of course these are my standards and would need to be customised for your own application.