13 Replies Latest reply: Feb 5, 2010 7:10 PM by mdrake RSS

    Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 1000

    747622
      I have a following script which is working and creating xml output when the rownum < 1000, but as the rownum <2000 clause is used, it is throwing "ORA-31011: XML parsing failed" error.
      There are about 2 million recirds that I need to extract in the xml file. Is there any way I can do that without creating 500 files?
      I am new in XML DB and any help/suggestion/input will be highly appreciated.
      Thanks.
      ---------------- script--------------------
      SET SERVEROUTPUT ON
      DECLARE
       v_xml              XMLTYPE;
       v_blob             BLOB;
       v_data_length      NUMBER;
      
       -- Loop Control Variables
       v_offset           NUMBER             DEFAULT 1;
       v_chunk   CONSTANT NUMBER             DEFAULT 4000;
      
      
      -- v_ctx   DBMS_XMLGen.ctxHandle;
       v_file  Utl_File.File_Type;
      
      BEGIN
      -- Create XML context.
      
          SELECT XMLELEMENT("COMBO_NETWORK", XMLAGG (IFC_BILL_LOC))
          INTO v_xml
          FROM (
          SELECT XMLELEMENT
              ("CLAIM",
               XMLFOREST(
              ib.clm_num as "CLM_NUM",
              NVL(ib.old_clm_num,' ') as "ALT_CLM_NUM",
              DECODE(ib.clm_stat_cde,1,'OPEN','CLOSED') as "CLM_STATUS",
              ib.SSN as "SSN",
              ib.CLMT_LAST_NAM as "LAST_NAME",
              ib.CLMT_FIRST_NAM as "FIRST_NAME",
              TO_CHAR(ib.CLMT_DOB,'RRRRMMDD') as "DOB",
              TO_CHAR(ib.CLMT_DOI,'RRRRMMDD') as "DOI",
              ib.ER_NAM as "EMPLOYER_NAME",
              sflo.cde  AS "DISTRICT_OFFICE",
              ib.ADD_UPDTE_DEL_FLG as "ADD_UPDATE_DELETE",
              XMLFOREST(ib.CLMT_FST_LINE_ADR||' '||ib.CLMT_SND_LINE_ADR  as "STREET",
                        ib.CLMT_CITY_ADR AS "CITY",
                        ib.CLMT_STATE_ADR as "STATE",
                        ib.CLMT_ZIP_CDE_ADR as "ZIP",
                        ' ' as "PHONE" ) AS "CLAIMNT_ADDRESS",
              DECODE(ib.CLMT_GENDER,1,'M',2,'F',' ') as "CLAIMNT_GENDER",
              XMLFOREST(ib.ADJ_LAST_NAM as "LAST_NAME",
                        ib.ADJ_FIRST_NAM as "FIRST_NAME",
                        au.PHONE_NUM as "PHONE",
                        au.USER_EMAIL as "EMAIL") as "ADJUSTER",
              NVL(ib.CATASTROPHE_FLG,'N') as "CATASTROPHE_FLAG"
             )
           )
          IFC_BILL_LOC
          FROM IFACE_BILL_CLAIM_TRANS ib, SCIF_LOCATIONS sflo, APPLICATION_USERS au
          WHERE ib.comb_transmit_dte IS NULL
          AND ib.SFLO_IDN = sflo.idn
          AND ib.adj_last_nam = UPPER(au.user_last_nam)
          AND ib.adj_first_nam = UPPER(au.user_first_nam)
          AND sflo.cde = 'NF'
          AND rownum < 1000);  -- Failes parsing as rownum < 2000 is used.
      
          -- Turn the XML into a BLOB 
           v_blob := v_xml.getblobval (1);
           v_data_length := DBMS_LOB.getlength (v_blob);
      
          -- Output XML document to file.
            v_file := Utl_File.FOpen('/u08/test/testdev/interface/out', 'result1.xml', 'wb', v_chunk);
                  LOOP
                  EXIT WHEN v_offset > v_data_length;
                  UTL_FILE.put_raw (v_file, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset),
                                                                    TRUE);
      
                   v_offset := v_offset + v_chunk;
      
                  END LOOP;
      
                      Utl_File.FClose(v_file);
      
              EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                -- We won't write any data, or even open the file,
                -- if the query return no rows
                NULL;
                END;
        /
      --------------------- script end--------------------------
      Following is the version of  y database:
      
      SQL> col comp_name format a45
      
      SELECT comp_name, status, substr(version,1,10) as version
      FROM dba_registry;
      SQL> SQL>   2
      COMP_NAME                                     STATUS      VERSION
      --------------------------------------------- -         ----------      ----------
      Oracle Database Catalog Views                VALID       10.2.0.4.0
      Oracle Database Packages and Types       VALID       10.2.0.4.0
      JServer JAVA Virtual Machine                  VALID       10.2.0.4.0
      Oracle Database Java Packages                VALID       10.2.0.4.0
      Oracle XDK                                           VALID       10.2.0.4.0
      Oracle XML Database                              VALID       10.2.0.4.0
      
      6 rows selected.
      
      SQL>
      Edited by: mdrake on Feb 4, 2010 3:55 PM
        • 1. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds
          AntsHindpere
          Are you sure that the data does not contain characters which may cause invalid xml?
          for example
          SQL> with t as (select chr(0) val from dual)
            2  select xmlelement("test",val) from t;
          ERROR:
          ORA-31011: XML parsing failed
          • 2. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds
            747622
            Yes, the data may data may contain characters which may cause invalid xml. But there are about > 10000 records. How do I found out if there is a data error in them. I am doing it by districts. For some of the districts, it is causing this parsing failure error. Is there a way?
            Please help.
            • 3. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds
              user503699
              user11990140 wrote:
              Yes, the data may data may contain characters which may cause invalid xml. But there are about > 10000 records. How do I found out if there is a data error in them. I am doing it by districts. For some of the districts, it is causing this parsing failure error. Is there a way?
              Please help.
              I doubt if you will be able to find all xml syntactical errors using sql.
              But you may be able to get some information (one at a time) using something like following:
              declare
                var   varchar2(200);
                myparser DBMS_XMLPARSER.parser;
              begin
                var := '<emp><name>Tim</nam></emp>' ;
                myparser := DBMS_XMLPARSER.newparser;
                DBMS_XMLPARSER.parsebuffer(myparser, var);
                DBMS_XMLPARSER.freeparser(myparser);
              end;
              If you want to process large chucks, you can use variable of type CLOB and use DBMS_XMLPARSER.PARSECLOB function in place of PARSEBUFFER.
              • 4. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds
                mdrake
                2 quesitons

                Which DB Version are you using

                What would you want us to do with Invalid Characters. There's little value in producing an XML document that other XML processors cannot consume
                • 5. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds
                  mdrake
                  One way is to use translate to map the invalid characters into something valid

                  Eg
                  SQL> var invchr varchar2(30);
                  SQL> begin
                  2  :invchr := ' '|| chr(0) || chr(1) || ... || chr(31);   -- don't include chars 9,10,13
                  end;
                  /
                  
                  SQL> select xmlelement("tag", TRANSLATE( strcol, :invchr, chr(32))) from tab1;
                  • 6. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds
                    747622
                    My sql statement is given in the first post. (Don't know jow to put formatted text in here.)
                    Not sure how to use XMLCDATA or DBMS_XMLPARSER in that.
                    Could you please provide a sample.

                    I am using Oracle Database 10g

                    Is there a way the records with invalid characters are logged in a file? Like SQL Loader does? Need to be give the bad records to the vendor.

                    Thanks for all your help.
                    • 7. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds
                      user503699
                      mdrake wrote:
                      2 quesitons

                      Which DB Version are you using
                      10.2.0.1
                      What would you want us to do with Invalid Characters. There's little value in producing an XML document that other XML processors cannot consume
                      Well, OP had not initially mentioned that he/she wants to log errors, so I just suggested a way which (at least) produced error with more details that what OP is getting at present. I don't claim that it is the best way forward but I thought it might help OP :)
                      • 8. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 1000
                        mdrake
                        SQL> DROP TABLE T1
                          2  /
                        
                        Table dropped.
                        
                        SQL> CREATE TABLE T1
                          2  (
                          3    MY_DATA VARCHAR2(10)
                          4  )
                          5  /
                        
                        Table created.
                        
                        SQL> insert into T1 VALUES ('AAAA')
                          2  /
                        
                        1 row created.
                        
                        SQL> insert into T1 VALUES ('BB' || chr(0) || 'BB')
                          2  /
                        
                        1 row created.
                        
                        SQL> insert into T1 VALUES ('CCCC')
                          2  /
                        
                        1 row created.
                        
                        SQL> select xmlElement
                          2         (
                          3            "Result",
                          4            xmlAgg
                          5            (
                          6               xmlElement("Data",MY_DATA)
                          7            )
                          8         )
                          9    from T1
                         10  /
                        ERROR:
                        ORA-31061: XDB error: special char to escaped char conversion failed.
                        
                        
                        
                        no rows selected
                        
                        SQL> var invalidChars varchar2
                        SQL> --
                        SQL> begin
                          2    :invalidChars := chr(0);
                          3  end;
                          4  /
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> select xmlElement
                          2         (
                          3            "Result",
                          4            xmlAgg
                          5            (
                          6               xmlElement("Data",TRANSLATE(MY_DATA,:invalidChars,'*'))
                          7            )
                          8         )
                          9    from T1
                         10  /
                        
                        XMLELEMENT("RESULT",XMLAGG(XMLELEMENT("DATA",TRANSLATE(MY_DATA,:INVALIDCHARS,'*'
                        --------------------------------------------------------------------------------
                        <Result><Data>AAAA</Data><Data>BB*BB</Data><Data>CCCC</Data></Result>
                        
                        SQL> set long 100000
                        SQL> --
                        SQL> select xmlserialize
                          2         (
                          3            DOCUMENT
                          4            xmlElement
                          5            (
                          6              "Result",
                          7              xmlAgg
                          8              (
                          9                 case
                         10                   when TRANSLATE(MY_DATA,:invalidChars,'*') = MY_DATA then
                         11                     XMLElement("Data",MY_DATA)
                         12                   else
                         13                     XMLElement("Data",XMLAttributes(ROWID as "Rowid"),TRANSLATE(MY_DATA,:invalidChars,'*'))
                         14                 end
                         15              )
                         16            )
                         17            as CLOB INDENT SIZE = 2
                         18         )
                         19    from T1
                         20  /
                        
                        XMLSERIALIZE(DOCUMENTXMLELEMENT("RESULT",XMLAGG(CASEWHENTRANSLATE(MY_DATA,:INVAL
                        --------------------------------------------------------------------------------
                        <Result>
                          <Data>AAAA</Data>
                          <Data Rowid="AAASPDAABAAAVIhAAB">BB*BB</Data>
                          <Data>CCCC</Data>
                        </Result>
                        
                        
                        SQL>
                        • 9. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 1000
                          mdrake
                          If you have the fix for bug 8246403 you can also control the behavoir with events. This avoids having to use explict translate operations on each column thay may contain bad data. Note this patch also has the effect of replacing the ORA-31011 error with the ORA-31061 error. Also note it is likely this error number will be changed again in the near future.
                          SQL>       --
                          SQL> -- Replace with ?
                          SQL> --
                          SQL>  alter session set events = '19119 trace name context forever, level 0x100000'
                            2  /
                          
                          Session altered.
                          
                          SQL> select xmlElement
                            2         (
                            3            "Result",
                            4            xmlAgg
                            5            (
                            6               xmlElement("Data",MY_DATA)
                            7            )
                            8         )
                            9    from T1
                           10  /
                          
                          XMLELEMENT("RESULT",XMLAGG(XMLELEMENT("DATA",MY_DATA)))
                          --------------------------------------------------------------------------------
                          <Result><Data>AAAA</Data><Data>BB?BB</Data><Data>CCCC</Data></Result>
                          
                          SQL>       --
                          SQL> -- Replace with Character Reference
                          SQL> --
                          SQL> alter session set events = '19119 trace name context forever, level 0x200000'
                            2  /
                          
                          Session altered.
                          
                          SQL> select xmlElement
                            2         (
                            3            "Result",
                            4            xmlAgg
                            5            (
                            6               xmlElement("Data",MY_DATA)
                            7            )
                            8         )
                            9    from T1
                           10  /
                          
                          XMLELEMENT("RESULT",XMLAGG(XMLELEMENT("DATA",MY_DATA)))
                          --------------------------------------------------------------------------------
                          <Result><Data>AAAA</Data><Data>BB&#x0000;BB</Data><Data>CCCC</Data></Result>
                          
                          SQL>
                          SQL>      --
                          SQL> -- Remove Bad Characters
                          SQL> --
                          SQL> alter session set events = '19119 trace name context forever, level 0x400000'
                            2  /
                          
                          Session altered.
                          
                          SQL> select xmlElement
                            2         (
                            3            "Result",
                            4            xmlAgg
                            5            (
                            6               xmlElement("Data",MY_DATA)
                            7            )
                            8         )
                            9    from T1
                           10  /
                          
                          XMLELEMENT("RESULT",XMLAGG(XMLELEMENT("DATA",MY_DATA)))
                          --------------------------------------------------------------------------------
                          <Result><Data>AAAA</Data><Data>BBBB</Data><Data>CCCC</Data></Result>
                          
                          SQL>
                          SQL>
                          Edited by: mdrake on Feb 4, 2010 3:45 PM

                          Edited by: mdrake on Feb 4, 2010 3:46 PM

                          Edited by: mdrake on Feb 4, 2010 3:47 PM
                          • 10. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 1000
                            747622
                            That's great! Thank you.
                            Unfortunately, our environment doesn't have the fix for the bug 8246403.
                            What way do I have to extract the xml file when I do not have knowledge about what kind of invalid characters I am having in my rowset?
                            • 11. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 1000
                              mdrake
                              Looking at your original post it appears you are on 10.2.0.4.0. The good news is that a patch for the bug number I identified is already available for 10.2.0.4.0 for a number of platforms including Solaris, HP-UX and Solaris for Intel. This means that it should be possible to either download the patch and install it if you are on one of those platforms, or contact support and ask for it to be made available to you if your are on a different platform from those where the patch is already prepared. The designated Oracle Suppport person in your company should be able to do this for you if you are unfamilar with the process.

                              Typically we produce a patch because there is not an (easy) alternative.. The only alternative would be to following the example I had with the TRANSLATE operator and a list of invalidchars. Invalidchars for XML are 0-31 excluding 9, 10, 13 plus some others. Look at the XML spec at www.w3c.org for more details

                              -Mark
                              • 12. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 1000
                                747622
                                Mark,

                                Thanks a lot. As you suggested, I looked at Oracle Metalink for the bug 8246403. It says "Cause: When a character chr(0) to chr(31) is encountered in the invoice or invoice line level description, format fails abruptly by giving the error "XML Parsing failed".

                                Issue is with AL32UTF8 Character set. "

                                But in our environments, we don't have. In one environment it is

                                NLS_CHARACTERSET WE8MSWIN1252
                                NLS_NCHAR_CHARACTERSET UTF8

                                In another, it is

                                NLS_CHARACTERSET WE8MSWIN1252
                                NLS_NCHAR_CHARACTERSET AL16UTF16

                                Do you know if RDBMS Patch 8246403 and XML Patch 7339075 (as specified in the bug) addresses this issue? Or there is any other solution for this?
                                Sorry for asking too many question.
                                • 13. Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 1000
                                  mdrake
                                  The patch is not character set specifc. It basically gives you control over what to do when the SQL/XML operators encounter a value that is not legally allowed to appear in an XML document. It should work with any database character set. Based on your character set I'm guessing you are on Windows or Linuix.

                                  In general we strongly recommend the use of the database character set AL32UTF8 when ever a database is going to used with our XML capabilities. This is simply a precaution based on the fact that most XML documents are UTF-8 encoded, so could easily end up containing characters that cannot be legally represented in an ISO-LATIN-1 based character set. XML DB does not use the NLS_LANGUAGE features of the DB to the setting of NLS_CHARSET is not relavant from an XML DB perspective.

                                  I would strong recommend download and installing / requesting the patch and testing to see of it resolves the issue you are encouintering. I will be very suprised it it does not address the issues you are encountering.