解决Oracle高版本 ORA-00904: “WM_CONCAT“: 标识符无效

若使用wm_concat函数较少，可用listagg替代

SELECT listagg(name_, ',') WITHIN GROUP (ORDER BY name_) AS name_list FROM test_data;

在19c中手动创建wm_concat函数

-- 1
CREATE OR REPLACE TYPE WM_CONCAT_IMPL 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
);
 
-- 2
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;
 
-- 3
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2) RETURN VARCHAR2
    AGGREGATE USING WM_CONCAT_IMPL;

注意：本文归作者所有，未经作者允许，不得转载