XMLAGG with RTRIM issue

Question

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 query 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 throwing the following error:

ORA-19011: Character string buffer too small

What change do I need to apply to make this work?

Solution 1

You need to add .getClobVal() to your XMLType result, before the RTRIM.

XMLAGG works fine with large amounts of data. And TRIM works fine with CLOBs. But when you put them together, Oracle tries to convert the XMLType into a VARCHAR2 instead of a CLOB.

Example:

create or replace function test_function return clob is
    v_clob clob;
begin
    v_clob := v_clob || lpad('a', 4000, 'a');
    v_clob := v_clob || lpad('b', 4000, 'b');
    return v_clob;
end;
/

--Works fine, returns an XMLType
select xmlagg(xmlelement("asdf", test_function)) from dual;

--Works fine, returns a CLOB
select trim(test_function) from dual;

--ORA-19011: Character string buffer too small
select trim(xmlagg(xmlelement("asdf", test_function))) from dual;

--Works
select trim(xmlagg(xmlelement("asdf", test_function)).getClobVal()) from dual;

Solution 2

You need to add getClobVal() and also need to rtrim() as it will return delimiter in the end of the results.

SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',')
  FROM tablename;

Comments

[Soham Shah]

I am trying following query: Select XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID").getClobVal() ) , ' ' ) AS Orders from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT COLUMNS "ID" NUMBER(19) PATH 'ID', R xmltype path '.' ) AS RW But it gives me follwoing error: ORA-00932: inconsistent datatypes: expected - got CHAR

[Jon Heller]

@SohamShah I think you need to put the .getClobVal() after the last XML function, not just the last XMLAGG. Try: XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID"), ' ' ).getClobVal().