Почему wm_concat не работает здесь?

Question

У меня есть этот запрос:

(SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN
    (SELECT ITEM FROM TABLE(CR_FN_SPLIT_STRING('28,56',','))))

что возвращает:

Но, когда я делаю:

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',','))))

Я получаю пустой результат... что я делаю не так?

Answer 1

Вы должны избегать wm_concat функция, потому что она недокументирована и обнаружена как обходной путь в Oracle 8i раз.

Со времен старого метода с пользовательской агрегатной функцией, обнаруженного Томом Кайтом, здесь есть несколько новых обходных путей, показанных в примерах ниже.

Все они воспроизведены в этой SQL Fiddle.

Обходной путь 1 - функция LISTAGG, работает в 11g:

select listagg(object_id,',') within group (order by rownum) id_string
from cr_object_group_entries_vw

Обходной путь 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

Обходной путь 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

PS Я не знал точно, в каких версиях Oracle sys_connect_by_path а также xmlagg был представлен, но оба хорошо работают на 10.2.0.4.0

Answer 2

Если вы на 11g попробуйте LISTAGG вместо wm_concat для начинающих.

Answer 3

Я только что видел этот пост относительно wm_concat и думал поделиться какой-то информацией.

Любое приложение, на которое полагались wm_concat функция не будет работать после обновления до 12c, С тех пор он был удален из последней версии 12c. См. Почему бы не использовать функцию WM_CONCAT в Oracle?

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.

Вы получите ошибку "неверный идентификатор":

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

Поэтому нет смысла полагаться на недокументированную функцию, которая больше не доступна в последних версиях.

Альтернативные решения см. В разделе Методы агрегации строк Oracle.

Answer 4

Вы, кажется, не делаете ничего плохого. С фиктивной табличной функцией для возврата данных, которые вы показали, 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      

Вы отметили вопрос как [11g]; как сказал @beherenow, если вы можете использовать поддерживаемые lisgagg по неподдерживаемым 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 (для listagg только, так как он не поддерживает wm_concat - может быть, ваш экземпляр тоже нет, но тогда он должен выдать ошибку?)