[解決済み] Oracle - ORA-01489: 文字列連結の結果が長すぎる [重複].

質問内容

ソーステーブルから2つのフィールドを連結した1つのフィールド（6つのうち）を持つビューを作成しました。 連結の結果を潜在的に結合するために listagg を使用する 2 番目のビューを作成しました。 この2段階の操作を行うと、ソーステーブルから2つのフィールドを連結したlistaggが正しく返されます。

連結と listagg の両方を生成するビューを 1 つ作成しようとすると、Oracle エラー ora-01489 Result of string concatenation is too long というエラーが発生します。

テストとして、max(length(concatenated field)) を選択したところ、837が返されました。 つまり、記載されているエラーは誤りであるようです。

ということは、構文に問題があるのでしょう。 rtrim、trim、substrも試しましたが、ビューステートメントでlistagg(concatenation)のコンパイルとデータの返却の両方を行うことができません。

データを正しく返す2つのビューを開発できたということは、基本的な構文は理解できたということですが、連結と listagg 関数を組み合わせるという作業は理解できていません。

別の場面では、連結とlistagg関数を1つのステートメントで組み合わせることができました。

    LISTAGG (STATEMENTS || ' - ' || BIRTH_DATE, ';  ')
                   WITHIN GROUP (ORDER BY STATEMENTS || ' - ' || BIRTH_DATE)
                   AS GROWING_UP

この構文は、目的のデータを正しく返します............。

しかし、上記のような状況では、同様の構文でOra-01489のエラーが発生します。

Oracleのエラーは時に誤解を招くことがありますが、Oracleがこのエラーを出す理由を、記載されているもの以外にご存知の方がいらっしゃいましたら教えてください。

LISTAGG ((NUMBER || '-' || text), ',') WITHIN GROUP (ORDER BY (NUMBER || '-' || text))
AS
  restrictions FROM source

エラーを返す

解決方法は？

を超えています。 SQLの制限 に適用される 4000 バイトの LISTAGG も同様です。

SQL> SELECT listagg(text, ',') WITHIN GROUP (
  2  ORDER BY NULL)
  3  FROM
  4    (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
  5    )
  6  /
SELECT listagg(text, ',') WITHIN GROUP (
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

回避策として XMLAGG .

例えば

SQL> SET LONG 2000000
SQL> SET pagesize 50000
SQL> SELECT rtrim(xmlagg(XMLELEMENT(e,text,',').EXTRACT('//text()')
  2                     ).GetClobVal(),',') very_long_text
  3  FROM
  4    (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
  5    )
  6  /

VERY_LONG_TEXT
--------------------------------------------------------------------------------
one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen
,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twenty-one,twenty-two,twenty
-three,twenty-four,twenty-five,twenty-six,twenty-seven,twenty-eight,twenty-nine,
thirty,thirty-one,thirty-two,thirty-three,thirty-four,thirty-five,thirty-six,thi
rty-seven,thirty-eight,thirty-nine,forty,forty-one,forty-two,forty-three,forty-f
our,forty-five,forty-six,forty-seven,forty-eight,forty-nine,fifty,fifty-one,fift
y-two,fifty-three,fifty-four,fifty-five,fifty-six,fifty-seven,fifty-eight,fifty-
nine,sixty,sixty-one,sixty-two,sixty-three,sixty-four,sixty-five,sixty-six,sixty
-seven,sixty-eight,sixty-nine,seventy,seventy-one,seventy-two,seventy-three,seve
nty-four,seventy-five,seventy-six,seventy-seven,seventy-eight,seventy-nine,eight
y,eighty-one,eighty-two,eighty-three,eighty-four,eighty-five,eighty-six,eighty-s
even,eighty-eight,eighty-nine,ninety,ninety-one,ninety-two,ninety-three,ninety-f
our,ninety-five,ninety-six,ninety-seven,ninety-eight,ninety-nine,one hundred,one
 hundred one,one hundred two,one hundred three,one hundred four,one hundred five
,one hundred six,one hundred seven,one hundred eight,one hundred nine,one hundre
d ten,one hundred eleven,one hundred twelve,one hundred thirteen,one hundred fou
rteen,one hundred fifteen,one hundred sixteen,one hundred seventeen,one hundred
eighteen,one hundred nineteen,one hundred twenty,one hundred twenty-one,one hund
red twenty-two,one hundred twenty-three,one hundred twenty-four,one hundred twen
ty-five,one hundred twenty-six,one hundred twenty-seven,one hundred twenty-eight
,one hundred twenty-nine,one hundred thirty,one hundred thirty-one,one hundred t
hirty-two,one hundred thirty-three,one hundred thirty-four,one hundred thirty-fi
ve,one hundred thirty-six,one hundred thirty-seven,one hundred thirty-eight,one
hundred thirty-nine,one hundred forty,one hundred forty-one,one hundred forty-tw
o,one hundred forty-three,one hundred forty-four,one hundred forty-five,one hund
red forty-six,one hundred forty-seven,one hundred forty-eight,one hundred forty-
nine,one hundred fifty,one hundred fifty-one,one hundred fifty-two,one hundred f
ifty-three,one hundred fifty-four,one hundred fifty-five,one hundred fifty-six,o
ne hundred fifty-seven,one hundred fifty-eight,one hundred fifty-nine,one hundre
d sixty,one hundred sixty-one,one hundred sixty-two,one hundred sixty-three,one
hundred sixty-four,one hundred sixty-five,one hundred sixty-six,one hundred sixt
y-seven,one hundred sixty-eight,one hundred sixty-nine,one hundred seventy,one h
undred seventy-one,one hundred seventy-two,one hundred seventy-three,one hundred
 seventy-four,one hundred seventy-five,one hundred seventy-six,one hundred seven
ty-seven,one hundred seventy-eight,one hundred seventy-nine,one hundred eighty,o
ne hundred eighty-one,one hundred eighty-two,one hundred eighty-three,one hundre
d eighty-four,one hundred eighty-five,one hundred eighty-six,one hundred eighty-
seven,one hundred eighty-eight,one hundred eighty-nine,one hundred ninety,one hu
ndred ninety-one,one hundred ninety-two,one hundred ninety-three,one hundred nin
ety-four,one hundred ninety-five,one hundred ninety-six,one hundred ninety-seven
,one hundred ninety-eight,one hundred ninety-nine,two hundred,two hundred one,tw
o hundred two,two hundred three,two hundred four,two hundred five,two hundred si
x,two hundred seven,two hundred eight,two hundred nine,two hundred ten,two hundr
ed eleven,two hundred twelve,two hundred thirteen,two hundred fourteen,two hundr
ed fifteen,two hundred sixteen,two hundred seventeen,two hundred eighteen,two hu
ndred nineteen,two hundred twenty,two hundred twenty-one,two hundred twenty-two,
two hundred twenty-three,two hundred twenty-four,two hundred twenty-five,two hun
dred twenty-six,two hundred twenty-seven,two hundred twenty-eight,two hundred tw
enty-nine,two hundred thirty,two hundred thirty-one,two hundred thirty-two,two h
undred thirty-three,two hundred thirty-four,two hundred thirty-five,two hundred
thirty-six,two hundred thirty-seven,two hundred thirty-eight,two hundred thirty-
nine,two hundred forty,two hundred forty-one,two hundred forty-two,two hundred f
orty-three,two hundred forty-four,two hundred forty-five,two hundred forty-six,t
wo hundred forty-seven,two hundred forty-eight,two hundred forty-nine

もし、あなたが 複数のカラムを連結する を持つもので、それ自体が 4000バイト であれば、各カラムのXMLAGG出力を連結することで、SQLの4000バイトの制限を回避することができます。

例えば

WITH DATA AS
  ( SELECT 1 id, rpad('a1',4000,'*') col1, rpad('b1',4000,'*') col2 FROM dual
  UNION
  SELECT 2 id, rpad('a2',4000,'*') col1, rpad('b2',4000,'*') col2 FROM dual
  )
SELECT ID,
       rtrim(xmlagg(XMLELEMENT(e,col1,',').EXTRACT('//text()') ).GetClobVal(), ',')
       || 
       rtrim(xmlagg(XMLELEMENT(e,col2,',').EXTRACT('//text()') ).GetClobVal(), ',') 
       AS very_long_text
FROM DATA
GROUP BY ID
ORDER BY ID;