Package modules boundaries

D

Did you ever wonder which lines in you package are used by which procedure/function? Let's find package modules (procedures and functions) and their boundaries (starting and ending lines).

WITH x AS (
    SELECT
        '&OBJECT_NAME' AS object_name,
        'PACKAGE BODY' AS object_type
    FROM DUAL
),
s AS (
    SELECT s.line, s.text
    FROM user_source s
    CROSS JOIN x
    WHERE s.name    = x.object_name
        AND s.type  = x.object_type
        --AND NOT REGEXP_LIKE(s.text, '^(\s)*$')      -- strip empty line
        --AND NOT REGEXP_LIKE(s.text, '^(\s)[-]{2}')  -- strip comments
),
p AS (
    -- to find first and last module line
    SELECT
        ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY i.line) AS position,
        i.name,
        i.type,
        i.line AS start_line,
        LEAD(i.line) OVER (PARTITION BY i.object_name, i.object_type ORDER BY i.line) - 1 AS end_line
    FROM user_identifiers i
    CROSS JOIN x
    WHERE i.object_name     = x.object_name
        AND i.object_type   = x.object_type
        AND i.type          IN ('PROCEDURE', 'FUNCTION')
        AND i.usage         = 'DEFINITION'
    ORDER BY line
),
s1 AS (
    -- to find multiline comments before each module
    SELECT s.*, ROW_NUMBER() OVER(ORDER BY s.line) AS rn
    FROM s
    WHERE       REGEXP_LIKE(s.text, '(/\*)', 'i')
        AND NOT REGEXP_LIKE(s.text, '(\*/)', 'i')
),
s2 AS (
    SELECT s.*, ROW_NUMBER() OVER(ORDER BY s.line) AS rn
    FROM s
    WHERE NOT REGEXP_LIKE(s.text, '(/\*)', 'i')
        AND   REGEXP_LIKE(s.text, '(\*/)', 'i')
)
SELECT
    r.position,
    r.name,
    r.type,
    r.start_line,
    r.end_line,
    r.lines,
    m.start_line AS description_start
FROM (
    SELECT
        p.position,
        p.name,
        p.type,
        p.start_line,
        MAX(s.line)                 AS end_line,
        MAX(s.line) - p.start_line  AS lines
    FROM (
        -- find last END; before next module start
        SELECT
            p.position, p.name, p.type,
            p.start_line,
            NVL(LEAD(p.start_line) OVER (ORDER BY p.start_line), MAX((SELECT MAX(s.line) - 1 AS last_line FROM s))) AS next_module
        FROM p
        GROUP BY p.position, p.name, p.type, p.start_line
    ) p
    LEFT JOIN s
        ON s.line   > p.start_line
        AND s.line  < p.next_module
        AND REGEXP_LIKE(UPPER(s.text), '^\s*END(\s+' || p.name || ')?\s*;', 'i')
    GROUP BY p.position, p.name, p.type, p.start_line
) r
LEFT JOIN (
    SELECT s1.line AS start_line, s2.line AS end_line
    FROM s1
    LEFT JOIN s2 ON s2.rn = s1.rn
) m ON m.end_line = r.start_line - 1
ORDER BY r.position;

To get assess to PL/SQL scope, you need to recompile objects with IDENTIFIERS:ALL.

BEGIN
    recompile (
        in_optimize         => 1,
        in_code_type        => 'INTERPRETED',
        in_scope            => 'IDENTIFIERS:ALL, STATEMENTS:ALL',
        in_invalid_only     => FALSE
    );
END;
/