结合使用WMSYS.WM_CONCAT和Oracle XE 10g [英] Using WMSYS.WM_CONCAT with Oracle XE 10g

问题描述

当我尝试将WMSYS.WM_CONCAT与Oracle XE 10g一起使用时，出现编译错误:ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier.任何人都可以验证这确实是由于XE缺少此(未记录)功能造成的吗?如果是这样，是否有必要在XE中启用它?

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?

推荐答案

建议不要使用 WM_CONCAT ，因为它是未记录的功能，并且已从12c版本中删除.请参见 为什么不使用WM_CONCAT在Oracle中发挥作用?

It is recommended not to use WM_CONCAT since it is an undocumented feature and it has been removed from 12c version. See Why not use WM_CONCAT function in Oracle?

如果您使用的是 11gR2 及更高版本，请使用 LISTAGG .

If you are on 11gR2 and up, use LISTAGG.

对于版本 11g之前的版本(其中不支持LISTAGG)，可以使用 ROW_NUMBER()和 SYS_CONNECT_BY_PATH 函数.

For version prior to 11g where LISTAGG is not supported, you could use ROW_NUMBER() and SYS_CONNECT_BY_PATH functions.

例如，

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.

这篇关于结合使用WMSYS.WM_CONCAT和Oracle XE 10g的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！