ORA-00904:"WM_CONCAT":標識符無效

今天客戶現場業務甩給我一個報錯讓處理，ora-00904:"WM_CONCAT"：標識符無效。

初步分析了下場景：

前天因為在原先一套庫要刪除，而他們業務用戶A使用的便是該套計劃刪除的庫，故導出A用戶，導入到新環境中，expdp/impdp導入導出中均無報錯。昨天在新環境給恢復成功，今天業務反映有報錯，在新環境中查看確實沒有該function，而老環境中有該function，但是是用戶B的，猜測之前老環境是給A建立了B的synonym。

解決方法：

1、獲取創建該函數的DDL語句，在新環境中直接創建該function

SQL> set long 999
SQL> select dbms_metadata.get_ddl('FUNCTION','WM_CONCAT','TBCS') from dual;
DBMS_METADATA.GET_DDL('FUNCTION','WM_CONCAT','TBCS')
--------------------------------------------------------------------------------
CREATE OR REPLACE EDITIONABLE FUNCTION "TBCS"."WM_CONCAT" (P1 VARCHAR2)
RETURN clob AGGREGATE USING  WM_CONCAT_IMPL;

執行DDL語句

SQL> CREATE OR REPLACE EDITIONABLE FUNCTION "AOPEN"."WM_CONCAT" (P1 VARCHAR2) RETURN clob AGGREGATE USING  WM_CONCAT_IMPL;
  2  /
Warning: Function created with compilation errors.
SQL>

此操作后，明顯報錯了，讓業務測試，肯定測不過，查看函數是無效的

SQL> select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME from dba_objects where object_name=upper('wm_concat') ;
OWNER                OBJECT_NAME                    SUBOBJECT_NAME                 OBJECT_TYPE             LAST_DDL_TIME
-------------------- ------------------------------ ------------------------------ ----------------------- ---------------
A                    WM_CONCAT                                                     FUNCTION                20191121 172355
SQL>

2、導出function在導入新環境

由于第一種方法不行，故考慮第二種方法，把老環境B用戶的wm_concat函數遷移到新環境，測試可行否

導出function

expdp \'/ as sysdba \' directory=expdp_2019 dumpfile=function_wm_concat_20191121.dmp logfile=function_wm_concat_expdp_20191121.log schemas=B include=function:\"IN \'WM_CONCAT\'\"
Export: Release 12.1.0.2.0 - Production on Thu Nov 21 17:46:38 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_02":  "/******** AS SYSDBA" directory=expdp_2019 dumpfile=function_wm_concat_20191121.dmp logfile=function_wm_concat_expdp_20191121.log schemas=B include=function:"IN 'WM_CONCAT'" 
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:
  /ora12c/oracle/dir_dump/function_wm_concat_20191121.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Nov 21 17:47:27 2019 elapsed 0 00:00:47

導入新環境，使用戶轉換，由B轉換為A

impdp \'/ as sysdba \' directory=DIR_DUMP dumpfile=function_wm_concat_20191121.dmp logfile=function_wm_concat_impdp_20191121.log remap_schema=B:A
Import: Release 12.1.0.2.0 - Production on Thu Nov 21 17:48:53 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=DIR_DUMP dumpfile=function_wm_concat_20191121.dmp logfile=function_wm_concat_impdp_20191121.log remap_schema=B:A
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type FUNCTION:"AOPEN"."WM_CONCAT" created with compilation warnings
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Nov 21 17:49:00 2019 elapsed 0 00:00:05

查看函數狀態，依舊invalid

SQL> select owner,object_name,object_type ,status from dba_objects where owner not in('SYS', 'SYSTEM') AND status != 'VALID' ;
OWNER                          OBJECT_NAME                    OBJECT_TYPE             STATUS
------------------------------ ------------------------------ ----------------------- -------
A                               WM_CONCAT                      FUNCTION                INVALID

3、手工創建該函數

考慮到第一種方法只是創建了function，而包體等內容并沒有創建，故手工創建package、body、function

a、以sys用戶創建包、包體、函數

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
);

b、創建類型body

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;

c、創建函數

CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;

d、創建同義詞并授權

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

然后查看function狀態，正常狀態。至此問題解決。

總結

1、從網上找資料了解到，11gr2和12C上已經摒棄了wm_concat函數，而應用在程序中確使用了該函數，導致程序出現錯誤，為了減輕程序員修改程序的工作量，只有通過手工創建個wm_concat函數，來臨時解決該問題，但是注意，及時創建了該函數，在使用的過程中，也需要用to_char(wm_concat())方式，才能完全替代之前的應用。

2、也查到了說VMSYS用戶下有該函數，但是默認該用戶是鎖定的，此次問題我也解鎖了VMSYS用戶，但是并無該function

SQL> alter user WMSYS account unlock;
User altered.
SQL>  select owner,object_name,object_type from dba_objects where owner='WMSYS' and object_type ='FUNCTION';
no rows selected

3、手工重建，保留腳本以后出現類似問題可以很快速重建。

更多相關內容：

ORA-00911: 無效字符問題案例以及解決方法

oracle監聽器啟動1067錯誤處理的方法