Skip to main content

Find multiline comments in object

T

This script will show you multiline comments in package or any other object available in user_source view. Do you know a better way how to do this?

WITH s AS (
    SELECT s.*
    FROM user_source s
    WHERE s.name    = '&OBJECT_NAME'
        AND s.type  = 'PACKAGE BODY'
),
s1 AS (
    SELECT s.*, ROW_NUMBER() OVER(PARTITION BY s.name ORDER BY s.line) AS rn
    FROM s
    WHERE       REGEXP_LIKE(s.text, '(/\*)', 'i')
        AND NOT REGEXP_LIKE(s.text, '(\*/)', 'i')
        AND s.text NOT LIKE '%''%'
),
s2 AS (
    SELECT s.*, ROW_NUMBER() OVER(PARTITION BY s.name ORDER BY s.line) AS rn
    FROM s
    WHERE NOT REGEXP_LIKE(s.text, '(/\*)', 'i')
        AND   REGEXP_LIKE(s.text, '(\*/)', 'i')
        AND s.text NOT LIKE '%''%'
)
SELECT s1.name, s1.line AS start_line, s2.line AS end_line, s2.line - s1.line + 1 AS lines
FROM s1
LEFT JOIN s2
    ON s2.name  = s1.name
    AND s2.rn   = s1.rn
ORDER BY 1, 2;


Comments