create or replace procedure test_xmldemo
is

my clob;
queryCtx DBMS_XMLquery.ctxType;
out_file utl_file.file_type;
buffer varchar2(2100);
amount number := 2000;
offset number := 1;
fname varchar2(17);
--out_dir Directories;
begin
 select to_char(sysdate,'yyyymmddhh24missss') into fname from dual;
 out_file := utl_file.fopen('k:\test', fname||'.xml', 'W');
 queryCtx := DBMS_XMLQuery.newContext('select * from dev_khxx');
 select DBMS_XMLQuery.getXML(queryCtx) into my from dual;
 Loop
      DBMS_LOB.READ(my,amount,offset,buffer);
      utl_file.put(out_file, buffer);
      offset := offset + amount;
  End Loop;
Exception
   when NO_DATA_FOUND then
      utl_file.fflush(out_file);
      utl_file.fclose(out_file);
      return;
end test_xmldemo;
/