Create output XML data files from oracle with dbms_xmlgen

The below code will generate XML format output, just plug in any SQL and it will work without further development.

This is based on three articles by other authors:

  • Tim Hall – but Tim’s code has a bug that stops it outputting after the first 32k of data.
  • Tom Kyte – the fix for that bug.
  • Steve Karam – code to nest XML data.

Performance is kept optimal by using set based SQL statements without cursor loops, and by using utl_file.put to write in 32k chunks rather than line-by-line of dbms_output.put_line or utl_file.put_line.

Before generating XML, ask if this is the best approach for transferring the data. Database to database copying is better done across database link, if possible.

Code:

CREATE OR REPLACE PROCEDURE myxmlproc AS
/*
 * Procedure:   myxmlproc
 * Purpose:     Creates XML data file from any SQL statements
 * Depends on:  Directory (formerly utl_file_dir) needs set up in database with permissions to
 *              allow writing to file with utl_file, e.g.:
 *              CREATE DIRECTORY mydir AS '/U01/oracle/mydir' ;
 *              GRANT READ , WRITE on mydir TO scott ;
 * Based on:    http://andrewfraserdba.com/2014/01/26/create-output-xml-data-files-from-oracle-with-dbms_xmlgen/
 *              http://www.oracle-base.com/articles/9i/xml-generation-9i.php#dbms_xmlgen and
 *              http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:744825627183
 *              http://www.dba-oracle.com/t_dbms_xmlgen.htm
 *
 * Version:     A00
 * Date:        26-Jan-2014
 * Author:      Andrew Fraser.
 * Comments:    First version.
 */
   l_ctx   DBMS_XMLGEN.ctxhandle ;
   l_file  UTL_FILE.file_type ;
   l_xml   CLOB ;
   l_more  BOOLEAN ;
   /*
    * Output code called after every SELECT, so keeping in private procedure to avoid duplication of coding.
    * Based on : http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:744825627183
    */
   PROCEDURE output_to_file AS
      l_amt NUMBER DEFAULT 32000 ;
      l_offset NUMBER DEFAULT 1 ;
      l_length NUMBER ;
   BEGIN
      /*
       * Create the XML document.
       */
      l_xml := DBMS_XMLGEN.getxml ( l_ctx )  ;
      DBMS_XMLGEN.closecontext ( l_ctx )  ;
      /*
       * Output XML document to file.
       */
      l_length := NVL ( DBMS_LOB.getlength ( l_xml ) , 0 ) ;
      WHILE ( l_offset < l_length )
      LOOP
         UTL_FILE.put ( l_file , DBMS_LOB.substr ( l_xml , l_amt , l_offset ) ) ;
         UTL_FILE.fflush ( l_file ) ;
         l_offset := l_offset + l_amt ;
      END LOOP ;
      UTL_FILE.new_line ( l_file ) ;
   END output_to_file ;

BEGIN
   /*
    * open file for writing
    */
   l_file := UTL_FILE.fopen ( '/U01/oracle/mydir' , 'myxmlproc.xml' , 'w' , 32760 )  ;

   /*
    * 1) Dept table
    * Create XML context.
    */
   l_ctx := DBMS_XMLGEN.newcontext ( q'[
   SELECT d.deptno
        , d.dname
        , d.loc
        , INITCAP ( d.loc ) location
     FROM dept d
    WHERE d.dname != 'Invalid'
    ORDER BY 1
   ]' ) ;
   /*
    * Set parameters to alter default Rowset and Row tag names.
    */
   DBMS_XMLGEN.setrowsettag ( l_ctx , 'Depts' ) ;
   DBMS_XMLGEN.setrowtag ( l_ctx , 'Dept' ) ;
   output_to_file ;

   /*
    * 2) Emp
    * Create XML context.
    */
   l_ctx := DBMS_XMLGEN.newcontext ( q'[
   SELECT e.*
     FROM emp e
    WHERE e.hiredate > TO_DATE ( '01-APR-1950' , 'DD-MON-YYYY' )
    ORDER BY e.empno
   ]' ) ;
   /*
    * Set parameters to alter default Rowset and Row tag names.
    */
   DBMS_XMLGEN.setrowsettag ( l_ctx , 'Emps' ) ;
   DBMS_XMLGEN.setrowtag ( l_ctx , 'Emp' ) ;
   output_to_file ;

   /*
    * 3) Nested XML of Dept and Emp together
    * Create XML context.
    */
   l_ctx := DBMS_XMLGEN.newcontext ( q'[
   SELECT d.deptno
        , d.dname
        , d.loc
        , INITCAP ( d.loc ) location
        , CURSOR (
          SELECT e.empno
               , e.ename
               , e.job
               , e.mgr
               , e.hiredate
               , e.sal
               , e.comm
            FROM emp e
           WHERE e.hiredate > TO_DATE ( '01-APR-1950' , 'DD-MON-YYYY' )
             AND e.deptno = d.deptno
          ) emp
     FROM dept d
    WHERE d.dname != 'Invalid'
    ORDER BY 1
   ]' ) ;
   /*
    * Set parameters to alter default Rowset and Row tag names.
    */
   DBMS_XMLGEN.setrowsettag ( l_ctx , 'Depts' ) ;
   DBMS_XMLGEN.setrowtag ( l_ctx , 'Dept' ) ;
   output_to_file ;

   UTL_FILE.fclose ( l_file )  ;

EXCEPTION
   WHEN OTHERS THEN
      UTL_FILE.fclose ( l_file )  ;
      RAISE ;   -- triggers a rollback, as well as writing more error info to screen
END myxmlproc ;
/
sho err
exec myxmlproc
host view /U01/oracle/mydir/myxmlproc.xml

Output:



 
  10
  ACCOUNTING
  NEW YORK
  New York
 
 
  20
  RESEARCH
  DALLAS
  Dallas
 
 
  30
  SALES
  CHICAGO
  Chicago
 
 
  40
  OPERATIONS
  BOSTON
  Boston
 




 
  7369
  SMITH
  CLERK
  7902
  17-DEC-80
  800
  20
 
 
  7499
  ALLEN
  SALESMAN
  7698
  20-FEB-81
  1600
  300
  30
 
 
  7521
  WARD
  SALESMAN
  7698
  22-FEB-81
  1250
  500
  30
 
 
  7566
  JONES
  MANAGER
  7839
  02-APR-81
  2975
  20
 
 
  7654
  MARTIN
  SALESMAN
  7698
  28-SEP-81
  1250
  1400
  30
 
 
  7698
  BLAKE
  MANAGER
  7839
  01-MAY-81
  2850
  30
 
 
  7782
  CLARK
  MANAGER
  7839
  09-JUN-81
  2450
  10
 
 
  7788
  SCOTT
  ANALYST
  7566
  19-APR-87
  3000
  20
 
 
  7839
  KING
  PRESIDENT
  17-NOV-81
  5000
  10
 
 
  7844
  TURNER
  SALESMAN
  7698
  08-SEP-81
  1500
  0
  30
 
 
  7876
  ADAMS
  CLERK
  7788
  23-MAY-87
  1100
  20
 
 
  7900
  JAMES
  CLERK
  7698
  03-DEC-81
  950
  30
 
 
  7902
  FORD
  ANALYST
  7566
  03-DEC-81
  3000
  20
 
 
  7934
  MILLER
  CLERK
  7782
  23-JAN-82
  1300
  10
 




 
  10
  ACCOUNTING
  NEW YORK
  New York
  
   
    7839
    KING
    PRESIDENT
    17-NOV-81
    5000
   
   
    7782
    CLARK
    MANAGER
    7839
    09-JUN-81
    2450
   
   
    7934
    MILLER
    CLERK
    7782
    23-JAN-82
    1300
   
  
 
 
  20
  RESEARCH
  DALLAS
  Dallas
  
   
    7566
    JONES
    MANAGER
    7839
    02-APR-81
    2975
   
   
    7788
    SCOTT
    ANALYST
    7566
    19-APR-87
    3000
   
   
    7902
    FORD
    ANALYST
    7566
    03-DEC-81
    3000
   
   
    7369
    SMITH
    CLERK
    7902
    17-DEC-80
    800
   
   
    7876
    ADAMS
    CLERK
    7788
    23-MAY-87
    1100
   
  
 
 
  30
  SALES
  CHICAGO
  Chicago
  
   
    7698
    BLAKE
    MANAGER
    7839
    01-MAY-81
    2850
   
   
    7499
    ALLEN
    SALESMAN
    7698
    20-FEB-81
    1600
    300
   
   
    7521
    WARD
    SALESMAN
    7698
    22-FEB-81
    1250
    500
   
   
    7654
    MARTIN
    SALESMAN
    7698
    28-SEP-81
    1250
    1400
   
   
    7844
    TURNER
    SALESMAN
    7698
    08-SEP-81
    1500
    0
   
   
    7900
    JAMES
    CLERK
    7698
    03-DEC-81
    950
   
  
 
 
  40
  OPERATIONS
  BOSTON
  Boston
  
  
 


January 26, 2014

Leave a Reply

Your email address will not be published. Required fields are marked *