sql - Oracle 中的 LISTAGG 给出 NULL

Question

如果列值太长，则 listagg 在 Oracle 12c 中给出 NULL 输出。这是什么原因，解决方法是什么？

SQL> select length(algo_desc) from r2_temp where DECL = '305';
     32759

ALGO_DESC 很长，比如 1045,2339,2389.......37364,58922,2389392现在，如果我使用 LISTAGG，它会给出 NULL，如下所示:

SQL> select dump(listagg(algo_desc, ',') within group (order by algo_desc)) as algo_desc from r2_temp where DECL = '305';
     NULL

列 ALGO_DESC 的最大大小为 32767，它在 12c 中扩展为 varchar2

Answer 1

作为解决方法，您可以使用此函数:

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(32767);
-- or     CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF NUMBER;


CREATE OR REPLACE FUNCTION JoinTable(TAB IN VARCHAR_TABLE_TYPE, Joiner IN VARCHAR2) RETURN CLOB IS
    res CLOB;
BEGIN
    IF TAB IS NULL THEN 
        RETURN NULL; 
    END IF;
    IF TAB.COUNT = 0 THEN 
        RETURN NULL; 
    END IF;

    DBMS_LOB.CREATETEMPORARY(res, FALSE, DBMS_LOB.CALL);
    IF TAB(TAB.FIRST) IS NOT NULL THEN
        DBMS_LOB.APPEND(res, TAB(TAB.FIRST));
    END IF;
    IF TAB.COUNT > 1 THEN
        FOR i IN TAB.FIRST+1..TAB.LAST LOOP
            DBMS_LOB.APPEND(res, Joiner||TAB(i));
        END LOOP;
    END IF;
    RETURN res;

END JoinTable;



select JoinTable(CAST(COLLECT(algo_desc order by algo_desc) AS VARCHAR_TABLE_TYPE), ',') as algo_desc 
from r2_temp 
where DECL = '305';