之前的项目突然出现一个错误,ORA-06575: 程序包或函数 WM_CONCAT 处于无效状态

对应的sql如下

SELECT u.LOGIN_NAME,
       u.REAL_NAME,
       u.ID,
       wm_concat(u.ORG_ID)   AS ORG_ID,
       wm_concat(u.ORG_NAME) AS ORG_NAME,
       wm_concat(u.ORG_CODE) AS ORG_CODE,
       u.SEX,
       u.PHONE,
       u.EMAIL,
       u.AVATAR,
       u.CARD_NUM,
       u.BPICTURE,
       u.APICTURE,
       u.INFO,
       u.NOTE,
       u.CARD_TYPE,
       u.UTIME,
       u.CTIME,
       u.ORDERS,
       u.STATUS,
       u.LABEL,
       u.USER_TYPE
FROM V_UUMS_USER_AND_INFO u
WHERE u.STATUS = 1
GROUP BY u.LOGIN_NAME, u.REAL_NAME, u.ID, u.SEX, u.PHONE, u.EMAIL, u.AVATAR, u.CARD_NUM, u.BPICTURE, u.APICTURE, u.INFO,
         u.NOTE, u.CARD_TYPE, u.UTIME, u.CTIME, u.ORDERS, u.STATUS, u.LABEL, u.USER_TYPE
ORDER BY NVL(u.UTIME, u.CTIME) DESC NULLS LAST

之前都是正常的,感觉很奇怪,百度下是因为在10以上的oracle 版本中，不再使用该函数，而是使用了listagg函数来代替

调整后

SELECT
    u.LOGIN_NAME,
    u.REAL_NAME,
    u.ID,
    LISTAGG(u.ORG_ID, ', ') WITHIN GROUP (ORDER BY u.ORG_ID) AS ORG_ID,
    LISTAGG(u.ORG_NAME, ', ') WITHIN GROUP (ORDER BY u.ORG_NAME) AS ORG_NAME,
    LISTAGG(u.ORG_CODE, ', ') WITHIN GROUP (ORDER BY u.ORG_CODE) AS ORG_CODE,
    u.SEX,
    u.PHONE,
    u.EMAIL,
    u.AVATAR,
    u.CARD_NUM,
    u.BPICTURE,
    u.APICTURE,
    u.INFO,
    u.NOTE,
    u.CARD_TYPE,
    u.UTIME,
    u.CTIME,
    u.ORDERS,
    u.STATUS,
    u.LABEL,
    u.USER_TYPE
FROM
    V_UUMS_USER_AND_INFO u
WHERE
        u.STATUS = 1
GROUP BY
    u.LOGIN_NAME, u.REAL_NAME, u.ID, u.SEX, u.PHONE, u.EMAIL, u.AVATAR, u.CARD_NUM, u.BPICTURE, u.APICTURE, u.INFO,
    u.NOTE, u.CARD_TYPE, u.UTIME, u.CTIME, u.ORDERS, u.STATUS, u.LABEL, u.USER_TYPE
ORDER BY
    NVL(u.UTIME, u.CTIME) DESC NULLS LAST;