XMLAGG with RTRIM issue
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 XMLTypeselect xmlagg(xmlelement("asdf", test_function)) from dual;--Works fine, returns a CLOBselect trim(test_function) from dual;--ORA-19011: Character string buffer too smallselect trim(xmlagg(xmlelement("asdf", test_function))) from dual;--Worksselect trim(xmlagg(xmlelement("asdf", test_function)).getClobVal()) from dual;