Oracle PL/SQL读取大hexBinary XML节点报错ORA-01706求助

问题根源 #

直接通过XMLTABLE提取大文本节点时，Oracle默认将节点内容转为VARCHAR2类型，当内容长度超过4000字符就会触发ORA-01706错误；直接指定CLOB/BLOB类型时，因XML路径返回的原始类型与目标类型不兼容，会报ORA-00932类型不匹配错误。

修正方案 #

1. 调整XMLTABLE游标定义，显式指定CLOB类型并转换节点内容

修改游标中的CONTENT字段定义，用XMLCAST将XML节点内容转为CLOB，避免默认类型限制：

CURSOR Cur_XML(pXML XMLTYPE) IS
  select *
    from XMLTABLE('//<path>' PASSING pXML COLUMNS
                  NODE1     NUMBER PATH '<pathofnode1>',
                  NODE2     VARCHAR2(4000) PATH '<pathofnode2>' DEFAULT NULL,
                  MEDIATYPE VARCHAR2(255) PATH './mediaType' DEFAULT NULL,
                  CONTENT   CLOB PATH XMLCAST(./content AS CLOB) DEFAULT NULL
                 );

2. 批量收集逻辑优化

原批量收集代码无需修改，但建议将limit值从120000调小（比如1000），避免大内存占用：

TYPE tCur_XML is table of Cur_XML%ROWTYPE index by pls_integer;
vtCur_XML tCur_XML;

open Cur_XML(pXML => your_xml_data);
fetch Cur_XML bulk collect into vtCur_XML limit 1000;
close Cur_XML;

3. 将hexBinary格式的CLOB转为BLOB并写入文件

通过分段读写处理大LOB，避免内存溢出，完成后将文件路径存入数据库：

FOR i IN 1..vtCur_XML.COUNT LOOP
  IF vtCur_XML(i).CONTENT IS NOT NULL THEN
    DECLARE
      v_raw RAW(32767);
      v_blob BLOB;
      v_file UTL_FILE.FILE_TYPE;
      v_chunk_size CONSTANT PLS_INTEGER := 32767;
      v_offset PLS_INTEGER := 1;
      v_length PLS_INTEGER;
      v_file_name VARCHAR2(255) := 'file_' || vtCur_XML(i).NODE1 || '.dat';
      v_file_path VARCHAR2(500) := '/your/assigned/dir/' || v_file_name; -- 替换为实际目录
    BEGIN
      DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
      -- 分段将CLOB格式的hexBinary转为RAW并写入BLOB
      v_length := DBMS_LOB.GETLENGTH(vtCur_XML(i).CONTENT);
      WHILE v_offset <= v_length LOOP
        DBMS_LOB.READ(vtCur_XML(i).CONTENT, v_chunk_size, v_offset, v_raw);
        DBMS_LOB.WRITEAPPEND(v_blob, UTL_RAW.LENGTH(v_raw), v_raw);
        v_offset := v_offset + v_chunk_size;
      END LOOP;
      -- 写入文件（需确保UTL_FILE目录已配置并授权）
      v_file := UTL_FILE.FOPEN('YOUR_DB_DIRECTORY', v_file_name, 'WB');
      v_offset := 1;
      v_length := DBMS_LOB.GETLENGTH(v_blob);
      WHILE v_offset <= v_length LOOP
        DBMS_LOB.READ(v_blob, v_chunk_size, v_offset, v_raw);
        UTL_FILE.PUT_RAW(v_file, v_raw, TRUE);
        v_offset := v_offset + v_chunk_size;
      END LOOP;
      UTL_FILE.FCLOSE(v_file);
      DBMS_LOB.FREETEMPORARY(v_blob);
      -- 保存文件路径到数据库表
      INSERT INTO your_target_table (node1_id, file_path)
      VALUES (vtCur_XML(i).NODE1, v_file_path);
    EXCEPTION
      WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_file) THEN
          UTL_FILE.FCLOSE(v_file);
        END IF;
        IF DBMS_LOB.ISTEMPORARY(v_blob) = 1 THEN
          DBMS_LOB.FREETEMPORARY(v_blob);
        END IF;
        RAISE;
    END;
  END IF;
END LOOP;
COMMIT;

注意事项 #

• 需由DBA创建并授权UTL_FILE使用的数据库目录（用CREATE DIRECTORY和GRANT READ, WRITE ON DIRECTORY ... TO your_user）
• 分段处理大LOB时，v_chunk_size建议设为32767（RAW类型的最大长度）
• 若hexBinary内容包含非ASCII字符，需调整字符集参数（上述示例用US7ASCII，适用于标准十六进制字符）

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