Oracle - ORA-01489: 字符串连接的结果太长
创建了一个视图，其中一个字段(共 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，但无法获得同时编译 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
返回错误
推荐答案
您超出了 SQL 限制 4000 字节，这也适用于 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;
相关文章