Oracle-"WM_CONCAT": 标识符无效

发表于 更新于 分类于

“WM_CONCAT”: 标识符无效

概述

11gr2和12C上已经摒弃了wm_concat函数，当时我们很多程序员在程序中确使用了该函数，导致程序出现错误，为了减轻程序员修改程序的工作量，只有通过手工创建个wm_concat函数，来临时解决该问题，但是注意，及时创建了该函数，在使用的过程中，也需要用to_char(wm_concat())方式，才能完全替代之前的应用。

方案

  1. 解锁sys用户

    1
    alter user sys account unlock;

  2. 创建包，包体，函数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    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 );

  3. 定义类型body

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    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;

  4. 自定义行变列函数

    1
    2
    3
    4
    CREATE OR REPLACE
    FUNCTION
        wm_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE
            USING WM_CONCAT_IMPL ;

  5. 创建同义词并授权

    1
    2
    3
    4
    create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL;
    create public synonym wm_concat for sys.wm_concat;
    grant execute on WM_CONCAT_IMPL to public;
    grant execute on wm_concat to public;

注意

DBeaver这种类似的数据库管理工具，无法执行上述SQL脚本，建议使用PLSQL