This discussion is archived
4 Replies Latest reply: Sep 23, 2013 10:07 AM by Jason_(A_Non) RSS

Oracle reading Large xml from Clob Column

5100f8f8-5869-43b6-912b-a1a05bd6993f Newbie
Currently Being Moderated


Hi ,

 

I have a xml > 32 KB stored in a clob colum in oracle 10g , i am trying to extract the values by using the below piece of code

 

DECLARE

v_clob CLOB;

v_xml_type XMLTYPE;

 

BEGIN

select XML_CONTENT INTO v_clob from table1 ;

v_xml_type:=XMLTYPE(v_clob);

 

I am trying to parse and extract values using XMLTable and inserting values in other table but when parsing through XMLTYPE it is throwing me below error

 

ORA-31011: XML parsing failed

ORA-19202: Error occurred in XML processing

LPX-00216: invalid character 0 (0x0)

Error at line 1

ORA-06512: at "SYS.XMLTYPE", line 254

ORA-06512: at line 8

 

Same PLSQL block works if the xml size is <15 KB , i have gone through lot of forums and came to know that XMLTYPE loads xml into memory thats why erroring for large xml.

 

Not able to find soution in any of threads how to handle this scenario i have seen similar threads but none with proper soltuion.

 

Please help.

 

Thank you.

  • 1. Re: Oracle reading Large xml from Clob Column
    Jason_(A_Non) Expert
    Currently Being Moderated

    First, the underlying cause of your error message is shown as

    > LPX-00216: invalid character 0 (0x0)

    If search via that, one of hits should be LPX-00216 error help

    So as that states, you have a null character in the particular XML.  Without knowing the XML/DB charactersets, another option is

    LPX-00216: invalid character 0 (0x0) - Urgent

     

    > ... came to know that XMLTYPE loads xml into memory ...

    All variables in PL/SQL are stored in memory, regardless of whether a VARCHAR2, CLOB, or XMLType.  I don't recall a 32k limit, but I do know there is a 64k limit.  See

    ORA-31167: 64k size limit for XML node

     

    Also, 10g is a marketing label covering from 10.1.0.1 through 10.2.0.5.  A lot changed from start to finish so please been specific and include the results of

    select * from v$version

    in the start of future posts.

  • 2. Re: Oracle reading Large xml from Clob Column
    odie_63 Guru
    Currently Being Moderated

     

    Same PLSQL block works if the xml size is <15 KB , i have gone through lot of forums and came to know that XMLTYPE loads xml into memory thats why erroring for large xml.

     

    So how large is your XML in reality ?

    Don't focus on the 32k boundary, it's certainly not the cause of the error.

     

    As Jason's already pinpointed, you probably have a not so wellformed content.

    Could you check that, or even better, share one the problematic XMLs with us so that we see for ourselves ?

  • 3. Re: Oracle reading Large xml from Clob Column
    5100f8f8-5869-43b6-912b-a1a05bd6993f Newbie
    Currently Being Moderated

    Thank you for your responses.

     

    My XML in reality it greater than 20 MB and less than 30 MB

     

    I have tried correcting my XML and now able to parse my XML with help of DOM Document and xslProcessor with below piece of code


    Where as when i try to read it using XMLTYPE and XMLTable with the above mentioned code

     

    DECLARE

    v_clob CLOB;

    v_xml_type XMLTYPE;

     

    BEGIN

    select XML_CONTENT INTO v_clob from table1 ;

    v_xml_type:=XMLTYPE(v_clob)

     

    for c in (SELECT x.* into detail1,detail2 FROM XMLTable ('for $i in /main/data/Details return $i'

                       passing  v_xml_type columns

                           detail1 VARCHAR2(200) path 'detail1 ',

                             detail2 VARCHAR2(200) path 'detail2 '

                        ) x  )

    Loop

    dbms_output.put_line(c.detail1);

    dbms_output.put_line(c.detail2);

    End Loop;

     

    I am getting below error now

    ORA-31167: XML nodes over 64K in size cannot be inserted

    ORA-06512: at "SYS.XMLTYPE", line 5

    ORA-06512: at line 8

     

    Once Again Thank you

  • 4. Re: Oracle reading Large xml from Clob Column
    Jason_(A_Non) Expert
    Currently Being Moderated

    From the link I included in my first response

    ORA-31167: 64k size limit for XML node

    one of the facts was

    In 10.x if we detect a node > 64K while parsing the document we throw an error.

    where we = oracle database.  Note that this means only a single node with more than 64K of text, not a node with children nodes.  The overall size of the XML does not matter, only the size of each individual node.  You will want to read through that whole post as lots of good info in it.

     

    The best answer now is to go to 11.2 or 12.1, otherwise you end up doing clob manipulations to strip out the large node so that you can convert it to an XMLType.

Legend

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