ORA-00904: WM_CONCAT 부적합한 식별자 해결하기 / XMLAGG, WM_CONCAT, LISTAGG

ORA-00904: WM_CONCAT 부적합한 식별자 오류가 발생하였을 때, 해결방법에 대해 알아보도록 합시다. 우선, 부적합한 식별자라는 설명이 나오는 것은 현재 여러분이 사용하고 있는 오라클 데이터베이스의 내장 함수에 WM_CONCAT이 없다는 것을 뜻합니다.

WM_CONCAT 함수

WM_CONCAT 함수는 오라클에서 문서화(성문화)하여 지원하는 함수가 아닙니다. 그리고 12c 이상 버전 또는 Express Edition (XE)와 같은 환경에서는 기본적으로 WM_CONCAT을 사용할 수 없습니다. 오라클 11g 이상 버전에서는 WM_CONCAT을 대체할만한 LISTAGG WITHIN GROUP 와 같은 함수가 있으나, 상황이 여의치 않다면 다음과 같이 타입과 함수를 추가하여 해결할 수 있습니다. 다만 추천드리는 방법은 아니므로 LISTAGG 와 같은 방식 등을 사용하는 방법을 권장합니다.

ORA-00904: WM_CONCAT 부적합한 식별자 해결하기

타입과 타입바디, 그리고 펑션을 추가하여 해결하는 방법을 안내해드리겠습니다. 다만, 타입과 펑션의 추가는 충분히 검토를 해보십시오.

WM_CONCAT_IMPL 타입 추가하기

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

WM_CONCAT_IMPL타입 바디 추가하기

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;

WM_CONCAT 펑션 추가하기

CREATE OR REPLACE FUNCTION WM_CONCAT (P1 VARCHAR2)
   RETURN VARCHAR2
   AGGREGATE USING WM_CONCAT_IMPL;

*필요에 따라 펑션 권한을 부여해 주십시오.

WM_CONCAT 요약

사용자 정의 집계함수 생성하는 방법 (User-Defined Aggregate Functions)
오라클 버전에 따라 대체 함수 사용하기(XMLAGG, WM_CONCAT, LISTAGG)
해당 두 가지 방법에 대해 안내해드리겠습니다.

ORA-00904: WMSYS.WM_CONCAT : invalid identifier

오라클 XE 와 같이 WM_CONCAT 함수가 지원이 되지 않는 경우 invalid identifier 오류가 발생합니다.

사용자 정의 집계함수 생성하는 방법

사용자 정의 집계함수를 생성하는 스크립트 예시는 다음과 같습니다. (필요에 따라 가공)

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

함수를 생성하면 상기와 같이 정상적으로 조회가 될 것입니다. (예시쿼리입니다.)

WITH TA AS (    SELECT   LEVEL COL
                  FROM   DUAL
            CONNECT BY   LEVEL <= 3)
SELECT   TO_CHAR (WMSYS.WM_CONCAT (COL))
  FROM   TA;

오라클 버전에 따라 대체 함수 사용하기(XMLAGG, WM_CONCAT, LISTAGG)

버전에 따라 다른 대체 함수를 사용하는 방법입니다.

xml 형식으로 변환하여 추출하는 방법, listagg 함수를 사용하는 방법 등이 있습니다.

WITH TA
     AS (    SELECT LEVEL COL
               FROM DUAL
         CONNECT BY LEVEL <= 3)
SELECT SUBSTR (
          XMLAGG (XMLELEMENT (X, ',', COL) ORDER BY COL).EXTRACT ('//text()'),
          2)
          "9i",
       TO_CHAR (WMSYS.WM_CONCAT (COL)) "10g",
       LISTAGG (COL, ',') WITHIN GROUP (ORDER BY COL) "11g"
  FROM TA;