怎样可以将oracle数据库里的表导出xml格式文档？由用户指定从哪些表中导出，所以表名为变量。
------解决思路----------------------
创建一个dir，用动态sql拼游标，然后xmldom生成xml文件导出。大体上是这个思路。
------解决思路----------------------
-- 给你一个现成的，表名传入时，要用大写。
SQL> create directory xmlfiledir as 'c:\' ;
目录已创建。
SQL> create user test identified by test default tablespace users ;
用户已创建。
SQL> grant connect , resource , dba to test ;
授权成功。
SQL> grant read , write on directory xmlfiledir to test ;
授权成功。
SQL> conn test/test
已连接。
SQL> create table x as select * from dba_objects where rownum<10;
表已创建。
SQL> create or replace procedure gen_xml_bytname(p_tname varchar2)
2 as
3 type type_cur is ref cursor;
4 cur type_cur;
5 m_strsql varchar2(2000);
6 m_rowinfo varchar2(2000);
7 hfile Utl_File.file_type ;
8 dwCount int := 0 ;
9 begin
10 m_strsql := 'select ';
11 for x in (select cname from col where tname = p_tname) loop
12 m_strsql := m_strsql
------解决思路----------------------
'''<'
------解决思路----------------------
x.cname
------解决思路----------------------
'>''
------解决思路----------------------
'
------解决思路----------------------
x.cname
------解决思路----------------------
'
------解决思路----------------------
------解决思路----------------------
''</'
------解决思路----------------------
x.cname
------解决思路----------------------
'>''
------解决思路----------------------
chr(10)
------解决思路----------------------
' ;
13 end loop;
14 m_strsql := substr( m_strsql ,1,length(m_strsql) -2)
------解决思路----------------------
' as c from '
------解决思路----------------------
p_tname;
15 dbms_output.put_line(m_strsql) ;
16
17 open cur for m_strsql;
18 hfile := Utl_file.fopen('XMLFILEDIR',p_tname
------解决思路----------------------
'.xml','W') ;
19 utl_file.put_line(hfile,'<?xml version="1.0" encoding="utf-8"?> ');
20 utl_file.put_line(hfile,'<'
------解决思路----------------------
p_tname
------解决思路----------------------
'>');
21 loop
22 fetch cur into m_rowinfo;
23 exit when cur%notfound;
24 dwCount := dwCount + 1 ;
25 utl_file.put_line(hfile,'<rowno>'
------解决思路----------------------
dwCount
------解决思路----------------------
'</rowno>');
26 utl_file.put_line(hfile,m_rowinfo);
27 end loop;
28 utl_file.put_line(hfile,'<'
------解决思路----------------------
p_tname
------解决思路----------------------
'>');
29 utl_file.fclose_all;
30 close cur;
31 end;
32 /
过程已创建。
SQL> call gen_xml_bytname('X');
调用完成。
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开
C:\Documents and Settings\Administrator>type c:\x.xml
<?xml version="1.0" encoding="utf-8"?>
<X>
<rowno>1</rowno>
<OWNER>SYS</OWNER>
<OBJECT_NAME>ICOL$</OBJECT_NAME>
<SUBOBJECT_NAME></SUBOBJECT_NAME>
<OBJECT_ID>20</OBJECT_ID>
<DATA_OBJECT_ID>2</DATA_OBJECT_ID>
<OBJECT_TYPE>TABLE</OBJECT_TYPE>
<CREATED>02-4月 -10</CREATED>
<LAST_DDL_TIME>02-4月 -10</LAST_DDL_TIME>
<TIMESTAMP>2010-04-02:13:18:38</TIMESTAMP>
<STATUS>VALID</STATUS>
<TEMPORARY>N</TEMPORARY>
<GENERATED>N</GENERATED>
<SECONDARY>N</SECONDARY>
<NAMESPACE>1</NAMESPACE>
<EDITION_NAME></EDITION_NAME>
...................
...................
<rowno>9</rowno>
<OWNER>SYS</OWNER>
<OBJECT_NAME>I_CDEF2</OBJECT_NAME>
<SUBOBJECT_NAME></SUBOBJECT_NAME>
<OBJECT_ID>54</OBJECT_ID>
<DATA_OBJECT_ID>54</DATA_OBJECT_ID>
<OBJECT_TYPE>INDEX</OBJECT_TYPE>
<CREATED>02-4月 -10</CREATED>
<LAST_DDL_TIME>02-4月 -10</LAST_DDL_TIME>
<TIMESTAMP>2010-04-02:13:18:38</TIMESTAMP>
<STATUS>VALID</STATUS>
<TEMPORARY>N</TEMPORARY>
<GENERATED>N</GENERATED>
<SECONDARY>N</SECONDARY>
<NAMESPACE>4</NAMESPACE>
<EDITION_NAME></EDITION_NAME>
<X>
C:\Documents and Settings\Administrator>