Oracle-ORA-01489:字符串连接的结果太长 [英] Oracle - ORA-01489: result of string concatenation is too long
问题描述
创建一个视图，其中一个字段(共6个)是源表中两个字段的串联.创建了第二个视图，该视图使用listagg潜在地合并串联的结果.使用此两步操作可以正确返回源表中两个字段的listagg串联.
Created one view within which one field(of 6) is a concatenation of two fields from the source table. Created a second view that uses listagg to potentially combine the results of the concatenation. Using this two step operation correctly returns the listagg concatenation of the two fields from the source table.
当我尝试创建一个将同时产生连接和listagg的视图时，出现oracle错误ora-01489字符串连接的结果太长.
When I try to create one view that will both produce the concatenation and the listagg, I get the oracle error ora-01489 Result of string concatenation is too long.
作为测试，我选择了max(length(concatenated field)，并返回837.所以看来所述错误是错误的.
As a test, I selected max(length(concatenated field) and that returned 837. So the stated error is in error, it seems.
因此它必须在语法中.我已经尝试过rtrim，trim甚至substr，但是无法同时获得view语句来编译listagg(concatenation)和返回数据.
So it must be in the syntax. I have tried rtrim, trim and even substr, but cannot get the view statement to both compile listagg(concatenation) and return data.
我能够开发出两个可以正确返回数据的视图，这表明我已经掌握了基本语法，但是将串联与listagg函数结合起来的任务却无法解决.
That I am able to develop the two views that correctly return the data suggests I have the basic syntax down, but the task of combining the concatenation with the listagg function I have not been able to figure out.
在另一种情况下，我已经能够在一个语句中将串联与listagg函数结合起来:
In a separate situation, I have been able to combine the concatenation with the listagg function in one statement:
LISTAGG (STATEMENTS || ' - ' || BIRTH_DATE, '; ')
WITHIN GROUP (ORDER BY STATEMENTS || ' - ' || BIRTH_DATE)
AS GROWING_UP
此语法正确返回所需的数据.........
This syntax correctly returns the desired data.........
但是在上述情况下，类似的语法会导致Ora-01489错误.
But in the situation described above, similar syntax creates the Ora-01489 error.
知道Oracle的错误有时可能会引起误解，我想知道是否有任何机上专家会知道Oracle可能抛出此错误以外的其他原因?
Knowing that at times Oracle errors can be misleading, I am wondering if any onboard experts might know of any reason other than that stated that Oracle might throw this error?
LISTAGG ((NUMBER || '-' || text), ',') WITHIN GROUP (ORDER BY (NUMBER || '-' || text))
AS
restrictions FROM source
返回错误
推荐答案
您超出了4000个字节的 SQL限制，该限制也适用于LISTAGG.
You are exceeding the SQL limit of 4000 bytes which applies to LISTAGG as well.
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 .
As a workaround, you could use 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字节.
If you want to concatenate multiple columns which itself have 4000 bytes, then you can concatenate the XMLAGG output of each column to avoid the SQL limit of 4000 bytes.
例如，
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;
这篇关于Oracle-ORA-01489:字符串连接的结果太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
