如何将Oracle数据库中的数据导出为xml文件？？急！

francis_008 2003-10-22 03:05:03

...全文

159 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

huangcaibing 2004-03-05

打赏
举报

写个存储过程：

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.
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;

pinghero 2003-12-29