LISTAGGで最大長を超えました

こじ

2年前

LISTAGGを使って検索結果を横並びに出力する事ができますが、検索結果が4000バイトを超えるとエラー「ORA-01489: 文字列を連結した結果、長さが最大長を超えました」が発生します。

4000バイトを超えるデータを扱う場合はXMLAGGを使う必要があります。

XMLAGGという関数を使えば、結果をCLOB型（上限4GB）で返却してくれるので、ほぼ上限を気にせず使えると思います。

前提条件

NAMEは4000バイトを超えている状態のデータを用意します。

-- テーブル定義
DESC TB_SAMPLE

 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 ID                                                 VARCHAR2(4)
 DEPT                                               VARCHAR2(10)
 NAME                                               VARCHAR2(2000)

-- NAMEが4000バイトを超えている
SELECT
    DEPT
  , SUM(LENGTHB(NAME))
FROM
    TB_SAMPLE
GROUP BY
    DEPT
;

DEPT                 SUM(LENGTHB(NAME))
-------------------- ------------------
管理部                               24
人事部                             6000

LISTAGGで4000バイトを超えるデータを横並び出力

LISTAGGを使うとエラーになってしまいます。

SELECT
    DEPT
  , LISTAGG(NAME, ',')
    WITHIN GROUP (ORDER BY NAME) NAME
FROM
    TB_SAMPLE
GROUP BY
    DEPT
;
行1でエラーが発生しました。:
ORA-01489: 文字列を連結した結果、長さが最大長を超えました

XMLAGGで4000バイトを超えるデータを横並び出力

XMLAGGを使えば、4000バイトを超えるデータも正しく出力できます。

CLOB型を出力するのでSET LONGを指定しておかないと文字切れが発生しますので、気を付けて下さい。

SET LONG 2000000000
SELECT
    RTRIM(
        XMLAGG(
            XMLELEMENT(e, NAME || ',').EXTRACT('//text()')
        ).GetClobVal(), ','
    ) AS RESULT
FROM
    TB_SAMPLE
;

～検索結果は省略～