Oracle SQL ORA-40478错误排查：CLOB返回大JSON失败

问题根源 #

你遇到的ORA-40478错误，核心原因是EXECUTE IMMEDIATE默认会将动态查询的结果按VARCHAR2类型解析，即便你用CLOB变量接收结果。哪怕查询里指定了RETURNING CLOB，Oracle在某些场景下仍可能隐式将结果视为VARCHAR2，当结果超过4000字符时就触发长度限制报错。

两种可行的解决方法 #

方法一：强制查询返回明确的CLOB类型 #

在动态查询的外层添加CAST(...) AS CLOB，确保Oracle执行查询时直接返回CLOB类型，避免隐式转换：

SELECT CAST(
    JSON_SERIALIZE( JSON_OBJECT(
        'surname' : column_X,
        'surname' : ( SELECT JSON_ARRAYAGG ( JSON_OBJECT(
            'surname' : column_Y
        ) ABSENT ON NULL RETURNING CLOB) as data 
        FROM table_Y
        WHERE column_Y = column_X)
        -- 其余JSON构造逻辑
    ) RETURNING CLOB ) RETURNING CLOB PRETTY 
    AS CLOB
) as data
FROM table_X
WHERE rowid = 'AAAgDXAAMAAAIL9AAN';

修改后，EXECUTE IMMEDIATE能正确识别返回类型为CLOB，直接存入你的o_return变量。

方法二：改用DBMS_SQL包执行动态查询 #

如果方法一仍不生效，使用DBMS_SQL包显式定义列类型为CLOB，强制Oracle以CLOB格式返回结果，这种方式更稳定处理大体积数据：

DECLARE
    v_query CLOB;
    o_return CLOB;
    v_cursor NUMBER;
    v_rows_processed NUMBER;
BEGIN
    v_query := 'SELECT JSON_SERIALIZE(JSON_OBJECT(...
    WHERE rowid = ''AAAgDXAAMAAAIL9AAN''';

    -- 初始化游标
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    -- 解析动态SQL
    DBMS_SQL.PARSE(v_cursor, v_query, DBMS_SQL.NATIVE);
    -- 定义第1列的类型为CLOB，绑定到o_return变量
    DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, o_return);
    -- 执行查询
    v_rows_processed := DBMS_SQL.EXECUTE(v_cursor);
    
    -- 获取查询结果
    IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
        DBMS_SQL.COLUMN_VALUE(v_cursor, 1, o_return);
    END IF;
    
    -- 关闭游标
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
    
    htp.p(o_return);
END;

额外检查点 #

确保所有嵌套的JSON构造函数（如JSON_ARRAYAGG、JSON_OBJECT）都明确指定了RETURNING CLOB，避免嵌套层级中出现VARCHAR2类型的中间结果，导致最终结果被隐式转换为VARCHAR2。

内容的提问来源于stack exchange，提问作者Gatho