LISTAGG 函数:“字符串连接的结果太长"；

2021-12-01 00:00:00 sql oracle

我使用的是 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.在 CLOB 的用户定义聚合示例:0%3a%3a%3a%3aP11_QUESTION_ID:2196162600402#29842804516386">原始 askTom 讨论，Tim 从第一个讨论链接到.

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.

相关文章