XMLAGG与RTRIM问题 [英] XMLAGG with RTRIM issue

问题描述

当前我有以下查询:

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

如果有少量XML行要与XML AGG合并为单行，则此查询工作正常.但是，如果要合并的XML行数较多，则此查询将引发以下错误:

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:字符串缓冲区太小

ORA-19011: Character string buffer too small

要进行这项工作，我需要进行哪些更改?

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

推荐答案

您需要在RTRIM之前将.getClobVal()添加到XMLType结果中.

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

XMLAGG可以很好地处理大量数据. TRIM与CLOB配合良好.但是，当您将它们放在一起时，Oracle会尝试将XMLType转换为VARCHAR2而不是CLOB.

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.

示例:

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;

这篇关于XMLAGG与RTRIM问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！