Find unused variables in PLSQL package

Oracle provided view ALL_IDENTIFIERS can be used to identify variables and constants which are decalred but never used. Many of the Integrated Development Environments like Toad and PLSQL developer issues warnings at compile time on unused varaibles.

Step 1 :- Set the PLSCOPE_SETTINGS session variable to IDENTIFIERS:ALL for the session.

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

Step 2 :- Recompile the package so that all identifiers are added to ALL_IDENTIFIERS view.

ALTER PACKAGE <> COMPILE;

Step 3 :- Execute the SQL below to identify all the unused variables and constants in the package 

WITH identifiers_inline AS(  

SELECT *

FROM all_identifiers ide

WHERE object_name = '&Package'

AND object_type = 'PACKAGE BODY')

--

SELECT NAME, 
       TYPE,
       line,
       col

FROM identifiers_inline ide

WHERE TYPE IN ('VARIABLE', 'CONSTANT')

AND usage = 'DECLARATION'

AND (NOT EXISTS 

      (SELECT 1

       FROM identifiers_inline

       WHERE signature = ide.signature

       AND usage <> 'DECLARATION')  

      OR  

     NOT EXISTS 

      (SELECT 1

       FROM identifiers_inline

       WHERE signature = ide.signature

       AND usage <> 'DECLARATION'

       AND line <> ide.line

       AND col <> ide.col));

Finally, the below script can be used to check for unused identifiers in packages identified by the package filter parameter. The output will be done in dbms output. Please note that the script recompiles every package fetch by the package filter parameter.

DECLARE

  l_package_filter VARCHAR2(30) := '&package_filter';

  CURSOR cur_packages IS

    SELECT object_name

    FROM all_objects

    WHERE object_name LIKE l_package_filter

    AND object_type = 'PACKAGE BODY';

  CURSOR cur_identifiers IS WITH identifiers_inline AS(

    SELECT object_name package_name,

           NAME identifier_name,

           TYPE,

           line,

           col,

           usage,

           signature

    FROM all_identifiers ide

    WHERE object_name LIKE l_package_filter

    AND object_type = 'PACKAGE BODY')

 

      SELECT package_name,

        identifier_name,

        TYPE,

        line,

        col

      FROM identifiers_inline ide

      WHERE TYPE IN ('VARIABLE', 'CONSTANT')

      AND usage = 'DECLARATION'

      AND (NOT EXISTS (SELECT 1

                      FROM identifiers_inline

                      WHERE signature = ide.signature

                      AND usage <> 'DECLARATION') OR NOT EXISTS (SELECT 1

                             

                                FROM identifiers_inline

                                                              WHERE signature = ide.signature

                                                              AND usage <> 'DECLARATION'

                                                              AND line <> ide.line

                                                              AND col <> ide.col));

BEGIN

  EXECUTE IMMEDIATE 'ALTER SESSION SET PLSCOPE_SETTINGS=''IDENTIFIERS:ALL''';

  FOR rec_packages IN cur_packages

  LOOP

    EXECUTE IMMEDIATE 'ALTER PACKAGE ' || rec_packages.object_name || ' COMPILE';

  END LOOP;

  --

  dbms_output.put_line(rpad('Package Name',

                              31) || rpad('Identifier Name',

                                          31) || rpad('Type',

                                                      15) || rpad('Line',

                                                                  7) || rpad('Column',

                                                                             7));

  dbms_output.put_line(RPAD('*',91,'*'));

  --

  FOR rec_identifiers IN cur_identifiers

  LOOP

    dbms_output.put_line(rpad(rec_identifiers.package_name,

                              31) || rpad(rec_identifiers.identifier_name,

                                          31) || rpad(rec_identifiers.TYPE,

                                                      15) || rpad(rec_identifiers.line,

                                                                  7) || rpad(rec_identifiers.col,

                                                                             7));

  END LOOP;

END;