Oracle查询BLOB字段遇ORA-22835，用转换函数仍报ORA-22921怎么解决？

操作包含BLOB字段的表时，执行以下SQL：

SELECT id FROM table WHERE blob_column LIKE '%something%';

触发错误：

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 16713, maximum: 4000)

参考方案创建了转换函数：

CREATE OR REPLACE FUNCTION VC2CLOB_FROM_BLOB(B BLOB)
RETURN CLOB IS
    c CLOB;
    n NUMBER;
BEGIN
    IF (b IS NULL) THEN 
        RETURN NULL;
    END IF;
    IF (LENGTH(b) = 0) THEN
        RETURN EMPTY_CLOB();
    END IF;
    DBMS_LOB.CREATETEMPORARY(c, TRUE);
    n := 1;
    WHILE (n + 32767 <= LENGTH(b)) LOOP
        DBMS_LOB.WRITEAPPEND(c, 32767, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, 32767, n)));
        n := n + 32767;
    END LOOP;
    DBMS_LOB.WRITEAPPEND(c, LENGTH(b) - n + 1, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, LENGTH(b) - n + 1, n)));
    RETURN c;
END;
/

执行查询时仍报错：

ORA-22921: length of input buffer is smaller than amount requested
ORA-06512: at "SYS.DBMS_LOB", line 1163 ORA-06512: at
"DATABASE.VC2CLOB_FROM_BLOB", line 18

1. 长度不匹配：函数中DBMS_LOB.WRITEAPPEND指定的写入长度是BLOB的字节数（如32767），但UTL_RAW.CAST_TO_VARCHAR2转换RAW为字符串时，若BLOB包含不符合当前字符集的字节（比如二进制空值、无效编码字节），转换后的字符串长度会小于原RAW的字节数，导致写入长度大于实际可用的字符长度，触发ORA-22921。
2. 字符集忽略：原函数未指定字符集，直接转换可能导致编码转换异常，进一步加剧长度不匹配问题。
3. 循环逻辑缺陷：LENGTH(b)返回的是BLOB的字节数，而CLOB存储的是字符数，当字符集为多字节（如UTF8）时，字节数和字符数不相等，循环中的长度计算会出现偏差。

方法一：使用官方转换函数替代自定义逻辑 #

Oracle提供了DBMS_LOB.CONVERTTOCLOB函数，可以安全地将BLOB转换为CLOB，自动处理字符集和长度问题，无需手动循环：

CREATE OR REPLACE FUNCTION BLOB_TO_CLOB(B BLOB, P_CHARSET VARCHAR2 := 'AL32UTF8')
RETURN CLOB IS
    C CLOB;
    DEST_OFFSET NUMBER := 1;
    SRC_OFFSET NUMBER := 1;
    BLOB_CSID NUMBER := NLS_CHARSET_ID(P_CHARSET);
    LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
    WARNING NUMBER;
BEGIN
    IF B IS NULL THEN
        RETURN NULL;
    END IF;
    DBMS_LOB.CREATETEMPORARY(C, TRUE);
    DBMS_LOB.CONVERTTOCLOB(
        DEST_LOB => C,
        SRC_BLOB => B,
        AMOUNT => DBMS_LOB.LOBMAXSIZE,
        DEST_OFFSET => DEST_OFFSET,
        SRC_OFFSET => SRC_OFFSET,
        BLOB_CSID => BLOB_CSID,
        LANG_CONTEXT => LANG_CONTEXT,
        WARNING => WARNING
    );
    RETURN C;
END;
/

使用该函数查询：

SELECT id FROM table WHERE BLOB_TO_CLOB(blob_column) LIKE '%something%';

方法二：修复自定义函数的长度问题 #

如果坚持使用自定义逻辑，需动态获取转换后的字符串长度，而非固定使用BLOB的字节数：

CREATE OR REPLACE FUNCTION VC2CLOB_FROM_BLOB(B BLOB)
RETURN CLOB IS
    c CLOB;
    n NUMBER;
    v_str VARCHAR2(32767);
BEGIN
    IF (b IS NULL) THEN 
        RETURN NULL;
    END IF;
    IF (LENGTH(b) = 0) THEN
        RETURN EMPTY_CLOB();
    END IF;
    DBMS_LOB.CREATETEMPORARY(c, TRUE);
    n := 1;
    WHILE (n + 32767 <= LENGTH(b)) LOOP
        v_str := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, 32767, n));
        DBMS_LOB.WRITEAPPEND(c, LENGTH(v_str), v_str);
        n := n + 32767;
    END LOOP;
    v_str := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, LENGTH(b) - n + 1, n));
    DBMS_LOB.WRITEAPPEND(c, LENGTH(v_str), v_str);
    RETURN c;
END;
/

注意：此方法仍可能因BLOB中存在无效字符导致转换异常，建议优先使用方法一。

额外优化：避免全表扫描 #

如果表数据量较大，直接在WHERE子句中调用函数会触发全表扫描，建议添加基于函数的索引：

CREATE INDEX idx_blob_clob ON table(BLOB_TO_CLOB(blob_column));

创建索引后，查询性能会显著提升。

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