Oracle LISTAGG関数の注意点とエラー時の対処法
LISTAGG
関数を使用する際の注意点とよくあるエラーの対処法を紹介します。
複数行の値をグループ化してカンマ区切りで取得したい時などにLISTAGG
関数を使用することがあります。
col1 |
---|
val1 |
val2 |
val3 |
SELECT
LISTAGG(col1, ',') WITHIN GROUP (ORDER BY col1) result
FROM
table1
result |
---|
val1,val2,val3 |
注意点
LISTAGG
関数は以下の点に注意が必要です。
11gR2から使用可能
LISTAGG
関数はOracle
のバージョンが11gR2
から使用可能です。
10g
や11gR1
では動作しません。特に11g
はR1
とR2
のマイナーバージョンの違いで使用可能かどうかが変わってきてしまうので、注意が必要です。
連結した文字列が4000バイトを超えるとエラーになる
Oracle
で扱える文字列は4000バイトのため、連結した文字列が4000バイトを超える場合は以下のエラーが発生します。
ORA-01489: 文字列を連結した結果、長さが最大長を超えました
ORA-01489: result of string concatenation is too long
対処法
LISTAGG
関数の代わりにXMLAGG
関数を使用すれば上記の問題点を解決できます。
SELECT
RTRIM(XMLAGG(XMLELEMENT(e, col1 || ',').EXTRACT('//text()')).GetClobVal(), ',') result
FROM
table1
result |
---|
val1,val2,val3 |
XMLAGG
は11gR2
以外のバージョンでも使用できます。
GetClobVal
で連結した文字列を明示的にCLOB型に変換することにより、4000バイト以上の文字列を扱うことができるようになります。