Generate a XML file from Oracle Database

Grant dba to scott;

Connect scott/tiger;

CREATE OR REPLACE DIRECTORY
XML_DIR AS
'D:\';

CREATE OR REPLACE PROCEDURE Generate_XML_file ( p_filename in varchar2 default 'Dept',
p_dir in varchar2 default 'XML_DIR'
) IS
v_OutFile utl_file.file_type;
v_value varchar2(2000);
v_QryCtx DBMS_XMLQuery.ctxType;
v_Xml CLOB;
v_More BOOLEAN := TRUE;
v_SqlQuery varchar2(2000):='Select * from dept';
v_RowsetTag Varchar2(200) :='Dept';
v_RowTag varchar2(200) :='Dept';

BEGIN


v_OutFile := utl_file.fopen( p_dir, p_filename||'.xml', 'w' );
v_More := TRUE;
v_QryCtx := DBMS_XMLQuery.newContext(v_SqlQuery);
DBMS_XMLQuery.setRowsetTag(v_QryCtx, v_RowsetTag);
DBMS_XMLQuery.setRowTag(v_QryCtx, v_RowTag);
v_Xml := DBMS_XMLQuery.GetXML(v_QryCtx);
DBMS_XMLQuery.closeContext(v_QryCtx);

while v_more loop
utl_File.Put(v_OutFile, Substr(v_Xml, 1, 32767));
if length(v_Xml) > 32767 then
v_Xml := substr(v_Xml, 32768);
else
v_More := FALSE;
end if;
end loop;

utl_file.fclose( v_OutFile );

Exception
when others then
utl_file.fclose( v_OutFile );
End;

--========================
exec Generate_XML_file;
--========================