The following operations are successful in Oracle9i and 10g.
Step 1: create a directory object for storing XML files
SQL>createorreplacedirectoryexp_diras'/opt';/ |
-- Note that the directory must have the write permission of the oracle user.
Step 2: Create a master process for generating xml files
SQL>createorreplaceproceduretable2xml(result inoutnocopyclob,
filenameinvarchar2)is
xmlstr varchar2(32767);
line varchar2(2000);
fhandle utl_file.file_type;
begin
fhandle := utl_file.fopen('EXP_DIR', filename,'w');
xmlstr := dbms_lob.substr(result,32767);
loop
exitwhenxmlstrisnull;
line := substr(xmlstr,1, instr(xmlstr, chr(10)) -1);
dbms_output.put_line('| '|| line);
utl_file.put_line(fhandle, line);
xmlstr := substr(xmlstr, instr(xmlstr, chr(10)) +1);
endloop;
utl_file.fclose(fhandle);
end;
/ |
Step 3: call this process
declare
queryctx dbms_xmlquery.ctxType;
result clob;
begin
queryctx := dbms_xmlquery.newContext('select user_id,user_name from lq_xml_test'); -- SQL statement for storing the query record set
Result: = dbms_xmlquery.getXML (queryctx );
Table2xml (result, 'table2xml. xml ');
Dbms_xmlquery.closeContext (queryctx );
End;
/ |
- Explain several paging query statements of Oracle
- Oracle database backup and recovery
- Oracle DBA responsibilities and daily work analysis
