SQL 为什么 wm_concat 在这里不起作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16674927/
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
Why does the wm_concat not work here?
提问by sprocket12
I have this query :
我有这个查询：
(SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN
(SELECT ITEM FROM TABLE(CR_FN_SPLIT_STRING('28,56',','))))
that returns :
返回：
But when I do :
但是当我这样做时：
SELECT wm_concat(object_id) FROM
(SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN
(SELECT ITEM FROM TABLE(CR_FN_SPLIT_STRING('28,56',','))))
I get a blank result... what am I doing wrong?
我得到一个空白的结果......我做错了什么？
回答by ThinkJet
You must avoid wm_concatfunction because it is undocumented and discovered as workaround at Oracle 8i times.
您必须避免使用wm_concat函数，因为它没有记录并且在 Oracle 8i 时代被发现为解决方法。
Since times of old method with custom aggregate function as discovered by Tom Kyte herethere are some new workarounds, showed at examples below.
由于 Tom Kyte在这里发现了带有自定义聚合函数的旧方法，因此有一些新的解决方法，如下面的示例所示。
All of them reproduced in this SQL Fiddle.
所有这些都在此 SQL Fiddle 中重现。
Workaround 1 - LISTAGG function, works in 11g:
解决方法 1 - LISTAGG 函数，适用于 11g：
select listagg(object_id,',') within group (order by rownum) id_string
from cr_object_group_entries_vw
Workaround 2 - SYS_CONNECT_BY_PATH, works since 10g:
解决方法 2 - SYS_CONNECT_BY_PATH，从 10g 开始工作：
select id_string from (
select rn, substr(sys_connect_by_path(object_id, ','),2) id_string
from (select object_id, rownum rn from cr_object_group_entries_vw)
start with rn = 1
connect by prior rn + 1 = rn
order by rn desc
)
where rownum = 1
Workaround 3 - XMLAGG, works since 10g:
解决方法 3 - XMLAGG，从 10g 开始工作：
select replace(
replace(
replace(
xmlagg(xmlelement("x",object_id)).getStringVal(),
'</x><x>',
','
),
'<x>',
''
),
'</x>',
''
) id_string
from cr_object_group_entries_vw
P.S. I didn't know exactly in which Oracle versions sys_connect_by_pathand xmlaggwas introduced, but both works well on 10.2.0.4.0
PS我不知道究竟在Oracle版本sys_connect_by_path和xmlagg被介绍，但都效果很好的10.2.0.4.0
回答by Kirill Leontev
回答by Lalit Kumar B
I just saw this post regarding wm_concatand thought to share some information.
我刚刚看到这篇关于wm_concat并想分享一些信息的帖子。
Any application which has had been relying on wm_concatfunction will not work once upgraded to 12c. Since, it has been removed from the latest 12c version. See Why not use WM_CONCAT function in Oracle?
任何一直依赖wm_concat功能的应用程序一旦升级到12c. 从那以后，它已从最新的 12c 版本中删除。请参阅为什么不在 Oracle 中使用 WM_CONCAT 函数？
SQL> select banner from v$version where rownum = 1;
BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
SQL> SELECT object_name
2 FROM dba_objects
3 WHERE owner='WMSYS'
4 AND object_name LIKE 'WM\_%' ESCAPE '\';
OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES
20 rows selected.
You will receive an “invalid identifier” error:
您将收到“无效标识符”错误：
SQL> SELECT banner FROM v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
*
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier
Therefore, there is no point relying on an undocumented featurewhich is no more made available in latest versions.
因此，依赖最新版本中不再提供的未记录功能是没有意义的。
For alternate solutions, please see Oracle String Aggregation Techniques
有关替代解决方案，请参阅Oracle 字符串聚合技术
回答by Alex Poole
You don't appear to be doing anything wrong. With a dummy table function to return the data you showed, wm_concatworked for me:
你似乎没有做错任何事。使用虚拟表函数返回您显示的数据，wm_concat对我有用：
select wm_concat(object_id) from
(select object_id from cr_object_group_entries_vw where object_group_id in
(select item from table(cr_fn_split_string('28,56',','))))
/
WM_CONCAT(OBJECT_ID)
--------------------------------------------------------------------------------
36,1,11,121,13,14,17,18,2,24,3,32,33,34,35,36,37,38,39,40,42,43,44,6,7,8,81
You've tagged the question as [11g]; as @beherenow said, if you can you should use the supported lisgaggover the unsupported wm_concat, though it's only available from 11gR2 I think:
您已将问题标记为 [11g]；正如@beherenow 所说，如果可以的话，您应该使用 supportlisgagg而非 unsupported wm_concat，尽管我认为它只能从 11gR2 获得：
select listagg(object_id, ',') within group (order by object_id)
from cr_object_group_entries_vw
where object_group_id in
(select item from table(cr_fn_split_string('28,56',',')))
/
LISTAGG(OBJECT_ID,',')WITHINGROUP(ORDERBYOBJECT_ID)
---------------------------------------------------------------------------
1,11,121,13,14,17,18,2,24,3,32,33,34,35,36,36,37,38,39,40,42,43,44,6,7,8,81
SQL Fiddle(for listaggonly, since it doesn't support wm_concat- maybe your instance doesn't either, but then it should error?)
SQL Fiddle（listagg仅用于，因为它不支持wm_concat- 也许您的实例也不支持，但它应该出错？）
