2025/04/01

ストアドプロシージャのソースとバイト数を取得する。
そのとき、
CREATE OR REPLACE PROCEDURE OWNER1.PROC_A ...
のような冒頭部のスキーマ名部分は削って取得したい。

------
SELECT LOWER(
         REGEXP_REPLACE(
           LISTAGG(TEXT, '') WITHIN GROUP (ORDER BY LINE), 
           'CREATE OR REPLACE PROCEDURE\s+\w+\.', 
           'CREATE OR REPLACE PROCEDURE ',
           1, 1, 'i'
         )
       ) AS normalized_source,
       LENGTH(
         REGEXP_REPLACE(
           LISTAGG(TEXT, '') WITHIN GROUP (ORDER BY LINE), 
           'CREATE OR REPLACE PROCEDURE\s+\w+\.', 
           'CREATE OR REPLACE PROCEDURE ',
           1, 1, 'i'
         )
       ) AS byte_length,
       COUNT(*) AS line_count
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND NAME = 'PROC_A'
AND OWNER = 'OWNER1';
------
SELECT LOWER(
         REGEXP_REPLACE(
           RTRIM(XMLAGG(XMLELEMENT(E, TEXT ORDER BY LINE).EXTRACT('//text()') ).GetClobVal(), CHR(10)),
           'CREATE OR REPLACE (PROCEDURE|FUNCTION|PACKAGE BODY)\s+\w+\.', 
           'CREATE OR REPLACE \1 ',
           1, 1, 'i'
         )
       ) AS normalized_source,
       LENGTH(
         REGEXP_REPLACE(
           RTRIM(XMLAGG(XMLELEMENT(E, TEXT ORDER BY LINE).EXTRACT('//text()') ).GetClobVal(), CHR(10)),
           'CREATE OR REPLACE (PROCEDURE|FUNCTION|PACKAGE BODY)\s+\w+\.', 
           'CREATE OR REPLACE \1 ',
           1, 1, 'i'
         )
       ) AS byte_length,
       COUNT(*) AS line_count,
       TYPE
FROM DBA_SOURCE
WHERE TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE BODY')
AND NAME = 'OBJECT_NAME'  -- ← 比較対象のオブジェクト名
AND OWNER = 'OWNER1'
GROUP BY TYPE;
------
SELECT LOWER(
         REGEXP_REPLACE(
           LISTAGG(TEXT, '') WITHIN GROUP (ORDER BY LINE),
           'CREATE OR REPLACE (PROCEDURE|FUNCTION|PACKAGE BODY)\s+\w+\.', 
           'CREATE OR REPLACE \1 ',
           1, 1, 'i'
         )
       ) AS normalized_source,
       LENGTH(
         REGEXP_REPLACE(
           LISTAGG(TEXT, '') WITHIN GROUP (ORDER BY LINE),
           'CREATE OR REPLACE (PROCEDURE|FUNCTION|PACKAGE BODY)\s+\w+\.', 
           'CREATE OR REPLACE \1 ',
           1, 1, 'i'
         )
       ) AS byte_length,
       COUNT(*) AS line_count,
       TYPE
FROM DBA_SOURCE
WHERE TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE BODY')
AND NAME = 'OBJECT_NAME'  -- ← 比較対象のオブジェクト名
AND OWNER = 'OWNER1'
GROUP BY TYPE;
------