Find variables, constants and parameters usage

D

Did you ever wondered how (where) are your variables used? This script show usage of local variables, constants and input parameters in packages. You can use it for example for finding unused variables (and removing them). It is based on user_identifiers view/feature and you should check previous article first if it is new for you.

WITH x (object_type, object_name) AS (
    SELECT 'PACKAGE BODY', '%' FROM DUAL
),
i AS (
    SELECT i.*
    FROM x
    JOIN user_identifiers i
        ON i.object_type        = x.object_type
        AND i.object_name       LIKE x.object_name
)
SELECT
    a.object_name,
    b.name                      AS module_name,
    REPLACE(a.type, 'FORMAL ')  AS variable_type,
    a.name                      AS variable_name,
    REGEXP_REPLACE(
        REGEXP_SUBSTR(s.text, '(' || a.name || ')([^;:]*)', 1, 1, 'i'),
        '\s*(' || a.name || ')\s+(.*)',
        '\2', 1, 1, 'i'
        )                       AS data_type,
    CASE WHEN r.lines IS NULL
        THEN 'Y'
        END                     AS unused,
    a.line                      AS declaration,
    r.lines                     AS references,
    a.signature
FROM i a
JOIN i b
    ON b.object_name            = a.object_name
    AND b.object_type           = a.object_type
    AND b.usage_id              = a.usage_context_id
    AND b.type                  IN ('FUNCTION', 'PROCEDURE')
    AND a.type                  IN ('VARIABLE', 'FORMAL IN', 'CONSTANT')
    AND a.usage                 = 'DECLARATION'
JOIN user_source s
    ON s.name                   = a.object_name
    AND s.type                  = a.object_type
    AND s.line                  = a.line
LEFT JOIN (
    -- this may have some duplicite numbers because you can reference same variable on same row multiple times
    SELECT
        i.signature,
        LISTAGG(i.line, ', ') WITHIN GROUP (ORDER BY i.line) AS lines
    FROM i
    WHERE i.usage               IN ('REFERENCE', 'DEFINITION', 'ASSIGNMENT')
    GROUP BY i.signature
) r
    ON r.signature              = a.signature
ORDER BY a.object_name, a.line, a.name;

And if you know signiture you can explore it row by row.

SELECT i.*
FROM user_identifiers i
WHERE i.signature = 'A128FBAA77D03FF310CD0BA476D514DA';