1 Reply Latest reply: Nov 9, 2012 2:45 AM by odie_63 RSS

    XMLAGG with RTRIM Issue

    TrojanSpirit
      Hello,

      Currently I have the following query:

      SELECT

      *CASE WHEN ( '[Param.3]' = 'SELECTED' )*

      THEN (

      Select rtrim(XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) , ' ' ) AS Orders
      from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
      COLUMNS "ID" NUMBER(19) PATH 'ID',
      R xmltype path '.'

      *) AS RW*

      *Where ID BETWEEN '[Param.1]' and '[Param.2]'*


      *)*

      *WHEN ('[Param.3]' = 'ALL' )*
      THEN (

      Select rtrim(XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) , ' ' ) AS Orders
      from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
      COLUMNS "ID" NUMBER(19) PATH 'ID',
      R xmltype path '.'

      *) AS RW*

      *)*

      END AS Orders

      FROM dual


      This qiery is working fine if there are small number of XML rows to be merged into single row with XML AGG. But if the number of XML Rows to be merged are higher, this query is giving me following error:

      ORA-19011: Character string buffer too small

      What change do I need to make it work?

      Edited by: 967327 on Nov 8, 2012 3:38 PM

      Edited by: 967327 on Nov 8, 2012 3:45 PM
        • 1. Re: XMLAGG with RTRIM Issue
          odie_63
          Hi,

          Once again, would you care to explain why you're using RTRIM? What are you trying to achieve?

          As explained earlier, RTRIM does an implicit serialization to VARCHAR2, hence the error when the aggregated content exceeds 4000 bytes.
          What datatype are you expecting for the final result?

          Give your database version.


          This works for me :
          SQL> set long 5000
          SQL> var param3 varchar2(30)
          SQL> var param1 number
          SQL> var param2 number
          SQL> exec :param1 := 2
          
          PL/SQL procedure successfully completed.
          
          SQL> exec :param2 := 3
          
          PL/SQL procedure successfully completed.
          
          SQL> exec :param3 := 'ALL'
          
          PL/SQL procedure successfully completed.
          
          SQL> select xmlelement("Rowset",
            2           xmlagg(x.r)
            3         ) as result
            4  from tmtable t
            5     , xmltable(
            6         '/Rowsets/Rowset/Row'
            7         passing t.text
            8         columns id  number  path 'ID'
            9               , r   xmltype path '.'
           10       ) x
           11  where ( x.id between :param1 and :param2
           12          and :param3 = 'SELECTED' )
           13     or :param3 = 'ALL'
           14  ;
          
          RESULT
          --------------------------------------------------------------------------------
          <Rowset><Row>
            <ID>1</ID>
            <PO Uom="">4500005146</PO>
            <Line Uom="">10</Line>
          </Row>
          <Row>
            <ID>2</ID>
            <PO Uom="">4500005147</PO>
            <Line Uom="">10</Line>
          </Row>
          <Row>
            <ID>3</ID>
            <PO Uom="">4500005148</PO>
            <Line Uom="">10</Line>
          </Row>
          </Rowset>
          
          
          SQL> exec :param3 := 'SELECTED'
          
          PL/SQL procedure successfully completed.
          
          SQL> select xmlelement("Rowset",
            2           xmlagg(x.r)
            3         ) as result
            4  from tmtable t
            5     , xmltable(
            6         '/Rowsets/Rowset/Row'
            7         passing t.text
            8         columns id  number  path 'ID'
            9               , r   xmltype path '.'
           10       ) x
           11  where ( x.id between :param1 and :param2
           12          and :param3 = 'SELECTED' )
           13     or :param3 = 'ALL'
           14  ;
          
          RESULT
          --------------------------------------------------------------------------------
          <Rowset><Row>
            <ID>2</ID>
            <PO Uom="">4500005147</PO>
            <Line Uom="">10</Line>
          </Row>
          <Row>
            <ID>3</ID>
            <PO Uom="">4500005148</PO>
            <Line Uom="">10</Line>
          </Row>
          </Rowset>
          Edited by: odie_63 on 9 nov. 2012 09:41