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?
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;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With