oracle WM_CONCAT 用例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2779540/
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
WM_CONCAT use CASE
提问by Ruslan
I have a select:
我有一个选择：
select substr(acc,1,4)
,currency
, amount
, module
, count(*)
, wm_concat(trn_ref_no) trn
from all_entries
where date = to_date ('01012010','DDMMYYYY')
group by substr(acc,1,4),currency, amount, module
In this case I get an error:
ORA-06502: PL/SQL: : character string buffer too small ... "WMSYS.WM_CONCAT_IMPL"
在这种情况下，我收到一个错误：
ORA-06502: PL/SQL: : character string buffer too small ... "WMSYS.WM_CONCAT_IMPL"
To avoid buffer limit error I changed it to:
为了避免缓冲区限制错误，我将其更改为：
select substr(acc,1,4)
,currency
, amount
, module
, count(*)
, (case when count(*) < 10 then wm_concat(trn_ref_no) else null end) trn
from fcc.acvw_all_ac_entries
where trn_dt = to_date ('05052010','DDMMYYYY')
group by substr(acc,1,4),currency, amount, module
But even in this case i have the same error. How can i avoid this error?
但即使在这种情况下，我也有同样的错误。我怎样才能避免这个错误？
回答by Tony Andrews
WM_CONCAT returns a VARCHAR2, and so is constrained to a maximum of 4000 characters in SQL. You could write your own string aggregate function that returned a CLOB if you need more than that. However, it may be better to think about why you are doing this, and whether there isn't a better way altogether - e.g. using the 10G COLLECT function to return a collection.
WM_CONCAT 返回一个 VARCHAR2，因此在 SQL 中被限制为最多 4000 个字符。如果您需要更多，您可以编写自己的字符串聚合函数，该函数返回 CLOB。但是，最好考虑一下为什么要这样做，以及是否没有更好的方法 - 例如使用 10G COLLECT 函数返回一个集合。
See this article on string aggregation techniquesfor how you might write your own aggregate function.
请参阅有关字符串聚合技术的这篇文章，了解如何编写自己的聚合函数。
