ORA-06502错误解决：存储过程返回超4000K内容的处理方案

我有一个使用DBMS_LOB.SUBSTR的存储过程，当返回内容超过4000字节时触发ORA-06502错误。尝试将4000字节内容拆分为多个结果拼接时，又出现ORA-01489错误（字符串拼接结果过长）。仅当CLOB数据类型内容小于4000字节时无异常。

存储过程代码 #

CREATE PROCEDURE TEST1 (v_lotNum in varchar, cur_data_out out sys_refcursor)
AS
BEGIN
open cur_data_out for
SELECT
CASE
WHEN l.c_material_info > 0 THEN (SELECT DBMS_LOB.SUBSTR(note_contents,(LENGTH(note_contents)))FROM
lims_notes WHERE note_id = l.c_material_info)
ELSE 'None'
END AS "Reminder"
FROM lot l
WHERE l.t_ph_interface_login = 'T'
AND l.inspection_lot_num = v_lotNum;
END TEST1;

执行报错信息 #

SQL> var mycursor refcursor; 
SQL> begin TEST1 ( v_lotNum => 80000169781, cur_data_out => :mycursor); end; / 

PL/SQL procedure successfully completed.

SQL> print mycursor 
ERROR: ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
ORA-06512: at line 1

no rows selected

方案1：直接返回CLOB类型（最优解） #

问题根源是DBMS_LOB.SUBSTR默认返回VARCHAR2类型，而VARCHAR2在SQL层有长度限制（12c及以前为4000字节）。直接返回CLOB可以彻底规避长度限制：

CREATE PROCEDURE TEST1 (v_lotNum in varchar, cur_data_out out sys_refcursor)
AS
BEGIN
open cur_data_out for
SELECT
CASE
WHEN l.c_material_info > 0 THEN (SELECT note_contents FROM lims_notes WHERE note_id = l.c_material_info)
ELSE TO_CLOB('None')
END AS "Reminder"
FROM lot l
WHERE l.t_ph_interface_login = 'T'
AND l.inspection_lot_num = v_lotNum;
END TEST1;

注意：需要确保调用端支持接收CLOB类型的返回值。

方案2：分块返回大文本（需应用层拼接） #

如果业务必须拆分内容，不要在数据库层拼接，而是返回多行片段由应用层组装：

CREATE PROCEDURE TEST1 (v_lotNum in varchar, cur_data_out out sys_refcursor)
AS
BEGIN
open cur_data_out for
WITH clob_parts AS (
    SELECT 
        l.inspection_lot_num,
        DBMS_LOB.SUBSTR(n.note_contents, 4000, (LEVEL-1)*4000 + 1) AS part_content,
        LEVEL AS part_num
    FROM lot l
    JOIN lims_notes n ON l.c_material_info = n.note_id
    WHERE l.t_ph_interface_login = 'T'
      AND l.inspection_lot_num = v_lotNum
      AND l.c_material_info > 0
    CONNECT BY LEVEL <= CEIL(DBMS_LOB.GETLENGTH(n.note_contents)/4000)
      AND PRIOR n.note_id = n.note_id
      AND PRIOR SYS_GUID() IS NOT NULL
    UNION ALL
    SELECT 
        inspection_lot_num,
        TO_CLOB('None') AS part_content,
        1 AS part_num
    FROM lot l
    WHERE l.t_ph_interface_login = 'T'
      AND l.inspection_lot_num = v_lotNum
      AND l.c_material_info <= 0
)
SELECT inspection_lot_num, part_num, part_content AS "Reminder"
FROM clob_parts
ORDER BY inspection_lot_num, part_num;
END TEST1;

该方案将大CLOB拆分为4000字节/块的多行记录，应用端按part_num顺序拼接即可还原完整内容。

方案3：扩展VARCHAR2长度（仅适用于12c+） #

如果数据库是Oracle 12c或更高版本，可设置MAX_STRING_SIZE=EXTENDED，将SQL层VARCHAR2最大长度扩展到32767字节，能缓解部分场景的长度问题，但仍无法处理超过32767字节的CLOB内容。

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