oracle 带有 RTRIM 问题的 XMLAGG
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13299843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
XMLAGG with RTRIM issue
提问by Soham Shah
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:
如果有少量 XML 行要使用 XML AGG 合并为单行，则此查询工作正常。但是如果要合并的 XML 行数更高，则此查询将引发以下错误：
ORA-19011: Character string buffer too small
ORA-19011: 字符串缓冲区太小
What change do I need to apply to make this work?
我需要申请什么改变才能使这项工作发挥作用？
回答by Jon Heller
You need to add .getClobVal()to your XMLType result, before the RTRIM.
您需要.getClobVal()在 RTRIM 之前添加到您的 XMLType 结果。
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.
XMLAGG 可以很好地处理大量数据。TRIM 可以很好地与 CLOB 配合使用。但是，当您将它们放在一起时，Oracle 会尝试将 XMLType 转换为 VARCHAR2 而不是 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;
回答by Ankur Bhutani
You need to add getClobVal()and also need to rtrim()as it will return delimiter in the end of the results.
您需要添加getClobVal()并且还需要添加，rtrim()因为它会在结果的末尾返回分隔符。
SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',')
FROM tablename;
