developers

    Forum Stats

  • 3,873,999 Users
  • 2,266,660 Discussions
  • 7,911,690 Comments

Discussions

Creating list by XMLAGG, XMLELEMENT throughs ORA-31186 error.

Rupa Mohan
Rupa Mohan Member Posts: 31
edited Jan 18, 2018 8:55AM in SQL & PL/SQL

Hi All,

I am using the below XMLAGG, XMLELEMENT for generating the list by concatenating the column row values. Ex : 123,456,789,908,765

Using:

select XMLAGG(XMLELEMENT(E,CODE_COMBINATION_ID,',')).EXTRACT('//text()').GetClobVal() text from ( SELECT CC.CODE_COMBINATION_ID,CC.CONTROL_CONTEXT_ID FROM HES_CODE_COMBINATIONS CC .....

But it works fine for the minimal no of rows.

But for the rows numbered nearly 500000 it throws the below error.

"ORA-31186: Document contains too many nodes

31186. 00000 -  "Document contains too many nodes"

*Cause:    Unable to load the document because it has exceeded

           the maximum allocated number of DOM nodes.

*Action:   Reduces the size of the document."

Kindly give an alternate solution for creating the column list for large no of rows .

Regards,

RM

Tagged:
Paulzip

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Jan 18, 2018 8:09AM

    You want a list of more than 307178 elements concatenated as a list of comma  separated values, what for?

    Paulzip
  • cormaco
    cormaco Member Posts: 2,041 Silver Crown
    edited Jan 18, 2018 8:25AM

    Hi,

    you could write you values into a clob in a loop like that:

    DECLARE     CURSOR codelist IS        SELECT TRUNC(dbms_random.value(100,999)) AS code_combination_id FROM dual CONNECT BY LEVEL <= 10;    tmp_clob clob := EMPTY_CLOB();BEGIN    FOR CL IN codelist LOOP        -- tmp_clob := tmp_clob || ',' || cl.code_combination_id;         tmp_clob := tmp_clob || TO_CLOB(',') || TO_CLOB(cl.code_combination_id);    END LOOP;        dbms_output.put_line(SUBSTR(tmp_clob,2)); --SUBSTR to strip off leading kommaend;

    Random result:

    432,803,684,944,809,237,465,848,901,176

    EDIT:

    Line 8 was orginally not correct, all values in the concatenation must be CLOBs

  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond
    edited Jan 18, 2018 8:55AM

    Unless you are on an old version of Oracle (10g), you're using routines that have been deprecated from Oracle's XML DB functionality.  The following is a more correct, more robust approach, although I think you're still going to hit the upper node limit for the DOM.

    rtrim(

      XMLCast(

        XMLAgg(

          XMLElement(e,

            E.CODE_COMBINATION_ID||','

          )

        ) as clob

      ),

      ','

    )

    Why are trying load half a million values into a single line comma separated list anyway?????

    I think your best bet is to lessen the number of nodes, by generating comma list for sections of records, then XMLagg-ing those.

    with data as (

      select CC.CODE_COMBINATION_ID,CC.CONTROL_CONTEXT_ID  ....., trunc(rownum / 100) SECTION -- group in 100 row sections

      FROM HES_CODE_COMBINATIONS CC

    )

    select RTrim(

             XMLCast(

               XMLAgg(

                 XMLElement(e,

                   E.COMMALIST

                 )

               ) as clob

             ),

             ','

           )

    from (

      select SECTION,

             XMLCast(

               XMLAgg(

                 XMLElement(e,

                   E.DESCRIPTION||','

                 )

               ) as clob

             ) COMMALIST

      from data e

      group by SECTION

    ) e

This discussion has been closed.
developers