How come "Find Unused Identifiers" finds used identifiers

Toad for Oracle
1

I am new to using Toad and keep finding issues that make it difficult to like.

For example, I was very happy to see that Toad has a “Find Unused Identifiers” facility, but, when I use it, it manages to list as unused, variables that are clearly being used.

Is this feature broken? Here’s a sample of what I’ve tried:

1351.Capture1.png

0602.Capture2.png

3326.Capture3.png

1351.Capture1.png

2

I posted this seven days ago and have not received any response although currently there have been 63 views. Not sure what to think about that.

For now, I have reverted to Oracle’s SQL Developer which I currently prefer for several other reasons and have been using the following code to find unused variables.

Toad developers: You may want to contact Binuraj (see below) to make a working feature. [;)]

DECLARE
/* Date :- 12-Jan-2014
Developer :- Binuraj Somanathan Nair (oracleappsdiary.blogspot.co.uk/))
*/
l_package_filter VARCHAR2(30) := ‘<pkg_name_here>’;
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 user_identifiers ide
WHERE object_name LIKE l_package_filter
AND object_type = ‘PACKAGE BODY’
order by line)
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;

3

Feel free to post sample code if you’d like and I’ll have a look.