PL/SQL表函数绑定变量问题及ORA-06502错误排查

问题背景 #

• 由于字符串字面量存在4000字节限制，编写了PL/SQL表函数patrik_split_clob，用于按逗号、冒号、分号或竖线分割CLOB数据并输出为列
• 原本考虑使用regexp_replace，但不确定该函数是否支持CLOB类型，希望了解更优的实现方式

遇到的问题 #

1. SQL Developer绑定变量异常 #

执行以下代码时，已通过exec为绑定变量赋值，但SQL Developer仍弹出输入框要求再次赋值：

variable patrik clob;
exec :patrik := 'abc,ghj,yut';

select * from patrik_split_clob(:patrik)

2. ORA-06502数值错误 #

运行函数时触发ORA-06502错误，具体信息如下：

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "PATRIK_SPLIT_CLOB", line 28
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: 发生了算术、数值、字符串、转换或约束错误。例如，尝试将NULL值赋值给声明为NOT NULL的变量，或尝试将大于99的整数赋值给声明为NUMBER(2)的变量时会触发此错误。
*Action: 修改数据、数据操作方式或变量声明，确保值不违反约束。

相关类型与函数定义代码：

CREATE OR REPLACE TYPE patrik_split_value AS OBJECT (value VARCHAR2(4000));
CREATE OR REPLACE TYPE patrik_split_table AS TABLE OF patrik_split_value;


CREATE OR REPLACE FUNCTION patrik_split_clob(p_clob CLOB)
RETURN patrik_split_table
AS
    v_result patrik_split_table := patrik_split_table();
    v_start_pos NUMBER := 1;
    v_end_pos NUMBER;
    v_length NUMBER := DBMS_LOB.GETLENGTH(p_clob);
BEGIN
    LOOP
        v_end_pos := DBMS_LOB.INSTR(p_clob, ',', v_start_pos);
        IF v_end_pos = 0 THEN
            v_end_pos := DBMS_LOB.INSTR(p_clob, ':', v_start_pos);
        END IF;
        IF v_end_pos = 0 THEN
            v_end_pos := DBMS_LOB.INSTR(p_clob, ';', v_start_pos);
        END IF;
        IF v_end_pos = 0 THEN
            v_end_pos := DBMS_LOB.INSTR(p_clob, '|', v_start_pos);
        END IF;
        IF v_end_pos = 0 THEN
            EXIT;
        END IF;
        v_result.EXTEND;
        v_result(v_result.COUNT) := patrik_split_value(DBMS_LOB.SUBSTR(p_clob, v_end_pos - v_start_pos, v_start_pos));
        v_start_pos := v_end_pos + 1;
    END LOOP;
    v_result.EXTEND;
    v_result(v_result.COUNT) := patrik_split_value(DBMS_LOB.SUBSTR(p_clob, v_length - v_start_pos + 1, v_start_pos));
    RETURN v_result;
END;

疑问 #

想排查ORA-06502错误的触发原因，是否与v_start_pos初始值设为1有关？

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