在 Oracle XE 10g 中使用 WMSYS.WM_CONCAT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3513787/
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
Using WMSYS.WM_CONCAT with Oracle XE 10g
提问by Stephen Swensen
When I try to use WMSYS.WM_CONCAT with Oracle XE 10g, I receive a compilation error: ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier. Can anyone verify that this is indeed due to XE lacking this (undocumented) feature? If so, is there anyway to enable it in XE?
当我尝试将 WMSYS.WM_CONCAT 与 Oracle XE 10g 一起使用时，我收到一个编译错误：ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier。任何人都可以验证这确实是由于 XE 缺少此（未记录的）功能吗？如果是这样，无论如何要在 XE 中启用它？
回答by RandyB
I had found a couple reference sites, but had no luck enabling it. I ended up writing my own function to handle the concatenation.
我找到了几个参考站点，但没有运气启用它。我最终编写了自己的函数来处理连接。
CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR, sep Varchar2 ) RETURN VARCHAR2 IS
ret VARCHAR2(32000);
tmp VARCHAR2(4000);
BEGIN
loop
fetch cur into tmp;
exit when cur%NOTFOUND;
if ret is null then
ret := tmp;
else
ret := ret || sep || tmp;
end if;
end loop;
RETURN ret; END;/
Then it can be called as
那么它可以被称为
SELECT distinct CONCAT_LIST(CURSOR(SELECT id FROM test_table1), ',') test_table1 FROM dual
SELECT distinct CONCAT_LIST(CURSOR(SELECT id FROM test_table1), ',') test_table1 FROM dual
回答by smnbbrv
Source: link.
来源：链接。
Just create this function by yourself:
只需自己创建此功能：
CREATE OR REPLACE TYPE wm_concat_impl
AUTHID CURRENT_USER
AS OBJECT (
curr_str VARCHAR2 (32767),
STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT wm_concat_impl,
p1 IN VARCHAR2
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (
SELF IN wm_concat_impl,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT wm_concat_impl,
sctx2 IN wm_concat_impl
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY wm_concat_impl
IS
STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl)
RETURN NUMBER
IS
BEGIN
sctx := wm_concat_impl (NULL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT wm_concat_impl,
p1 IN VARCHAR2
)
RETURN NUMBER
IS
BEGIN
IF (curr_str IS NOT NULL)
THEN
curr_str := curr_str || ',' || p1;
ELSE
curr_str := p1;
END IF;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate (
SELF IN wm_concat_impl,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := curr_str;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT wm_concat_impl,
sctx2 IN wm_concat_impl
)
RETURN NUMBER
IS
BEGIN
IF (sctx2.curr_str IS NOT NULL)
THEN
SELF.curr_str := SELF.curr_str || ',' || sctx2.curr_str;
END IF;
RETURN odciconst.success;
END;
END;
/
CREATE OR REPLACE FUNCTION wm_concat (p1 VARCHAR2)
RETURN VARCHAR2
AGGREGATE USING wm_concat_impl;
/
回答by Lalit Kumar B
It is recommended not to use WM_CONCATsince it is an undocumented feature and it has been removed from 12c version. See Why not use WM_CONCAT function in Oracle?
建议不要使用WM_CONCAT，因为它是一个未记录的功能并且已从 12c 版本中删除。请参阅为什么不在 Oracle 中使用 WM_CONCAT 函数？
If you are on 11gR2and up, use LISTAGG.
如果您使用的是11gR2及更高版本，请使用LISTAGG。
For version prior to 11gwhere LISTAGGis not supported, you could use ROW_NUMBER()and SYS_CONNECT_BY_PATHfunctions.
对于版本11克之前的地方LISTAGG不支持，你可以使用ROW_NUMBER（）和SYS_CONNECT_BY_PATH功能。
For example,
例如，
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM (SELECT deptno,
ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
