Использование WMSYS.WM_CONCAT с Oracle XE 10g

Когда я пытаюсь использовать WMSYS.WM_CONCAT с Oracle XE 10g, я получаю сообщение об ошибке компиляции: ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier. Может ли кто-нибудь подтвердить, что это действительно связано с тем, что в XE отсутствует эта (недокументированная) функция? Если да, то можно ли его включить в XE?


oracle oracle-xe oracle10g ora-00904 wm-concat
person Stephen Swensen    schedule 18.08.2010    source источник

Ответы (3)


arrow_upward
3
arrow_downward

Я нашел пару справочных сайтов, но мне не повезло включить его. В итоге я написал свою собственную функцию для обработки конкатенации.

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;/

Тогда его можно назвать

SELECT distinct CONCAT_LIST(CURSOR(SELECT id FROM test_table1), ',') test_table1 FROM dual

person RandyB    schedule 03.09.2010
comment
Спасибо, мне очень любопытно, можно ли включить эту функцию. Лучшей альтернативой, которую я нашел, была пользовательская агрегатная функция здесь: oracle-base. com/articles/misc/StringAggregationTechniques.php. Я переименовал функцию из string_agg в wm_concat, так как теперь я могу делиться хранимыми процедурами со своими коллегами, у которых есть полный oracle 10g (метод, приведенный в статье, допускает тот же синтаксис, который вы использовали бы с реальным wm_concat — до тех пор, пока вы не используя скрипты, которые полностью определяют wm_concat с wm_sys) - person Stephen Swensen; 05.09.2010
comment
Это довольно гладко. У меня есть пара больших запросов, которые я использую для этой функции. Как только я вернусь в офис, я создам новый и проверю работоспособность. Это меня любопытно. Спасибо! - person RandyB; 05.09.2010
comment
Круто, я хотел бы услышать ваши результаты производительности, когда вы закончите. - person Stephen Swensen; 06.09.2010
comment
Я провел нагрузочное тестирование обеих функций, CONCAT_LIST и STRING_AGG, и обнаружил, что мне нужно внести одну модификацию в свою функцию. Я забыл закрыть курсор и довольно быстро исчерпал допустимую сумму. После внесения изменений я создал запрос, который сгруппировал набор из 250 магазинов по соответствующему типу. CONCAT_LIST завершился за 2,68 секунды, а STRING_AGG из oracle-base завершился за 0,38 секунды и выглядит намного чище. Я изменил несколько запросов, которые мне приходилось ссылаться на эту новую функцию. Я думал, что смогу помочь тебе, и в итоге ты помог мне. БЛАГОДАРНОСТЬ! - person RandyB; 16.09.2010

arrow_upward
3
arrow_downward

Источник: ссылка.

Просто создайте эту функцию самостоятельно:

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;
/
person smnbbrv    schedule 03.03.2014

arrow_upward
3
arrow_downward

Не рекомендуется использовать WM_CONCAT, так как это недокументированная функция, и она была удалена из версии 12c. См. Почему бы не использовать WM_CONCAT. работать в Oracle?

Если вы используете 11gR2 и выше, используйте LISTAGG.

Для версии до 11g, где LISTAGG не поддерживается, можно использовать функции ROW_NUMBER() и SYS_CONNECT_BY_PATH.

Например,

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.
person Lalit Kumar B    schedule 12.09.2015