HOW TO GENERATE xml FROM ORACLE TABLE AND SAVE TO HARD DRIVE

HOW TO GENERATE xml FROM ORACLE TABLE AND SAVE TO HARD DRIVE
--------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE BEFTN.dpr_generate_FHR_6 ( p_filename in varchar2
default 'FHR',
p_dir in varchar2
default 'MY_XML'
) IS

v_OutFile utl_file.file_type;
v_value varchar2(2000);
v_QryCtx DBMS_XMLQuery.ctxType;
v_Xml CLOB;
v_More BOOLEAN := TRUE;


--exec BEFTN.dpr_generate_FHR_4

---create directory MY_XML AS 'D:\MY_XML\';


BEGIN
v_OutFile := utl_file.fopen( p_dir, p_filename||'.xml', 'w' );

utl_file.put_line(v_OutFile,'');
utl_file.put_line(v_OutFile,' ');
---------------------------------------------------------------------------------
for i in (select table_name from OCE where table_level=2 order by sl_id)
loop

if i.table_name = 'BEFTN_FHR' then
v_More := TRUE;
v_QryCtx := DBMS_XMLQuery.newContext('SELECT PRIORITYCODE,
IMMEDIATEDESTINATION ,
IMMEDIATEORIGIN ,
CREATIONDATE ,
CREATEIONTIME,
FILEIDMODIFIER ,
FORMATCODE,
IMMEDIATEDESTINATIONNAME,
IMMEDIATEORIGINNAME,
REFERENCECODE
FROM '||i.table_name||'
WHERE rownum = 1');
DBMS_XMLQuery.setRowsetTag(v_QryCtx, 'FHR');
DBMS_XMLQuery.setRowTag(v_QryCtx, 'FHR');
v_Xml := DBMS_XMLQuery.GetXML(v_QryCtx);
DBMS_XMLQuery.closeContext(v_QryCtx);

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

END LOOP;


utl_file.put_line(v_OutFile,' ');
utl_file.fclose( v_OutFile );

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