This discussion is archived
1 2 3 Previous Next 43 Replies Latest reply: Feb 6, 2013 12:40 PM by 987833 RSS

How to generate XML file with a particular shape from a Big table

987833 Newbie
Currently Being Moderated
#1 My environment
select * from V$VERSION;

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production     
PL/SQL Release 11.2.0.3.0 - Production                                           
CORE     11.2.0.3.0     Production                                                         
TNS for Linux: Version 11.2.0.3.0 - Production                                   
NLSRTL Version 11.2.0.3.0 - Production                                           
#2 What I’m trying to achieve
Generate a XML file on a file system by querying data of a relational table. The table has a big size:
select sum(bytes)/1024/1024/1024 gigabytes from DBA_SEGMENTS where SEGMENT_NAME = 'AR_BHVR_DIM' and owner = 'DTL';
-- 5.72735595703125 Gigabytes
The table has 67 columns and 11465828 rows

#3 Implementation
The XML file should have a namespace and an attribute.
DECLARE DOC CLOB;
BEGIN

    select 
          xmlserialize(DOCUMENT
            XMLELEMENT("root",
              XMLATTRIBUTES(SYSDATE AS "DataFeedDate"
                                   , 'http://www.abccompany.com' as "xmlns"),
              XMLAGG(    
          xmlelement("r", 
          xmlforest( 
              ROUND(AR_BHVR_KEY,4) as "AR_BHVR_KEY" ,
              ROUND(AR_KEY,4) as "AR_KEY" ,
              ROUND(AR_ACTVN_ST_KEY,4) as "AR_ACTVN_ST_KEY" ,
              AR_ACTVN_ST_CD as "AR_ACTVN_ST_CD" ,
              ROUND(AR_CMPLN_ST_KEY,4) AS "AR_CMPLN_ST_KEY" ,
              AR_CMPLN_ST_CD as "AR_CMPLN_ST_CD" ,
              ROUND(AR_MKT_SEG_KEY,4) as "AR_MKT_SEG_KEY" ,
              AR_MKT_SEG_CD as "AR_MKT_SEG_CD" ,
              ROUND(AR_RTLR_KEY,4) as "AR_RTLR_KEY" ,
              AR_RTLR_CD as "AR_RTLR_CD" ,
              ROUND(AR_ST_DT_KEY,4) as "AR_ST_DT_KEY" ,
              TO_CHAR(AR_ST_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "AR_ST_DT" ,
              ROUND(AR_ST_KEY,4) as "AR_ST_KEY" ,
              AR_ST_CD as "AR_ST_CD" ,
              ROUND(CNTRCT_BILL_CYCL_KEY,4) as "CNTRCT_BILL_CYCL_KEY" ,
              CNTRCT_BILL_CYCL_CD as "CNTRCT_BILL_CYCL_CD" ,
              ROUND(CNTRCT_EFF_DT_KEY,4) as "CNTRCT_EFF_DT_KEY" ,
              TO_CHAR(CNTRCT_EFF_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "CNTRCT_EFF_DT" ,
              ROUND(CNTRCT_PYMNT_TP_KEY,4) as "CNTRCT_PYMNT_TP_KEY" ,
              CNTRCT_PYMNT_TP_CD as "CNTRCT_PYMNT_TP_CD" ,
              ROUND(CNTRCT_MKT_SEG_KEY,4) as "CNTRCT_MKT_SEG_KEY" ,
              CNTRCT_MKT_SEG_CD AS "CNTRCT_MKT_SEG_CD" ,
              ROUND(ACTVN_OFFCR_KEY,4)                             as "ACTVN_OFFCR_KEY" ,
              ACTVN_OFFCR_CD as "ACTVN_OFFCR_CD" ,
              ROUND(EMPE_KEY,4) as "EMPE_KEY" ,
              EMPE_CD as "EMPE_CD" ,
              ROUND(RLN_OFFCR_KEY,4) as "RLN_OFFCR_KEY" ,
              RLN_OFFCR_CD as "RLN_OFFCR_CD" ,
              ROUND(NTW_TCHNLGY_KEY,4) as "NTW_TCHNLGY_KEY" ,
              NTW_TCHNLGY_CD as "NTW_TCHNLGY_CD" ,
              ROUND(PD_KEY,4) as "PD_KEY" ,
              PD_CD as "PD_CD" ,
              ROUND(PD_ALTRNTV_1_KEY,4) as "PD_ALTRNTV_1_KEY" ,
              PD_ALTRNTV_1_CD as "PD_ALTRNTV_1_CD" ,
              ROUND(PD_ALTRNTV_2_KEY,4) as "PD_ALTRNTV_2_KEY" ,
              PD_ALTRNTV_2_CD as "PD_ALTRNTV_2_CD" ,
              ROUND(PD_ALTRNTV_3_KEY,4) as "PD_ALTRNTV_3_KEY" ,
              PD_ALTRNTV_3_CD as "PD_ALTRNTV_3_CD" ,
              ROUND(PYMNT_ENTTY_KEY,4) as "PYMNT_ENTTY_KEY" ,
              PYMNT_ENTTY_CD as "PYMNT_ENTTY_CD" ,
              ROUND(PYMNT_PRCSSR_KEY,4) as "PYMNT_PRCSSR_KEY" ,
              PYMNT_PRCSSR_CD as "PYMNT_PRCSSR_CD" ,
              ROUND(RI_OWN_ST_KEY,4) as "RI_OWN_ST_KEY" ,
              RI_OWN_ST_CD as "RI_OWN_ST_CD" ,
              CNTRCT_DD as "CNTRCT_DD" ,
              SIM_DD as "SIM_DD" ,
              TO_CHAR(EFF_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "EFF_DT" ,
              TO_CHAR(PPN_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "PPN_DT" ,
              CNTRL_MD5_AR as "CNTRL_MD5_AR" ,
              CNTRL_MD5 as "CNTRL_MD5" ,
              ROUND(PRTBLTY_OPR_KEY,4) as "PRTBLTY_OPR_KEY" ,
              PRTBLTY_OPR_CD as "PRTBLTY_OPR_CD" ,
              ROUND(PRTBLTY_TP_KEY,4) as "PRTBLTY_TP_KEY" ,
              PRTBLTY_TP_CD as "PRTBLTY_TP_CD" ,
              ROUND(PRTBLTY_DT_KEY,4) as "PRTBLTY_DT_KEY" ,
              TO_CHAR(PRTBLTY_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "PRTBLTY_DT" ,
              ROUND(AR_BILL_ST_KEY,4) as "AR_BILL_ST_KEY" ,
              AR_BILL_ST_CD as "AR_BILL_ST_CD" ,
              ROUND(CNTRCT_END_DT_KEY,4) as "CNTRCT_END_DT_KEY" ,
              TO_CHAR(CNTRCT_END_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "CNTRCT_END_DT" ,
              ROUND(CSTMR_KEY,4) as "CSTMR_KEY" ,
              CSTMR_CD as "CSTMR_CD" ,
              ROUND(PRN_AR_KEY,4) as "PRN_AR_KEY" ,
              PRN_AR_CD as "PRN_AR_CD" ,
              ROUND(IP_USR_KEY,4) as "IP_USR_KEY" ,
              IP_USR_CD AS "IP_USR_CD" ,
              MSISDN_DD as "MSISDN_DD" )            
          )))
              AS CLOB )  INTO DOC
        from DTL.AR_BHVR_DIM 
  ;

  DBMS_XSLPROCESSOR.CLOB2FILE('<?xml version="1.0" encoding="iso-8859-1"?>' || DOC,
                            'TMP',
                            'AR_BHVR_DIM.XML'
                            , nls_charset_id('WE8ISO8859P1'));
END;
#4 Error message
The process was running about 20 hours and never finished, I had to stop it manually.

#5 Plan B
I saw on the Oracle XML DB forum a different implementation. It has a deficiency that it’s impossible to control the shape of the XML.

#6 PLAN B implementation
declare
    rc sys_refcursor;
begin
    open rc FOR SELECT * FROM (
    
    SELECT 
              ROUND(AR_BHVR_KEY,4) as "AR_BHVR_KEY" ,
              ROUND(AR_KEY,4) as "AR_KEY" ,
              ROUND(AR_ACTVN_ST_KEY,4) as "AR_ACTVN_ST_KEY" ,
              AR_ACTVN_ST_CD as "AR_ACTVN_ST_CD" ,
              ROUND(AR_CMPLN_ST_KEY,4) AS "AR_CMPLN_ST_KEY" ,
              AR_CMPLN_ST_CD as "AR_CMPLN_ST_CD" ,
              ROUND(AR_MKT_SEG_KEY,4) as "AR_MKT_SEG_KEY" ,
              AR_MKT_SEG_CD as "AR_MKT_SEG_CD" ,
              ROUND(AR_RTLR_KEY,4) as "AR_RTLR_KEY" ,
              AR_RTLR_CD as "AR_RTLR_CD" ,
              ROUND(AR_ST_DT_KEY,4) as "AR_ST_DT_KEY" ,
              TO_CHAR(AR_ST_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "AR_ST_DT" ,
              ROUND(AR_ST_KEY,4) as "AR_ST_KEY" ,
              AR_ST_CD as "AR_ST_CD" ,
              ROUND(CNTRCT_BILL_CYCL_KEY,4) as "CNTRCT_BILL_CYCL_KEY" ,
              CNTRCT_BILL_CYCL_CD as "CNTRCT_BILL_CYCL_CD" ,
              ROUND(CNTRCT_EFF_DT_KEY,4) as "CNTRCT_EFF_DT_KEY" ,
              TO_CHAR(CNTRCT_EFF_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "CNTRCT_EFF_DT" ,
              ROUND(CNTRCT_PYMNT_TP_KEY,4) as "CNTRCT_PYMNT_TP_KEY" ,
              CNTRCT_PYMNT_TP_CD as "CNTRCT_PYMNT_TP_CD" ,
              ROUND(CNTRCT_MKT_SEG_KEY,4) as "CNTRCT_MKT_SEG_KEY" ,
              CNTRCT_MKT_SEG_CD AS "CNTRCT_MKT_SEG_CD" ,
              ROUND(ACTVN_OFFCR_KEY,4)                             as "ACTVN_OFFCR_KEY" ,
              ACTVN_OFFCR_CD as "ACTVN_OFFCR_CD" ,
              ROUND(EMPE_KEY,4) as "EMPE_KEY" ,
              EMPE_CD as "EMPE_CD" ,
              ROUND(RLN_OFFCR_KEY,4) as "RLN_OFFCR_KEY" ,
              RLN_OFFCR_CD as "RLN_OFFCR_CD" ,
              ROUND(NTW_TCHNLGY_KEY,4) as "NTW_TCHNLGY_KEY" ,
              NTW_TCHNLGY_CD as "NTW_TCHNLGY_CD" ,
              ROUND(PD_KEY,4) as "PD_KEY" ,
              PD_CD as "PD_CD" ,
              ROUND(PD_ALTRNTV_1_KEY,4) as "PD_ALTRNTV_1_KEY" ,
              PD_ALTRNTV_1_CD as "PD_ALTRNTV_1_CD" ,
              ROUND(PD_ALTRNTV_2_KEY,4) as "PD_ALTRNTV_2_KEY" ,
              PD_ALTRNTV_2_CD as "PD_ALTRNTV_2_CD" ,
              ROUND(PD_ALTRNTV_3_KEY,4) as "PD_ALTRNTV_3_KEY" ,
              PD_ALTRNTV_3_CD as "PD_ALTRNTV_3_CD" ,
              ROUND(PYMNT_ENTTY_KEY,4) as "PYMNT_ENTTY_KEY" ,
              PYMNT_ENTTY_CD as "PYMNT_ENTTY_CD" ,
              ROUND(PYMNT_PRCSSR_KEY,4) as "PYMNT_PRCSSR_KEY" ,
              PYMNT_PRCSSR_CD as "PYMNT_PRCSSR_CD" ,
              ROUND(RI_OWN_ST_KEY,4) as "RI_OWN_ST_KEY" ,
              RI_OWN_ST_CD as "RI_OWN_ST_CD" ,
              CNTRCT_DD as "CNTRCT_DD" ,
              SIM_DD as "SIM_DD" ,
              TO_CHAR(EFF_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "EFF_DT" ,
              TO_CHAR(PPN_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "PPN_DT" ,
              CNTRL_MD5_AR as "CNTRL_MD5_AR" ,
              CNTRL_MD5 as "CNTRL_MD5" ,
              ROUND(PRTBLTY_OPR_KEY,4) as "PRTBLTY_OPR_KEY" ,
              PRTBLTY_OPR_CD as "PRTBLTY_OPR_CD" ,
              ROUND(PRTBLTY_TP_KEY,4) as "PRTBLTY_TP_KEY" ,
              PRTBLTY_TP_CD as "PRTBLTY_TP_CD" ,
              ROUND(PRTBLTY_DT_KEY,4) as "PRTBLTY_DT_KEY" ,
              TO_CHAR(PRTBLTY_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "PRTBLTY_DT" ,
              ROUND(AR_BILL_ST_KEY,4) as "AR_BILL_ST_KEY" ,
              AR_BILL_ST_CD as "AR_BILL_ST_CD" ,
              ROUND(CNTRCT_END_DT_KEY,4) as "CNTRCT_END_DT_KEY" ,
              TO_CHAR(CNTRCT_END_DT,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "CNTRCT_END_DT" ,
              ROUND(CSTMR_KEY,4) as "CSTMR_KEY" ,
              CSTMR_CD as "CSTMR_CD" ,
              ROUND(PRN_AR_KEY,4) as "PRN_AR_KEY" ,
              PRN_AR_CD as "PRN_AR_CD" ,
              ROUND(IP_USR_KEY,4) as "IP_USR_KEY" ,
              IP_USR_CD AS "IP_USR_CD" ,
              MSISDN_DD as "MSISDN_DD" 
    from DTL.AR_BHVR_DIM 
    );

    dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , 'TMP','AR_BHVR_DIM_B.XML' , nls_charset_id('WE8ISO8859P1'));
end;
#7 PLAN B outcome
As a mentioned above, the XML file shape is not what I need. Also is impossible to add a namespace and attributes. The only way to generated the file was to apply a rownum < 100000 in the WHERE clause.
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <AR_BHVR_KEY>-1</AR_BHVR_KEY>
  <AR_KEY>-1</AR_KEY>
  <AR_ACTVN_ST_KEY>-1</AR_ACTVN_ST_KEY>
  …  
  <MSISDN_DD>3153771398</MSISDN_DD>
 </ROW>
</ROWSET>
#8 PLAN B errors
Please find below two errors produce by the Oracle Database after removing the rownum < 100000 in the WHERE clause.
Error report:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: invalid character 58 (':') found in a Name or Nmtoken
Error at line 111512854
ORA-06512: at "SYS.XMLTYPE", line 343
ORA-06512: at line 78
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.
Error report:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00240: element-start tag is not well formed
Error at line 113808108
ORA-06512: at "SYS.XMLTYPE", line 343
ORA-06512: at line 78
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.
Please advise.

Edited by: 984830 on Jan 29, 2013 6:59 AM
  • 1. Re: How to generate XML file with a particular shape from a Big table
    odie_63 Guru
    Currently Being Moderated
    Regarding your first attempt (plan A), it would be interesting to see where the time is being spent by tracing the session :

    - If the query is the issue, then how does memory consumption evolve during the process?
    - If the output to the filesystem is the issue, you can monitor IO's on the OS.

    Could you estimate the size of the whole file?

    Do you have a license for the parallel option?
  • 2. Re: How to generate XML file with a particular shape from a Big table
    987833 Newbie
    Currently Being Moderated
    The file is almost 22 Gigabytes

    -rw-r--r-- 1 oracle asmadmin 22737039360 Jan 29 14:42 AR_BHVR_DIM_H.xml

    Yes,i have a license for the parallel option

    The retrieval part it is working. Save to file is failing
    DBMS_XSLPROCESSOR.CLOB2FILE('<?xml version="1.0" encoding="iso-8859-1"?>' || DOC,
                                'TMP',
                                'AR_BHVR_DIM.XML'
                                , nls_charset_id('WE8ISO8859P1'));
  • 3. Re: How to generate XML file with a particular shape from a Big table
    988118 Newbie
    Currently Being Moderated
    Hi Odie_63,

    Do you have enough information from 984830 to proceed?

    So the stumbling block is the scalability of the DBMS_XSLPROCESSOR.CLOB2FILE() method.
    Is DBMS_XSLPROCESSOR.CLOB2FILE() doing DOM processing inside which makes it not so scalable?

    The best internal implementation of the .CLOB2FILE() method would be streaming CLOB data type to the file system…

    Maybe Mark Drake who knows the DBMS_XSLPROCESSOR package internals can answer that question and provide ideas what could be done to save the XML file on file system.
  • 4. Re: How to generate XML file with a particular shape from a Big table
    odie_63 Guru
    Currently Being Moderated
    Hi,
    Do you have enough information from 984830 to proceed?
    You're one of his/her co-worker?
    To answer your question, yes I guess we can now focus on the offending part of the process.
    Is DBMS_XSLPROCESSOR.CLOB2FILE() doing DOM processing inside which makes it not so scalable?
    No, it just takes a CLOB, whatever its content, and writes it to a file.
    The best internal implementation of the .CLOB2FILE() method would be streaming CLOB data type to the file system…
    CLOB2FILE uses DBMS_LOB and UTL_FILE behind the scenes to chunk and write the content to disk.
    I suppose it does that the best possible way, though I remember some other threads mentioning similar problems. MOS may have a few articles about this too.

    Besides using UTL_FILE explicitly, the other alternatives I know of in PL/SQL are :

    - DBMS_XMLDOM.writeToFile
    - DBMS_ADVISOR.create_file
    - saving the CLOB as a resource in the XML DB repository and pull it via FTP, but I think 22 GB exceeds the upper limit for a resource anyway.

    I mentioned the parallel query features earlier, it may be an option to consider too.
    We can have different parallel slave processes write a different portion of the table in its own file, and at the end run a shell script to merge them (along with the root).
    See "parallel execution with pipelined functions" @ http://www.oracle-developer.net/display.php?id=425

    I would gladly test some of those suggestions myself, however I don't have such volume of test data at my disposal.
  • 5. Re: How to generate XML file with a particular shape from a Big table
    988118 Newbie
    Currently Being Moderated
    Thanks for the generated ideas.
    Yes, we are working together.

    It is easy to generate volume of data along the following lines:
    SELECT xmlserialize(DOCUMENT
                XMLELEMENT("root",
                  XMLATTRIBUTES(SYSDATE AS "DataFeedDate"
    ...               , 'http://www.abccompany.com' as "xmlns"),
    from table_name
    cross join (select 1 from dual connect by level <=10000000);
    I am dreaming that in the future XQuery could be used as an alternative solution.
    It is iterative rather than set-based.
    So the solution could be using FLOWR and stream the sequence of XML nodes to persist on the local file system.
    The XQuery will allow to generate any XML natively with proper BOM, XML prolog, encoding, namespaces, attributes, etc.

    Edited by: 985115 on Jan 30, 2013 8:28 AM

    Edited by: 985115 on Jan 30, 2013 8:29 AM
  • 6. Re: How to generate XML file with a particular shape from a Big table
    odie_63 Guru
    Currently Being Moderated
    I am dreaming that in the future XQuery could be used as an alternative solution.
    It is iterative rather than set-based.
    So the solution could be using FLOWR and stream the sequence of XML nodes to persist on the local file system.
    The XQuery will allow to generate any XML natively with proper BOM, XML prolog, encoding, namespaces, attributes, etc.
    XQuery might provide you with an apparently convenient abstraction layer, but the data still has to be retrieved from the RDBMS at some point, and set-based operations are the best way to do it.
    Oracle's current implementation of relational data access through XQuery does that.
    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#BABECFFD
  • 7. Re: How to generate XML file with a particular shape from a Big table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Some Pointers

    Differences: http://www.liberidu.com/blog/2008/02/15/howto-saving-and-storing-xml-data-a-tuning-adventure/

    Extra, not mentioned options would be:

    - External table (expdp option/method)
    - DBFS option of the securefile development group
    - Streams?

    Upper limit of one file in the XDB repository, in size, is 4GB
  • 8. Re: How to generate XML file with a particular shape from a Big table
    odie_63 Guru
    Currently Being Moderated
    Marco Gralike wrote:
    - External table (expdp option/method)
    Interesting... though it would need some additional processing to dig out the XML text out of the dump file.
    Will test that tonight.
  • 9. Re: How to generate XML file with a particular shape from a Big table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    In principal, if you don't treat it as XML but as CLOB, it should be possible to "dump" it as a table with one column. But I also never really tested it.

    The external table option can be used in very creative ways though. Here 3 brilliant examples:

    http://iggyfernandez.wordpress.com/2013/01/22/we-dont-use-databases-we-dont-use-indexes/
    http://www.oracle-developer.net/display.php?id=513
    http://www.oracle-developer.net/display.php?id=516
  • 10. Re: How to generate XML file with a particular shape from a Big table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    ...the expdp alternative was something that popped in my head, but you would probably get a not so usefull dmp file, although no idea what "strings" and other alternatives would achieve... I am good in breaking things anyway...

    ;-)

    Not supported methods could be (mis)using DBMS_SYSTEM or alternative usage of the PL/SQL RMAN package API...
  • 11. Re: How to generate XML file with a particular shape from a Big table
    987833 Newbie
    Currently Being Moderated
    We broke down big single XML into smaller fragments and stored them in a database table. Each row stores 1 million rows from the source table as one single fragment.
    Even for such scenario the DBMS_XSLPROCESSOR.CLOB2FILE still errored out. Please see below.
    The verdict is that DBMS_XSLPROCESSOR.CLOB2FILE has serious problems.
    desc DESA.TEMP_CLOB_TAB;
    
    Name   Null Type 
    ------ ---- ---- 
    RESULT      CLOB 
    FECHA       DATE 
    Error starting at line 58 in command:
    declare
        DOC CLOB;
    
        cursor C1 is
          select result from DESA.TEMP_CLOB_TAB;  
          
        fileCount number;
    begin
        fileCount := 0;
        
        for A1 in C1
        LOOP
          FILECOUNT := FILECOUNT + 1;            
          DBMS_XSLPROCESSOR.CLOB2FILE(A1.result,
                                      'TMP',
                                      'AR_BHVR_DIM_M_'|| FILECOUNT ||'.XML'
                                      , nls_charset_id('WE8ISO8859P1'));
        END LOOP;
    end;
    Error report:
    ORA-01426: numeric overflow
    ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 319
    ORA-06512: at line 14
    01426. 00000 -  "numeric overflow"
    *Cause:    Evaluation of an value expression causes an overflow/underflow.
    *Action:   Reduce the operands.
  • 12. Re: How to generate XML file with a particular shape from a Big table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Maybe you can try to distill the needed code from procedure "CREATE_LARGE_CSV", which creates a CSV file in binary/raw format
    --------------------------------------------------------
    
      CREATE OR REPLACE PACKAGE "CSV_UTILITIES" 
    AS
      -- --------------------------------------------------------------------------
      -- Version    : 6.01
      -- --------------------------------------------------------------------------
      -- Changes    : MGR, 20110322, Packaging functions and procedures here
      --            : 
      -- --------------------------------------------------------------------------
      -- Created    : Marco Gralike (MG), with help of Anton Scheffers
      -- Purpose    : Central package that contains utility functionality
      -- --------------------------------------------------------------------------
      --
    PROCEDURE create_csv(
        p_sql         IN VARCHAR2 ,
        p_dir         IN VARCHAR2 ,
        p_header_file IN VARCHAR2 ,
        p_gen_header  IN BOOLEAN := FALSE,
        p_prefix      IN VARCHAR2 := NULL,
        p_delimiter   IN VARCHAR2 DEFAULT '|',
        p_dateformat  IN VARCHAR2 DEFAULT 'YYYYMMDD',
        p_data_file   IN VARCHAR2 := NULL,
        p_utl_wra     IN VARCHAR2 := 'w');
      -- -----------------------------------------------------------------------------
      -- Purpose: Utility to create a CSV based on a simple select statement
      --          to be used and optimized for handling small data amounts
      -- Usage  : execute run_query('select * from emp','TEST_DIR','output.txt');
      -- -----------------------------------------------------------------------------
      --
      -- Parameters
      --
      -- P_SQL         - Select statement in use
      -- P_DIR         - Oracle directory alias
      -- P_HEADER_FILE - The filename to be created on disk
      -- P_GEN_HEADER  - Generate header column if needed
      -- P_PREFIX      - Prefix name/alias in front of the row dump to CSV string identifying the staging table
      -- P_DELIMITER   - Delimiter in use
      -- P_DATEFORMAT  - Date time format for all DATE columns used in P_SQL
      -- P_DATA_FILE   - Data file name if a header file and data file seperately are needed
      -- p_utl_wra     - UTL_FILE operator, (w)rite, (a)ppend or (b)inary operation on p_header_file and p_data_file
      --
      -- -----------------------------------------------------------------------------
    PROCEDURE create_large_csv(
        p_sql         IN VARCHAR2 ,
        p_dir         IN VARCHAR2 ,
        p_header_file IN VARCHAR2 ,
        p_gen_header  IN BOOLEAN := FALSE,
        p_prefix      IN VARCHAR2 := NULL,
        p_delimiter   IN VARCHAR2 DEFAULT '|',
        p_dateformat  IN VARCHAR2 DEFAULT 'YYYYMMDD',
        p_data_file   IN VARCHAR2 := NULL,
        p_utl_wra     IN VARCHAR2 := 'wb');
      -- -----------------------------------------------------------------------------
      -- Purpose: Utility to create a CSV based on a simple select statement
      --          to be used and optimized for handling HUGE data amounts
      --          This procedure will write in RAW format to disk.
      -- Usage  : execute run_query('select * from emp','TEST_DIR','output.txt');
      -- -----------------------------------------------------------------------------
      --
      -- Parameters
      --
      -- P_SQL         - Select statement in use
      -- P_DIR         - Oracle directory alias
      -- P_HEADER_FILE - The filename to be created on disk
      -- P_GEN_HEADER  - Generate header column if needed
      -- P_PREFIX      - Prefix name/alias in front of the row dump to CSV string identifying the staging table
      -- P_DELIMITER   - Delimiter in use
      -- P_DATEFORMAT  - Date time format for all DATE columns used in P_SQL
      -- P_DATA_FILE   - Data file name if a header file and data file seperately are needed
      -- p_utl_wra     - UTL_FILE operator, (w)rite, (a)ppend or (b)inary operation on p_header_file and p_data_file
      --
      -- -----------------------------------------------------------------------------
    END CSV_UTILITIES;
    /
    --------------------------------------------------------
    --  DDL for Package Body CSV_UTILITIES
    --------------------------------------------------------
    
      CREATE OR REPLACE PACKAGE BODY "CSV_UTILITIES" 
    AS
      -- --------------------------------------------------------------------------
      -- Version    : 6.01
      -- --------------------------------------------------------------------------
      -- Changes    : MGR, 20110322, Packaging functions and procedures here
      --            : 
      -- --------------------------------------------------------------------------
      -- Created    : Marco Gralike (MG), with help of Anton Scheffers
      -- Purpose    : Central package that contains utility functionality
      -- --------------------------------------------------------------------------
      --
      -- ---------------------------------------------------------------------------
      -- PROCEDURE CREATE_CSV
      -- ---------------------------------------------------------------------------
    PROCEDURE create_csv(
        p_sql         IN VARCHAR2 ,
        p_dir         IN VARCHAR2 ,
        p_header_file IN VARCHAR2 ,
        p_gen_header  IN BOOLEAN := FALSE,
        p_prefix      IN VARCHAR2 := NULL,
        p_delimiter   IN VARCHAR2 DEFAULT '|',
        p_dateformat  IN VARCHAR2 DEFAULT 'YYYYMMDD',
        p_data_file   IN VARCHAR2 := NULL,
        p_utl_wra     IN VARCHAR2 := 'w')
    IS
      v_finaltxt CLOB;
      v_v_val VARCHAR2(4000);
      v_n_val NUMBER;
      v_d_val DATE;
      v_ret   NUMBER;
      c       NUMBER;
      d       NUMBER;
      col_cnt INTEGER;
      f       BOOLEAN;
      rec_tab DBMS_SQL.DESC_TAB;
      col_num NUMBER;
      v_filehandle UTL_FILE.FILE_TYPE;
      v_samefile BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
    BEGIN
      c := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
      d := DBMS_SQL.EXECUTE(c);
      DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
      --
      FOR j IN 1..col_cnt
      LOOP
        BEGIN
          CASE rec_tab(j).col_type
          WHEN 1 THEN
            DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
          WHEN 2 THEN
            DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
          WHEN 12 THEN
            DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
          ELSE
            DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
          END CASE;
        EXCEPTION
        WHEN OTHERS THEN
          LOGGER.LOG_ERROR();
        END;
      END LOOP;
      -- --------------------------------------
      -- This part outputs the HEADER if needed
      -- --------------------------------------
      v_filehandle := UTL_FILE.FOPEN(upper(p_dir),p_header_file,p_utl_wra,32767);
      --
      IF p_gen_header = TRUE THEN
        FOR j        IN 1..col_cnt
        LOOP
          v_finaltxt := ltrim(v_finaltxt||p_delimiter||lower(rec_tab(j).col_name),p_delimiter);
        END LOOP;
        --
        -- Adding prefix if needed
        IF p_prefix IS NULL THEN
          UTL_FILE.PUT_LINE(v_filehandle, v_finaltxt);
          -- DBMS_OUTPUT.PUT_LINE(v_finaltxt);
        ELSE
          v_finaltxt := 'p_prefix'||p_delimiter||v_finaltxt;
          UTL_FILE.PUT_LINE(v_filehandle, v_finaltxt);
          -- DBMS_OUTPUT.PUT_LINE(v_finaltxt);
        END IF;
        --
        -- Creating creating seperate header file if requested
        IF NOT v_samefile THEN
          UTL_FILE.FCLOSE(v_filehandle);
        END IF;
      END IF;
      -- --------------------------------------
      -- This part outputs the DATA to file
      -- --------------------------------------
      IF NOT v_samefile THEN
        v_filehandle := UTL_FILE.FOPEN(upper(p_dir),p_data_file,p_utl_wra,32767);
      END IF;
      --
      LOOP
        v_ret := DBMS_SQL.FETCH_ROWS(c);
        EXIT
      WHEN v_ret    = 0;
        v_finaltxt := NULL;
        FOR j      IN 1..col_cnt
        LOOP
          BEGIN
            CASE rec_tab(j).col_type
            WHEN 1 THEN
              -- VARCHAR2
              BEGIN
                DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                v_finaltxt := ltrim(to_clob(v_finaltxt)||p_delimiter||TO_CHAR(v_v_val),p_delimiter);
              EXCEPTION
              WHEN VALUE_ERROR THEN
                LOGGER.LOG('Data not unloaded due to ORA'||SQLCODE||'! ('||'column name: '||rec_tab(j).col_name||' column type: '||rec_tab(j).col_type||' value: '||v_v_val||')', 'BTR_UTILITES');
              END;
            WHEN 2 THEN
              -- NUMBER
              BEGIN
                DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                v_finaltxt := ltrim(to_clob(v_finaltxt)||p_delimiter||TO_CHAR(v_n_val),p_delimiter);
              EXCEPTION
              WHEN VALUE_ERROR THEN
                LOGGER.LOG('Data not unloaded due to ORA'||SQLCODE||'! ('||'column name: '||rec_tab(j).col_name||' column type: '||rec_tab(j).col_type||' value: '||v_n_val||')', 'BTR_UTILITES');
              END;
            WHEN 12 THEN
              -- DATE
              BEGIN
                DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                v_finaltxt := ltrim(to_clob(v_finaltxt)||p_delimiter||TO_CHAR(v_d_val,p_dateformat),p_delimiter);
              EXCEPTION
              WHEN VALUE_ERROR THEN
                LOGGER.LOG('Data not unloaded due to ORA'||SQLCODE||'! ('||'column name: '||rec_tab(j).col_name||' column type: '||rec_tab(j).col_type||' value: '||v_d_val||')', 'BTR_UTILITES');
              END;
            ELSE
              BEGIN
                v_finaltxt := ltrim(to_clob(v_finaltxt)||p_delimiter||v_v_val,p_delimiter);
              EXCEPTION
              WHEN VALUE_ERROR THEN
                LOGGER.LOG('Data not unloaded due to ORA'||SQLCODE||'! ('||'column name: '||rec_tab(j).col_name||' column type: '||rec_tab(j).col_type||' value: '||v_v_val||')', 'BTR_UTILITES');
              END;
            END CASE;
          END;
        END LOOP;
        --
        IF p_prefix IS NULL THEN
          -- No prefix needed
          UTL_FILE.PUT_LINE(v_filehandle, v_finaltxt);
          -- DBMS_OUTPUT.PUT_LINE(v_finaltxt);
        ELSE
          -- Prefix needed
          v_finaltxt := TO_CHAR(p_prefix)||TO_CHAR(p_delimiter)||v_finaltxt;
          UTL_FILE.PUT_LINE(v_filehandle, v_finaltxt);
          -- DBMS_OUTPUT.PUT_LINE(v_finaltxt);
        END IF;
        --
      END LOOP;
      UTL_FILE.FCLOSE(v_filehandle);
      DBMS_SQL.CLOSE_CURSOR(c);
    EXCEPTION
    WHEN OTHERS THEN
      LOGGER.LOG_ERROR();
      RAISE;
    END create_csv;
    -- ---------------------------------------------------------------------------
    -- PROCEDURE CREATE_LARGE_CSV
    -- ---------------------------------------------------------------------------
    PROCEDURE create_large_csv(
        p_sql         IN VARCHAR2 ,
        p_dir         IN VARCHAR2 ,
        p_header_file IN VARCHAR2 ,
        p_gen_header  IN BOOLEAN := FALSE,
        p_prefix      IN VARCHAR2 := NULL,
        p_delimiter   IN VARCHAR2 DEFAULT '|',
        p_dateformat  IN VARCHAR2 DEFAULT 'YYYYMMDD',
        p_data_file   IN VARCHAR2 := NULL,
        p_utl_wra     IN VARCHAR2 := 'wb')
    IS
      v_finaltxt CLOB;
      v_v_val VARCHAR2(4000);
      v_n_val NUMBER;
      v_d_val DATE;
      v_ret   NUMBER;
      c       NUMBER;
      d       NUMBER;
      col_cnt INTEGER;
      f       BOOLEAN;
      rec_tab DBMS_SQL.DESC_TAB;
      col_num NUMBER;
      v_filehandle UTL_FILE.FILE_TYPE;
      v_samefile BOOLEAN      := (NVL(p_data_file,p_header_file) = p_header_file);
      v_CRLF raw(2)           := HEXTORAW('0D0A');
      v_chunksize pls_integer := 8191 - UTL_RAW.LENGTH( v_CRLF );
    BEGIN
      c := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
      --
      FOR j IN 1..col_cnt
      LOOP
        CASE rec_tab(j).col_type
        WHEN 1 THEN
          DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
        WHEN 2 THEN
          DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
        WHEN 12 THEN
          DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
        ELSE
          DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
        END CASE;
      END LOOP;
      -- --------------------------------------
      -- This part outputs the HEADER if needed
      -- --------------------------------------
      v_filehandle := UTL_FILE.FOPEN(upper(p_dir),p_header_file,p_utl_wra,32767);
      --
      IF p_gen_header = TRUE THEN
        FOR j        IN 1..col_cnt
        LOOP
          v_finaltxt := ltrim(v_finaltxt||p_delimiter||lower(rec_tab(j).col_name),p_delimiter);
        END LOOP;
        --
        -- Adding prefix if needed
        IF p_prefix IS NULL THEN
          UTL_FILE.PUT_LINE(v_filehandle, v_finaltxt);
        ELSE
          v_finaltxt := 'p_prefix'||p_delimiter||v_finaltxt;
          UTL_FILE.PUT_LINE(v_filehandle, v_finaltxt);
        END IF;
        --
        -- Creating creating seperate header file if requested
        IF NOT v_samefile THEN
          UTL_FILE.FCLOSE(v_filehandle);
        END IF;
      END IF;
      -- --------------------------------------
      -- This part outputs the DATA to file
      -- --------------------------------------
      IF NOT v_samefile THEN
        v_filehandle := UTL_FILE.FOPEN(upper(p_dir),p_data_file,p_utl_wra,32767);
      END IF;
      --
      d := DBMS_SQL.EXECUTE(c);
      LOOP
        v_ret := DBMS_SQL.FETCH_ROWS(c);
        EXIT
      WHEN v_ret    = 0;
        v_finaltxt := NULL;
        FOR j      IN 1..col_cnt
        LOOP
          CASE rec_tab(j).col_type
          WHEN 1 THEN
            -- VARCHAR2
            DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
            v_finaltxt := v_finaltxt || p_delimiter || v_v_val;
          WHEN 2 THEN
            -- NUMBER
            DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
            v_finaltxt := v_finaltxt || p_delimiter || TO_CHAR(v_n_val);
          WHEN 12 THEN
            -- DATE
            DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
            v_finaltxt := v_finaltxt || p_delimiter || TO_CHAR(v_d_val,p_dateformat);
          ELSE
            v_finaltxt := v_finaltxt || p_delimiter || v_v_val;
          END CASE;
        END LOOP;
        --
        v_finaltxt               := p_prefix || v_finaltxt;
        IF SUBSTR(v_finaltxt,1,1) = p_delimiter THEN
          v_finaltxt             := SUBSTR(v_finaltxt,2);
        END IF;
        --
        FOR i IN 1 .. ceil( LENGTH( v_finaltxt ) / v_chunksize )
        LOOP
          UTL_FILE.PUT_RAW( v_filehandle, utl_raw.cast_to_raw( SUBSTR( v_finaltxt, ( i - 1 ) * v_chunksize + 1, v_chunksize ) ), TRUE );
        END LOOP;
        UTL_FILE.PUT_RAW( v_filehandle, v_CRLF );
        --
      END LOOP;
      UTL_FILE.FCLOSE(v_filehandle);
      DBMS_SQL.CLOSE_CURSOR(c);
    END create_large_csv;
    --
    END CSV_UTILITIES;
    /
    Edited by: Marco Gralike on Jan 30, 2013 9:07 PM
  • 13. Re: How to generate XML file with a particular shape from a Big table
    987833 Newbie
    Currently Being Moderated
    Thanks, Can you explain me what is v_chunksize and why the number 8191 ?
    PROCEDURE create_large_csv(
    
    ...
    
      v_CRLF raw(2)           := HEXTORAW('0D0A');
      v_chunksize pls_integer := 8191 - UTL_RAW.LENGTH( v_CRLF );
  • 14. Re: How to generate XML file with a particular shape from a Big table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    I asked Anton, via email, if he would answer this one. I think I still remember why, but I don't want to guess (it's more than 2 years ago that I wrote this code with a bit of help from Anton)
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points