-- Unused Variables in PL/SQL
-- Developers in the rush to meet deadlines create code and in  DEV/QA cycle or sprint 
-- end up declaring several variables and forget to delete them when the functionality 
-- changes.  Some  tools  show such  variables.  The query  below can be  used to list 
-- unused variables in Oracle package.

-- Variables are declared but not assigned or referenced and hence are unused.

SELECT
  d.object_name  package_name
 ,d.name  variable_name
 ,d.type
 ,d.line
 ,d.usage
 ,d.signature    --optional
 ,s.text
FROM 
     user_identifiers d
JOIN user_source s
ON   d.object_type = s.type   AND
     d.object_name = s.name   AND
     d.line = s.line
WHERE  NOT EXISTS
 (SELECT  NULL
  FROM    user_identifiers ra
  WHERE   d.object_name = ra.object_name
  AND     d.name = ra.name
  AND     d.signature = ra.signature
  AND     ra.usage IN ('ASSIGNMENT','REFERENCE')
 )
AND    d.object_type IN ('FUNCTION','PROCEDURE','PACKAGE BODY','TRIGGER')
AND    d.type = 'VARIABLE'
AND    d.usage = 'DECLARATION'
AND    d.object_name = 'LOAD_SALES_PKG'
ORDER BY
  package_name, variable_name, line
;



--Other way is by session management:

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';  

ALTER SESSION SET PLSQL_WARNINGS='identifiers:all';