LISTAGG功能:“字符串连接的结果太长". [英] LISTAGG function: "result of string concatenation is too long"

问题描述

我正在使用Oracle SQL开发人员版本3.0.04.我试图使用功能LISTAGG将数据分组在一起.

I'm using Oracle SQL developer version 3.0.04. I attempted to use the function LISTAGG to group the data together..

    CREATE TABLE FINAL_LOG AS
    SELECT SESSION_DT, C_IP, CS_USER_AGENT,
    listagg(WEB_LINK, ' ')
        WITHIN GROUP(ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS"
        FROM webviews
        GROUP BY C_IP, CS_USER_AGENT, SESSION_DT
        ORDER BY SESSION_DT

但是，我不断收到错误消息，

However, I keep getting the error,

SQL Error: ORA-01489: result of string concatenation is too long

我非常确定输出可能会超过4000，因为这里提到的WEB_LINK是url stem和url query的串联值.

I'm pretty sure that the output may be more than 4000, since the WEB_LINK mentioned here is a concatenated value of url stem and url query.

有什么办法可以解决吗?

Is there any way to go around it or is there any other alternative?

推荐答案

由于聚合字符串可以长于4000个字节，因此不能使用LISTAGG函数.您可能会创建用户定义的聚合函数，该函数将返回CLOB而不是VARCHAR2.有一个用户定义的聚合示例，该示例在

Since the aggregates string can be longer than 4000 bytes, you can't use the LISTAGG function. You could potentially create a user-defined aggregate function that returns a CLOB rather than a VARCHAR2. There is an example of a user-defined aggregate that returns a CLOB in the original askTom discussion that Tim links to from that first discussion.

这篇关于LISTAGG功能:“字符串连接的结果太长".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！