PL/SQL函数返回大字符串遇ORA-01489错误的解决问询

编写的TODAS_NOTIFICACIONES_ACTIVAS PL/SQL函数返回CLOB类型，但当返回大字符串时触发错误：

SELECT TODAS_NOTIFICACIONES_ACTIVAS('21/10/2023', 0, 10) as result
FROM DUAL Error on command line: 1 Column: 8 Error Report - SQL Error:
ORA-01489: result of string concatenation is too long ORA-06512: at
"NOTIFICACIONES_PKG", line 95

00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size

函数代码如下：

FUNCTION TODAS_NOTIFICACIONES_ACTIVAS(fecha_val IN VARCHAR2, start_position IN NUMBER, end_position IN NUMBER)
RETURN CLOB
AS
    l_result1 CLOB;
    l_result2 CLOB;
    l_result3 CLOB;

    l_count NUMBER;
BEGIN

    SELECT COUNT(*) INTO l_count
    FROM NOTIFICATIONS
    WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') >= NOTIFICATION_DATE_FROM
      AND TO_DATE(fecha_val, 'DD/MM/YYYY') <= NOTIFICATION_DATE_TO;

    l_result1 := '{ "count": ' || TO_CHAR(l_count) || ', "data": ';

    SELECT '[' ||
           LISTAGG(
               '{ "ID": "' || ID || '", "RECIPIENTS": "' || RECIPIENTS || '", "AGE_FROM": "' || AGE_FROM || '", "AGE_TO": "' || AGE_TO || '", "DEPARTMENT": "' || DEPARTMENT_ID || '", "LOCALITY": "' || LOCALITY_ID || '", "MESSAGE_TITLE": "' || MESSAGE_TITLE || '", "MESSAGE_BODY": "' || MESSAGE_BODY || '", "MULTIMEDIA_ID": "' || MULTIMEDIA_ID || '", "NOTIFICATION_DATE_FROM": "' || TO_CHAR(NOTIFICATION_DATE_FROM, 'DD/MM/YYYY HH24:MI:SS') || '", "NOTIFICATION_DATE_TO": "' || TO_CHAR(NOTIFICATION_DATE_TO, 'DD/MM/YYYY HH24:MI:SS') || '", "SEND_BY_EMAIL": "' || SEND_BY_EMAIL || '", "CREATED_AT": "' || TO_CHAR(CREATED_AT, 'DD/MM/YYYY HH24:MI:SS') || '", "DELETED_AT": "' || TO_CHAR(DELETED_AT, 'DD/MM/YYYY HH24:MI:SS') || '"}',
               ', '
           ) WITHIN GROUP (ORDER BY CREATED_AT DESC) ||
           ']' INTO l_result2
    FROM (
        SELECT 
            ID, RECIPIENTS, AGE_FROM, AGE_TO, DEPARTMENT_ID, LOCALITY_ID, 
            MESSAGE_TITLE, MESSAGE_BODY, MULTIMEDIA_ID, 
            NOTIFICATION_DATE_FROM, NOTIFICATION_DATE_TO, SEND_BY_EMAIL, 
            CREATED_AT, DELETED_AT,
            ROW_NUMBER() OVER (ORDER BY CREATED_AT DESC) AS rn
        FROM NOTIFICATIONS
        WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') >= NOTIFICATION_DATE_FROM
          AND TO_DATE(fecha_val, 'DD/MM/YYYY') <= NOTIFICATION_DATE_TO
    )
    WHERE rn BETWEEN start_position AND end_position;

    
    l_result3 := l_result1 ||l_result2 || '}';

    RETURN l_result3;
END;

已尝试通过start_position和end_position分页解决，但新增通知消息后仍报错，询问其他修复方法、注意事项及返回前的初始检查。

1. 替换LISTAGG为XMLAGG（核心修复） #

LISTAGG返回类型为VARCHAR2，长度受限于数据库MAX_STRING_SIZE设置（默认4000字节，扩展后32767字节），即使赋值给CLOB变量，拼接过程中仍会触发长度超限。改用XMLAGG可直接生成CLOB，规避长度限制：

SELECT '[' ||
       RTRIM(XMLAGG(XMLELEMENT(E, 
           '{ "ID": "' || ID || '", "RECIPIENTS": "' || RECIPIENTS || '", "AGE_FROM": "' || AGE_FROM || '", "AGE_TO": "' || AGE_TO || '", "DEPARTMENT": "' || DEPARTMENT_ID || '", "LOCALITY": "' || LOCALITY_ID || '", "MESSAGE_TITLE": "' || MESSAGE_TITLE || '", "MESSAGE_BODY": "' || MESSAGE_BODY || '", "MULTIMEDIA_ID": "' || MULTIMEDIA_ID || '", "NOTIFICATION_DATE_FROM": "' || TO_CHAR(NOTIFICATION_DATE_FROM, 'DD/MM/YYYY HH24:MI:SS') || '", "NOTIFICATION_DATE_TO": "' || TO_CHAR(NOTIFICATION_DATE_TO, 'DD/MM/YYYY HH24:MI:SS') || '", "SEND_BY_EMAIL": "' || SEND_BY_EMAIL || '", "CREATED_AT": "' || TO_CHAR(CREATED_AT, 'DD/MM/YYYY HH24:MI:SS') || '", "DELETED_AT": "' || NVL(TO_CHAR(DELETED_AT, 'DD/MM/YYYY HH24:MI:SS'), '') || '"}',
           ', '
       ).EXTRACT('//text()') ORDER BY CREATED_AT DESC).GETCLOBVAL(), ', ') ||
       ']' INTO l_result2
FROM (
    SELECT 
        ID, RECIPIENTS, AGE_FROM, AGE_TO, DEPARTMENT_ID, LOCALITY_ID, 
        MESSAGE_TITLE, MESSAGE_BODY, MULTIMEDIA_ID, 
        NOTIFICATION_DATE_FROM, NOTIFICATION_DATE_TO, SEND_BY_EMAIL, 
        CREATED_AT, DELETED_AT,
        ROW_NUMBER() OVER (ORDER BY CREATED_AT DESC) AS rn
    FROM NOTIFICATIONS
    WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') >= NOTIFICATION_DATE_FROM
      AND TO_DATE(fecha_val, 'DD/MM/YYYY') <= NOTIFICATION_DATE_TO
)
WHERE rn BETWEEN start_position AND end_position;

2. 空值处理 #

原代码中TO_CHAR(DELETED_AT)若字段为空会返回NULL，破坏JSON结构，需用NVL处理：

NVL(TO_CHAR(DELETED_AT, 'DD/MM/YYYY HH24:MI:SS'), '')

3. JSON特殊字符转义 #

若MESSAGE_TITLE、MESSAGE_BODY等字段包含双引号、反斜杠等字符，会导致JSON格式错误，需添加转义逻辑：

CREATE OR REPLACE FUNCTION ESCAPE_JSON(p_str IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
    RETURN REPLACE(REPLACE(REPLACE(p_str, '\', '\\'), '"', '\"'), CHR(10), '\n');
END;

拼接时调用该函数：

"MESSAGE_TITLE": "' || ESCAPE_JSON(MESSAGE_TITLE) || '"

4. 分页参数合法性检查 #

在函数开头添加参数校验，避免无效分页范围：

IF start_position < 1 THEN
    start_position := 1;
END IF;
IF end_position < start_position THEN
    end_position := start_position;
END IF;
IF end_position > l_count THEN
    end_position := l_count;
END IF;

5. 日期转换优化 #

提前将输入的日期字符串转为DATE类型，避免重复计算并防止格式错误：

l_fecha DATE;
BEGIN
    l_fecha := TO_DATE(fecha_val, 'DD/MM/YYYY');
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Fecha inválida: ' || fecha_val);
END;
-- 后续查询用l_fecha代替TO_DATE(fecha_val, ...)

6. 中间结果调试 #

若仍有长度问题，可输出中间结果长度定位问题：

DBMS_OUTPUT.PUT_LINE('Longitud l_result1: ' || DBMS_LOB.GETLENGTH(l_result1));
DBMS_OUTPUT.PUT_LINE('Longitud l_result2: ' || DBMS_LOB.GETLENGTH(l_result2));

内容的提问来源于stack exchange，提问作者Julián Oviedo