ORA-00904: "wm_concat":invalid identifier錯誤如何解決？

xiaowuhexiaoli發表於2020-07-06
IDE

本文轉載，來源： https://cloud.tencent.com/developer/article/1515645


若在建立資料庫的時候沒有建立WMSYS使用者，則在SQL或PL/SQL中有用到WM_CONCAT函式的時候就會報ORA-00904的錯誤。其實，WMSYS使用者下的WM_CONCAT函式有很重要的用途，比如行轉列，但是該函式不穩定。例如，在Oracle 10g上返回的是字串型別，但是在Oracle 11gR2上返回的是CLOB型別。很多資料庫開發人員在程式中都使用了該函式，若是系統升級，則會導致程式出現錯誤。為了減輕程式設計師修改程式的工作量，只有重建函式WM_CONCAT來解決該問題。

若沒有建立WMSYS使用者的話，則在查詢DBA_OBJECTS檢視的時候就不能查詢到WM_CONCAT的相關資訊。在正常情況下查詢DBA_OBJECTS檢視，會有如下的資訊：

 SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'WM_CONCAT%';

解決辦法有兩種，一種是採用Oracle本身的指令碼來建立WM_CONCAT函式，一種是採用自己建立的函式來解決這個問題。

1、用Oracle自帶指令碼重建WMSYS使用者的WMSYS.WM_CONCAT函式

執行如下指令碼解除安裝WMSYS使用者的資料：

@$ORACLE_HOME/rdbms/admin/owmuinst.plb

執行如下指令碼安裝WMSYS使用者即可建立WMSYS.WM_CONCAT函式：

@$ORACLE_HOME/rdbms/admin/owminst.plb

解鎖WMSYS使用者：

ALTER USER WMSYS ACCOUNT UNLOCK;

2、自己訂製指令碼

如果只是單個使用者使用，那麼不用刻意去建立WMSYS使用者，可以在所需的使用者下執行訂製指令碼，生成WM_CONCAT函式。另外，為了和系統的函式名區別開來，也可以修改函式名稱。如果是多個使用者使用，也可以執行自己定製的指令碼，然後建立同義詞，這樣多個使用者都可以使用。

下面按照返回值的不同分為幾種情況來訂製不同的指令碼。

①　無分隔符，返回CLOB型別

建立函式的指令碼如下所示：

CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_NULL_LHR AUTHID CURRENT_USER AS OBJECT(
  CURR_STR CLOB,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                       P1   IN CLOB) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                         RETURNVALUE OUT CLOB,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)
    RETURN NUMBER);/CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_NULL_LHR IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)
    RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL_CLOB_NULL_LHR(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                       P1   IN CLOB) 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_CLOB_NULL_LHR,
                                         RETURNVALUE OUT CLOB,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)
    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_CLOB_NULL_LHR(P1 VARCHAR2) RETURN CLOB
  AGGREGATE USING WM_CONCAT_IMPL_CLOB_NULL_LHR;/CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_NULL_LHR FOR WM_CONCAT_CLOB_NULL_LHR;GRANT EXECUTE ON WM_CONCAT_CLOB_NULL_LHR TO PUBLIC;

以上函式的測試示例如下所示，函式的返回值是無分隔符的CLOB，在PL/SQL中要使用TO_CHAR進行轉換：

SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
   USER_ID----------
         0
         5SYS@lhrdb21> SELECT WM_CONCAT_CLOB_NULL_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);WM_CONCAT_CLOB_LHR_NULL(D.USER_ID)--------------------------------------------------------------------------------05

②　逗號分隔符，返回CLOB

建立函式的指令碼如下所示：

CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_LHR AUTHID CURRENT_USER AS OBJECT(
  CURR_STR CLOB,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
                                       P1   IN CLOB) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_LHR,
                                         RETURNVALUE OUT CLOB,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)
    RETURN NUMBER);/CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_LHR IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)
    RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL_CLOB_LHR(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
                                       P1   IN CLOB) 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_CLOB_LHR,
                                         RETURNVALUE OUT CLOB,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)
    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_CLOB_LHR(P1 VARCHAR2) RETURN CLOB
  AGGREGATE USING WM_CONCAT_IMPL_CLOB_LHR;/CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_LHR FOR WM_CONCAT_CLOB_LHR;GRANT EXECUTE ON WM_CONCAT_CLOB_LHR TO PUBLIC;

以上函式的測試示例如下所示，函式的返回值是以逗號為分隔符的CLOB，在PL/SQL中需要使用TO_CHAR進行轉換：

SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
   USER_ID----------
         0
         5SYS@lhrdb21> SELECT WM_CONCAT_CLOB_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);WM_CONCAT_LHR(D.USER_ID)--------------------------------------------------------------------------------0,5

③　逗號分隔符，返回字串型別

建立函式的指令碼如下所示：

CREATE OR REPLACE TYPE WM_CONCAT_IMPL_STRINGS_LHR AUTHID CURRENT_USER AS OBJECT(
  CURR_STR VARCHAR2(32767),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
                                       P1   IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_STRINGS_LHR,
                                         RETURNVALUE OUT VARCHAR2,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)
    RETURN NUMBER);/CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_STRINGS_LHR IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)
    RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL_STRINGS_LHR(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
                                       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_STRINGS_LHR,
                                         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_STRINGS_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)
    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_STRINGS_LHR(P1 VARCHAR2) RETURN VARCHAR2
  AGGREGATE USING WM_CONCAT_IMPL_STRINGS_LHR;/CREATE PUBLIC SYNONYM WM_CONCAT_STRINGS_LHR FOR WM_CONCAT_STRINGS_LHR;GRANT EXECUTE ON WM_CONCAT_STRINGS_LHR TO PUBLIC;

以上函式的測試示例如下所示，函式的返回值是以逗號為分隔符的字串：

SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
   USER_ID----------
         0
         5SYS@lhrdb21> SELECT WM_CONCAT_STRINGS_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);WM_CONCAT_STRINGS_LHR(D.USER_ID)---------------------------------------------------0,5

其實，與WM_CONCAT相似的還有一個函式是LISTAGG。這是一個Oracle的列轉行函式，使用示例如下所示：

WITH TEMP AS(
  SELECT 'China' NATION ,'Guangzhou' CITY FROM DUAL UNION ALL
  SELECT 'China' NATION ,'Shanghai' CITY FROM DUAL UNION ALL
  SELECT 'China' NATION ,'Beijing' CITY FROM DUAL UNION ALL
  SELECT 'USA' NATION ,'New York' CITY FROM DUAL UNION ALL
  SELECT 'USA' NATION ,'Bostom' CITY FROM DUAL UNION ALL
  SELECT 'USA' NATION ,'Bostom' CITY FROM DUAL UNION ALL
  SELECT 'Japan' NATION ,'Tokyo' CITY FROM DUAL)SELECT NATION,LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY)FROM TEMP
GROUP BY NATION;

輸出結果如下所示：

對於LISTAGG函式，如果聚合的內容太多就會報“ORA-01489: result of string concatenation is too long”的錯誤，那麼這個時候可以從業務的角度去修改SQL，也可以使用WM_CONCAT函式返回CLOB型別來解決這個問題。

& 說明：

有關字串中是否含義漢字和多位元組字元的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2124931/


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69978530/viewspace-2702752/，如需轉載，請註明出處，否則將追究法律責任。

相關文章