oracle用plsql将sql查询的所有数据导出为xml

第一步需要先建一个oracle directory。

create directory TEMP_DIR as '/home/oracle/temp';

declare
  xml_str            clob;
  xml_file           Utl_File.file_type;
  offset             NUMBER              := 1;
  buffer             varchar2(4000);
  buffer_size        number              := 2000;
begin
  xml_file := Utl_File.fopen('TEMP_DIR','l360_lookup_values.xml','W');
  xml_str := DBMS_XMLGEN.getXML('select * from fnd_lookup_values where lookup_type like ''L360%''');
  while(offset < dbms_lob.getlength(xml_str))
  loop
     buffer := dbms_lob.substr(xml_str,buffer_size,offset);
     utl_file.put(xml_file,buffer);
     utl_file.fflush(xml_file);
     offset := offset + buffer_size;
  end loop;
  utl_file.fclose(xml_file);
  dbms_lob.freetemporary(xml_str);
end;

大概代码是这样的，可以封装为一个procedure。DBMS_XMLGEN这个包可以将一个sql查询的结果转化为xml格式的数据。

由于没有api可以直接将clob写到文件系统里，所有需要先将clob逐次读到一个buffer里，然后通过utl_file将数据写到文件系统。

需要注意的是buffer_size不能跟buffer的长度一样大，因为如果有中文这种双字节的字符的时候，就会出现buffer大小不够的错误。还有需要调用utl_file.fflush。因为如果utl_file.put调用使缓冲区的字符达到了32767以后，就会报缓冲区不够大的错误。dbms_lob.freetemporary这个存储过程是否一定要调用，暂时还不是很清楚，不知道oracle会不会自动垃圾回收。最好还是调用一下吧，作用是释放clob占用的资源。