Oracle：不转换为CLOB如何移除BLOB中的无效字符？

问题背景 #

现有Oracle查询用于将BLOB转换为CLOB，但转换后结果包含无意义的0值字符：

WITH FUNCTION GETCONTENTS(BLOB_IN IN BLOB) RETURN CLOB IS
    CLOBOUT CLOB; CLOBDLT CLOB; LDESTOFF NUMBER := 1; LSOUROFF NUMBER := 1; LLANGOFF NUMBER := DBMS_LOB.DEFAULT_LANG_CTX; LWARNING NUMBER;
    BEGIN
        DBMS_LOB.CREATETEMPORARY(CLOBOUT, TRUE);
        DBMS_LOB.CONVERTTOCLOB(CLOBOUT, BLOB_IN, DBMS_LOB.LOBMAXSIZE, LDESTOFF, LSOUROFF, NLS_CHARSET_ID('WE8ISO8859P1'), LLANGOFF, LWARNING);
        RETURN CLOBOUT;
    END;
SELECT DUMP(DBMS_LOB.SUBSTR(GETCONTENTS(GDTXFT), 50, 1)) FROM PRODDTA.F00165
    WHERE GDOBNM = 'GT4101' AND GDTXKY = 22;

尝试用TRANSLATE移除0值时触发ORA-00905错误，错误脚本如下：

WITH FUNCTION GETCONTENTS(BLOB_IN IN BLOB) RETURN CLOB IS
    CLOBOUT CLOB; CLOBDLT CLOB; LDESTOFF NUMBER := 1; LSOUROFF NUMBER := 1; LLANGOFF NUMBER := DBMS_LOB.DEFAULT_LANG_CTX; LWARNING NUMBER;
    BEGIN
        DBMS_LOB.CREATETEMPORARY(CLOBOUT, TRUE);
        DBMS_LOB.CONVERTTOCLOB(CLOBOUT, BLOB_IN, DBMS_LOB.LOBMAXSIZE, LDESTOFF, LSOUROFF, NLS_CHARSET_ID('WE8ISO8859P1'), LLANGOFF, LWARNING);
        SELECT TRANSLATE(CLOUBOUT, '', '') INTO CLOBOUT FROM DUAL;
    RETURN CLOBOUT;
END;
SELECT DUMP(DBMS_LOB.SUBSTR(GETCONTENTS(GDTXFT), 50, 1)) FROM PRODDTA.F00165
    WHERE GDOBNM = 'GT4101' AND GDTXKY = 22;

错误原因分析 #

• 变量名拼写错误：CLOUBOUT应为CLOBOUT
• TRANSLATE函数用法错误：该函数要求至少3个参数，TRANSLATE(CLOBOUT, '', '')不符合语法，需明确指定要移除的目标字符（即chr(0)）

解决方案 #

方案1：在BLOB阶段直接过滤0值字符 #

转换为CLOB前先处理BLOB，移除所有对应0值的字节（WE8ISO8859P1编码中chr(0)对应字节0x00）：

WITH FUNCTION GETCONTENTS(BLOB_IN IN BLOB) RETURN CLOB IS
    CLEAN_BLOB BLOB;
    CLOBOUT CLOB;
    LDESTOFF NUMBER := 1;
    LSOUROFF NUMBER := 1;
    LLANGOFF NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
    LWARNING NUMBER;
    BLOB_LEN NUMBER;
    BUFFER RAW(32767);
    POS NUMBER := 1;
BEGIN
    -- 初始化临时BLOB存储清理后的数据
    DBMS_LOB.CREATETEMPORARY(CLEAN_BLOB, TRUE);
    
    BLOB_LEN := DBMS_LOB.GETLENGTH(BLOB_IN);
    
    -- 遍历BLOB，过滤掉0x00字节
    WHILE POS <= BLOB_LEN LOOP
        DBMS_LOB.READ(BLOB_IN, LEAST(32767, BLOB_LEN - POS + 1), POS, BUFFER);
        -- 移除缓冲区中的0x00字节
        BUFFER := REPLACE(BUFFER, UTL_RAW.CAST_TO_RAW(CHR(0)), NULL);
        -- 将清理后的缓冲区写入临时BLOB
        DBMS_LOB.WRITEAPPEND(CLEAN_BLOB, UTL_RAW.LENGTH(BUFFER), BUFFER);
        POS := POS + 32767;
    END LOOP;
    
    -- 将清理后的BLOB转换为CLOB
    DBMS_LOB.CREATETEMPORARY(CLOBOUT, TRUE);
    DBMS_LOB.CONVERTTOCLOB(CLOBOUT, CLEAN_BLOB, DBMS_LOB.LOBMAXSIZE, LDESTOFF, LSOUROFF, NLS_CHARSET_ID('WE8ISO8859P1'), LLANGOFF, LWARNING);
    
    -- 释放临时BLOB资源
    DBMS_LOB.FREETEMPORARY(CLEAN_BLOB);
    
    RETURN CLOBOUT;
END;
SELECT DUMP(DBMS_LOB.SUBSTR(GETCONTENTS(GDTXFT), 50, 1)) FROM PRODDTA.F00165
WHERE GDOBNM = 'GT4101' AND GDTXKY = 22;

方案2：修正TRANSLATE用法在CLOB阶段移除0值 #

如果更倾向于转换后处理，修正TRANSLATE语法并修复变量拼写错误：

WITH FUNCTION GETCONTENTS(BLOB_IN IN BLOB) RETURN CLOB IS
    CLOBOUT CLOB;
    LDESTOFF NUMBER := 1;
    LSOUROFF NUMBER := 1;
    LLANGOFF NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
    LWARNING NUMBER;
BEGIN
    DBMS_LOB.CREATETEMPORARY(CLOBOUT, TRUE);
    DBMS_LOB.CONVERTTOCLOB(CLOBOUT, BLOB_IN, DBMS_LOB.LOBMAXSIZE, LDESTOFF, LSOUROFF, NLS_CHARSET_ID('WE8ISO8859P1'), LLANGOFF, LWARNING);
    
    -- 移除CLOB中的chr(0)字符，直接赋值无需SELECT FROM DUAL
    CLOBOUT := TRANSLATE(CLOBOUT, CHR(0) || CLOBOUT, CLOBOUT);
    
    RETURN CLOBOUT;
END;
SELECT DUMP(DBMS_LOB.SUBSTR(GETCONTENTS(GDTXFT), 50, 1)) FROM PRODDTA.F00165
WHERE GDOBNM = 'GT4101' AND GDTXKY = 22;

说明：TRANSLATE(CLOBOUT, CHR(0) || CLOBOUT, CLOBOUT)通过将chr(0)映射为空字符，实现移除所有0值字符的效果。

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