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

xiaowuhexiaoli發表於2020-07-06

本文轉載，來源： 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/，如需轉載，請註明出處，否則將追究法律責任。

【LISTAGG】 ORA-00904: "WM_CONCAT": invalid identifier (Doc
2021-06-04
IDE
ORA-00904: invalid identifier
2013-03-29
IDE
ORA-00904: "DROP_SEGMENTS": invalid identifier
2009-05-19
IDE
Python 錯誤 SyntaxError: invalid character in identifier
2020-03-21
PythonErrorIDE
exp匯出出現：ORA-00904: : invalid identifier
2014-11-17
IDE
exp匯出出現：ORA-00904: "POLTYP": invalid identifier
2010-11-30
IDE
關於merge時，出現的ORA-00904 invalid identifier
2008-08-12
IDE
Invalid bound statement (not found)錯誤解決
2016-05-18
[20200312]ORA-00904 POLTYP invalid identifier.txt
2020-03-12
IDE
域名解析錯誤如何解決？
2019-11-15
[20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txt
2021-04-21
IDEOBJ
爬蟲錯誤程式碼如何解決？
2022-06-16
爬蟲
如何解決ORA-00845錯誤
2011-04-15
MyBatis 錯誤：Invalid bound statement (not found)
2016-12-07
MyBatis
std::sort 錯誤"Expression : invalid operator <"
2024-03-26
Express
如何解決ORA-04031 錯誤(轉)
2019-07-11
如何解決寬頻連線錯誤769
2016-11-02
如何解決寬頻連線錯誤651
2016-11-02
如何解決ORA-04031 錯誤(zt)
2005-04-06
如何解決該錯誤？（與Struts有關）
2004-08-04
[20211022]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts(補充).txt
2021-10-22
IDEOBJ
oracle很奇怪的報ora-00904錯誤的討論
2010-01-20
Oracle
Checkpoint log:invalid bitmap page錯誤修復
2020-11-05
ORA-600(kkoipt:invalid join method)錯誤
2011-09-19
雲伺服器如何解決DNS解析錯誤故障
2021-04-19
伺服器DNS
mysql服務啟動1053錯誤如何解決
2021-04-06
MySql
資料分析中會常犯哪些錯誤，如何解決？
2020-10-09
如何解決url傳參導致錯誤問題
2017-03-15
如何解決 VLC 影片嵌入字幕中遇到的錯誤
2017-08-21
jdon到最後出現的錯誤..如何解決
2006-10-10
win10寬頻連線錯誤提示錯誤程式碼為651如何解決
2020-06-17
Win10
AFNetworking 2.0 出現Use of undeclared identifier AFURLSessionManager錯誤
2013-12-09
IDESession
微信分享JSSDK-invalid signature簽名錯誤的解決方案
2017-04-19
JS
如何判斷DNS解析故障？如何解決DNS解析錯誤？
2022-06-20
DNS
win7寬頻連線錯誤738如何解決？
2017-07-12
Win7
windows10系統下HTTP錯誤400如何解決
2018-07-10
WindowsHTTP
憋了1.5天了，這個錯誤提示如何解決？
2004-06-02
Sublime修復錯誤【xcrun: error: invalid active developer path… 】
2016-11-02
ErrorDeveloper

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

相關文章