CLOB型の列 を 1行だけ取得する リテラルSQL を 引数にして、その CLOB型のデータを 2000文字ずつ分解して 複数レコードとして返却する Table Function を作成

うーん、タイトルからしてダーティな作りやね 彡(-)(-)

CLOB型の列 を 1行だけ取得する リテラルSQL を 引数にして、
その CLOB型のデータを 2000文字ずつ分解して 複数レコードとして
返却する Table Function を 作成します。

-- Package定義
CREATE OR REPLACE PACKAGE clob_2_mrow_vc2
IS
  -- Table Function が 返す型定義
  TYPE tp_mr_vc2 IS TABLE OF VARCHAR2(4000);
  -- Table Function の 定義
  FUNCTION sql_2_mr_vc2(
      in_v_sql IN VARCHAR2
  )
  RETURN tp_mr_vc2 PIPELINED;
END clob_2_mrow_vc2;
/

-- Package本体の定義
CREATE OR REPLACE PACKAGE BODY clob_2_mrow_vc2
IS
  -- Table Function本体
  FUNCTION sql_2_mr_vc2(
      in_v_sql IN VARCHAR2
  )
  RETURN tp_mr_vc2 PIPELINED
  IS
    -- 変数定義
    rec        VARCHAR2(4000); -- Table Function が 返す行
    c_clob     CLOB;           -- CLOBデータ
    i_length   INTEGER;        -- CLOBデータのバイト数
    i_offset   INTEGER;        -- CLOBデータ操作時のoffset
  BEGIN
    --動的SQLを実行してCLOBを取得
    EXECUTE IMMEDIATE in_v_sql INTO c_clob;
    -- 初期化
    i_length := DBMS_LOB.GETLENGTH(c_clob);
    i_offset := 1;
    --CLOBデータ を 2000文字ずつ 切り出して レコードとして返却
    LOOP
      rec := DBMS_LOB.SUBSTR(c_clob, 2000, i_offset);
      PIPE ROW(rec);
      i_offset := i_offset + 2000;
      EXIT WHEN i_offset > i_length;
    END LOOP;
    RETURN;
  END sql_2_mr_vc2;
END clob_2_mrow_vc2;
/

実行結果は以下の通り。想定通り動いたけど、結局使い道無いやな…彡(-)(-)

SQL> SET LINESIZE 300
SQL> SET PAGESIZE 1000
SQL> SELECT LENGTH(COLUMN_VALUE) FROM TABLE(
  2    clob_2_mrow_vc2.sql_2_mr_vc2(
  3      'SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE DBID = 2461204241 AND SQL_ID = ''grtrhkkshy2wh'''
  4    )
  5  );

LENGTH(COLUMN_VALUE)
--------------------
                2000
                2000
                2000
                2000
                  ：
                2000
                2000
                2000
                 498

57 rows selected.

SQL>