导出表数据为xml过程
一个过程测试了下,9M的小表导出花了13s,导出文件35M,大表没有测过。
过程导出之前需要先创建个directory. 并授权给执行过程的用户.
SQL> select count(1) from dkf_dong.myob;
COUNT(1)
----------
70791
SQL>
SQL> select sum(bytes)/1024/1024 MB from dba_segmentswhere segment_name='MYOB';
MB
----------
9
SQL> set timing on
SQL>
SQL> DECLARE
2 v_filename Varchar2(50) := 'export_xml_table.xml';
3 xml_str clob;
4 xml_file utl_file.file_type;
5 offset number;
6 buffer varchar2(32767);
7 buffer_size number;
8 BEGIN
9 offset := 1;
10 buffer_size := 3000;
11 xml_file :=utl_file.fopen('XML_DIR', v_filename, 'A');
12 xml_str :=dbms_xmlquery.getxml('select * fromdkf_dong.myob'); ------要导出的表数据
13 while (offset <dbms_lob.getlength(xml_str)) loop
14 buffer :=dbms_lob.substr(xml_str, buffer_size, offset);
15 utl_file.put(xml_file, buffer);
16 utl_file.fflush(xml_file);
17 offset :=offset + buffer_size;
18 end loop;
19 utl_file.fclose(xml_file);
20 END;
21 /
PL/SQL procedure successfully completed
Executed in 13.478 seconds
SQL>