DBMS_XMLGEN: SAve SQL query result in XML

DBMS_XMLGEN is a useful Oracle package that gives you a query result in XML file. Couple of days ago I have got a task to export all tables of an schema in different XML files. Here  is the PL/SQL code


grant read,write on directory DUMP_DIR to scott;
grant execute on DBMS_XMLGEN to scott;
grant execute on utl_file to scott;

set serveroutput on;
set echo on;
set timing on;
spool export_schema_xml.log;
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  l_output utl_file.file_type;
  l_amount NUMBER default 4000;
  l_offset NUMBER(38) default 1;
  l_length NUMBER(38);
  sqltext VARCHAR2 (4000 CHAR);
  result CLOB;
  l_buffer VARCHAR2 (4000 CHAR);
BEGIN
FOR x IN (SELECT x.table_name FROM user_tables x)
LOOP     
  l_offset :=1;
  sqltext := 'SELECT * FROM '||x.table_name;
  qryCtx :=  dbms_xmlgen.newContext (sqltext);
  DBMS_XMLGEN.SETCONVERTSPECIALCHARS (qryCtx,FALSE);
  result :=  DBMS_XMLGEN.getXML(qryCtx);
  l_output := utl_file.fopen('DUMP_DIR', x.table_name||'.xml', 'w', 32760);
  l_length:=nvl(dbms_lob.getlength(result),0);
  WHILE ( l_offset < l_length AND l_length > 0 )
  LOOP
    DBMS_LOB.READ (result, l_amount, l_offset, l_buffer);
    utl_file.put (l_output, l_buffer);
    utl_file.fflush(l_output);
      utl_file.fflush(l_output);
    l_offset := l_offset + l_amount;
  END LOOP;
  utl_file.fflush(l_output);
  utl_file.fclose(l_output);
  DBMS_XMLGEN.CLOSECONTEXT (qryCtx);
  dbms_output.put_line(x.table_name||' Data Exported');
END LOOP;
END;
/

spool off;