分类: Oracle
2011-10-20 10:45:05
1.创建如下存储过程,注意将其中location =>'d:\work'之中的目录改为你本机的某个目录. create or replace procedure getXML(newContext_qry varchar2,rowSettag varchar2,rowTag varchar2,filename varchar2) is -- Input query string -- Input rowsetTag , the root tag -- Input row level tag -- Input file name -- Note make sure that before running this procedure, it is required that UTL_FILE_DIR must -- be set in init.ora file In this code it has been set to d:\work. begin declare qryCtx dbms_xmlgen.ctxHandle ; result clob; lob_length integer; read_amount integer; read_offset integer; buffer varchar2(100); loc varchar2(100) := 'usr_dir'; f_hand utl_file.file_type; Begin -- Setting up offset and no. of chars to be read in -- in one go from clob datatype. read_offset := 1; read_amount := 75; dbms_output.put_line('opening'); --Opening file f_hand := Utl_File.Fopen(location =>'d:\work', filename =>filename, open_mode =>'w', max_linesize => 32767); dbms_output.put_line('file open'); -- Creating new context qryCtx := dbms_xmlgen.newContext(newContext_qry); -- Defining Rowsettag DBMS_XMLGEN.setRowSetTag(qryCtx,rowSettag); -- Defining Rowtag DBMS_XMLGEN.setRowTag(qryCtx,rowTag); -- Generating XML and storing in an clob datatype result :=DBMS_XMLGEN.getXML(qryCtx); dbms_output.put_line('xml generated'); -- Getting the length of the data stored in Clob lob_length := dbms_lob.getlength(result); -- Reading data from clob variable and writng into file. while (lob_length > 0) loop dbms_lob.read(result,read_amount,read_offset,buffer); dbms_output.put_line('writing in file'); utl_file.put(f_hand,buffer); dbms_output.put_line('written'); read_offset := read_offset+read_amount; lob_length := lob_length-read_amount; if lob_length < read_amount then read_amount := lob_length; end if; end loop; utl_file.fclose(f_hand); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN RAISE_APPLICATION_ERROR(-20100,'Invalid Path'); WHEN UTL_FILE.INVALID_MODE THEN RAISE_APPLICATION_ERROR(-20101,'Invalid Mode'); WHEN UTL_FILE.INVALID_OPERATION then RAISE_APPLICATION_ERROR(-20102,'Invalid Operation'); WHEN UTL_FILE.INVALID_FILEHANDLE then RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle'); WHEN UTL_FILE.WRITE_ERROR then RAISE_APPLICATION_ERROR(-20104,'Write Error'); WHEN UTL_FILE.READ_ERROR then RAISE_APPLICATION_ERROR(-20105,'Read Error'); WHEN UTL_FILE.INTERNAL_ERROR then RAISE_APPLICATION_ERROR(-20106,'Internal Error'); WHEN OTHERS THEN UTL_FILE.FCLOSE(f_hand); end; end; / 2.修改init.ora参数文件中的参数utl_file_dir,将其值指定为和存储过程中location指定的目录,如D:\work,重新启动数据库。 3.登录后,执行类似如下语句 execute getXML('select * from tab','TAB','RECORD','shanfei.xml') 参数1:SQL语句 参数2:XML文件中将生成的根元素 参数3:XML文件中表格每行代表的元素 参数4:生成的xml文件名称 |
生成xml文件内容类似如下: ...................... ...................... |
作者:shanfei