LISTAGG 函数：“字符串连接的结果太长”

Question

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

     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

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

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

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

有什么办法可以绕过它还是有其他选择？

原文由 user1874311 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

由于聚合字符串可能超过 4000 字节，因此不能使用 LISTAGG 函数。您可能会创建一个 用户定义的聚合函数，该函数返回 CLOB 而不是 VARCHAR2 。在 Tim 从第一个讨论链接到的 原始 askTom 讨论 中，有一个用户定义的聚合示例返回 CLOB 。

原文由 Justin Cave 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

我正在使用名为 clob_agg 的自定义函数，使用如下：

 select clob_agg(*detail_column*)
from *table*
group by *group_column*

真实例子：

 select length(clob_agg(x||'')) fullList
from (select level as x
      from dual
      connect by level < 40000)

输出为： 228887 ==> 输出长度:)

对于定义函数（在 sqlDeveloper、dataGrip 或其他一些编辑器中有时无法执行）：

 CREATE OR REPLACE TYPE t_clob_agg AS OBJECT
(
    g_string clob,

    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_clob_agg)
        RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_clob_agg,
                                         value IN clob)
        RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_clob_agg,
                                           returnValue OUT clob,
                                           flags IN NUMBER)
        RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_clob_agg,
                                       ctx2 IN t_clob_agg)
        RETURN NUMBER
);
/
SHOW ERRORS

CREATE OR REPLACE TYPE BODY t_clob_agg IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_clob_agg)
        RETURN NUMBER IS
    BEGIN
        sctx := t_clob_agg(NULL);
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_clob_agg,
                                         value IN clob)
        RETURN NUMBER IS
    BEGIN
        -- Concatenate string only when not already existing in the list (=unique)
        SELF.g_string := self.g_string || ',' || value;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_clob_agg,
                                           returnValue OUT clob,
                                           flags IN NUMBER)
        RETURN NUMBER IS
    BEGIN
        returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_clob_agg,
                                       ctx2 IN t_clob_agg)
        RETURN NUMBER IS
    BEGIN
        SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
        RETURN ODCIConst.Success;
    END;

END;
/
SHOW ERRORS

CREATE OR REPLACE FUNCTION clob_agg(p_input clob)
    RETURN clob
    PARALLEL_ENABLE AGGREGATE USING t_clob_agg;
/
SHOW ERRORS

原文由 utrucceh 发布，翻译遵循 CC BY-SA 4.0 许可协议