Oracle中CONVERTTOCLOB转换后字符间出现虚假空值的问题及解决

原因分析 #

字符间出现虚假空值（零值）的核心问题是字符集不匹配：

• 你在转换时指定了单字节字符集WE8ISO8859P1，但BLOB字段GDTXFT中实际存储的是**双字节字符集（如UTF-16）**内容。
• 双字节字符集中，ASCII范围内的字符会以[字符编码][0]的双字节形式存储，用单字节字符集转换时，会把每个双字节中的0字节解析为单独的空字符，最终导致输出字符间出现零值。

解决方法 #

1. 修正转换字符集
   确认BLOB实际存储的字符集（常见的UTF-16对应Oracle字符集AL16UTF16），修改CONVERTTOCLOB中的字符集参数：

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);
        -- 替换为BLOB实际对应的字符集，此处以AL16UTF16为例
        DBMS_LOB.CONVERTTOCLOB(CLOBOUT, BLOB_IN, DBMS_LOB.LOBMAXSIZE, LDESTOFF, LSOUROFF, NLS_CHARSET_ID('AL16UTF16'), LLANGOFF, LWARNING);
        RETURN CLOBOUT;
    END;
SELECT JSON_OBJECT(TRIM(GDGTITNM) VALUE GETCONTENTS(GDTXFT) FORMAT JSON) FROM PRODDTA.F00165
    WHERE GDOBNM = 'GT4101' AND GDTXKY = 22;

2. 确认BLOB真实字符集
   如果不确定字符集，可通过DUMP函数查看BLOB的十六进制内容：

SELECT DUMP(GDTXFT, 16) FROM PRODDTA.F00165 WHERE GDOBNM = 'GT4101' AND GDTXKY = 22;

• 若输出中ASCII字符显示为4100（如字母A），则是UTF-16LE编码，对应AL16UTF16；
• 若显示为0041，则是UTF-16BE编码，同样可使用AL16UTF16（Oracle会自动处理字节序）。

3. 解决REPLACE报错问题
   之前使用REPLACE触发ORA-00905错误，是因为语法位置或写法错误。若修正字符集后仍有残留零值，可在RETURN前添加替换逻辑：

-- 在RETURN CLOBOUT;之前添加
CLOBOUT := REPLACE(CLOBOUT, CHR(0));

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