Step 1 :- Set the PLSCOPE_SETTINGS session variable to IDENTIFIERS:
ALTER SESSION SET
PLSCOPE_SETTINGS='IDENTIFIERS:
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;
