ORA-00904: "wm_concat":invalid identifier错误如何解决？

DB宝 2019-04-25
1616
♣题目部分ORA-00904: "wm_concat":invalid identifier错误如何解决？

     
♣答案部分


若在创建数据库的时候没有创建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视图，会有如下的信息：
1 SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'WM_CONCAT%';



解决办法有两种，一种是采用Oracle本身的脚本来创建WM_CONCAT函数，一种是采用自己创建的函数来解决这个问题。
1、用Oracle自带脚本重建WMSYS用户的WMSYS.WM_CONCAT函数
运行如下脚本卸载WMSYS用户的数据：
1@$ORACLE_HOME/rdbms/admin/owmuinst.plb

运行如下脚本安装WMSYS用户即可创建WMSYS.WM_CONCAT函数：
1@$ORACLE_HOME/rdbms/admin/owminst.plb


解锁WMSYS用户：
1ALTER USER WMSYS ACCOUNT UNLOCK;


2、自己订制脚本
如果只是单个用户使用，那么不用刻意去创建WMSYS用户，可以在所需的用户下运行订制脚本，生成WM_CONCAT函数。另外，为了和系统的函数名区别开来，也可以修改函数名称。如果是多个用户使用，也可以运行自己定制的脚本，然后创建同义词，这样多个用户都可以使用。
下面按照返回值的不同分为几种情况来订制不同的脚本。
①　无分隔符，返回CLOB类型
创建函数的脚本如下所示：
 1CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_NULL_LHR AUTHID CURRENT_USER AS OBJECT
 2(
 3  CURR_STR CLOB,
 4  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)
 5    RETURN NUMBER,
 6  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
 7                                       P1   IN CLOB) RETURN NUMBER,
 8  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_NULL_LHR,
 9                                         RETURNVALUE OUT CLOB,
10                                         FLAGS       IN NUMBER)
11    RETURN NUMBER,
12  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
13                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)
14    RETURN NUMBER
15);
16/
17CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_NULL_LHR IS
18  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)
19    RETURN NUMBER IS
20  BEGIN
21    SCTX := WM_CONCAT_IMPL_CLOB_NULL_LHR(NULL);
22    RETURN ODCICONST.SUCCESS;
23  END;
24  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
25                                       P1   IN CLOB) RETURN NUMBER IS
26  BEGIN
27    IF (CURR_STR IS NOT NULL) THEN
28      CURR_STR := CURR_STR ||  P1;
29    ELSE
30      CURR_STR := P1;
31    END IF;
32    RETURN ODCICONST.SUCCESS;
33  END;
34  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_NULL_LHR,
35                                         RETURNVALUE OUT CLOB,
36                                         FLAGS       IN NUMBER) RETURN NUMBER IS
37  BEGIN
38    RETURNVALUE := CURR_STR;
39    RETURN ODCICONST.SUCCESS;
40  END;
41  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
42                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)
43    RETURN NUMBER IS
44  BEGIN
45    IF (SCTX2.CURR_STR IS NOT NULL) THEN
46      SELF.CURR_STR := SELF.CURR_STR ||  SCTX2.CURR_STR;
47    END IF;
48    RETURN ODCICONST.SUCCESS;
49  END;
50END;
51/
52CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_NULL_LHR(P1 VARCHAR2) RETURN CLOB
53  AGGREGATE USING WM_CONCAT_IMPL_CLOB_NULL_LHR;
54/
55CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_NULL_LHR FOR WM_CONCAT_CLOB_NULL_LHR;
56GRANT EXECUTE ON WM_CONCAT_CLOB_NULL_LHR TO PUBLIC;


以上函数的测试示例如下所示，函数的返回值是无分隔符的CLOB，在PL/SQL中要使用TO_CHAR进行转换：
1SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
2   USER_ID
3----------
4         0
5         5
6SYS@lhrdb21> SELECT WM_CONCAT_CLOB_NULL_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
7WM_CONCAT_CLOB_LHR_NULL(D.USER_ID)
8--------------------------------------------------------------------------------
905


②　逗号分隔符，返回CLOB
创建函数的脚本如下所示：
 1CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_LHR AUTHID CURRENT_USER AS OBJECT
 2(
 3  CURR_STR CLOB,
 4  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)
 5    RETURN NUMBER,
 6  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
 7                                       P1   IN CLOB) RETURN NUMBER,
 8  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_LHR,
 9                                         RETURNVALUE OUT CLOB,
10                                         FLAGS       IN NUMBER)
11    RETURN NUMBER,
12  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,
13                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)
14    RETURN NUMBER
15);
16/
17CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_LHR IS
18  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)
19    RETURN NUMBER IS
20  BEGIN
21    SCTX := WM_CONCAT_IMPL_CLOB_LHR(NULL);
22    RETURN ODCICONST.SUCCESS;
23  END;
24  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
25                                       P1   IN CLOB) RETURN NUMBER IS
26  BEGIN
27    IF (CURR_STR IS NOT NULL) THEN
28      CURR_STR := CURR_STR || ',' || P1;
29    ELSE
30      CURR_STR := P1;
31    END IF;
32    RETURN ODCICONST.SUCCESS;
33  END;
34  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_LHR,
35                                         RETURNVALUE OUT CLOB,
36                                         FLAGS       IN NUMBER) RETURN NUMBER IS
37  BEGIN
38    RETURNVALUE := CURR_STR;
39    RETURN ODCICONST.SUCCESS;
40  END;
41  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,
42                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)
43    RETURN NUMBER IS
44  BEGIN
45    IF (SCTX2.CURR_STR IS NOT NULL) THEN
46      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
47    END IF;
48    RETURN ODCICONST.SUCCESS;
49  END;
50END;
51/
52CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_LHR(P1 VARCHAR2) RETURN CLOB
53  AGGREGATE USING WM_CONCAT_IMPL_CLOB_LHR;
54/
55CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_LHR FOR WM_CONCAT_CLOB_LHR;
56GRANT EXECUTE ON WM_CONCAT_CLOB_LHR TO PUBLIC;


以上函数的测试示例如下所示，函数的返回值是以逗号为分隔符的CLOB，在PL/SQL中需要使用TO_CHAR进行转换：
1SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
2   USER_ID
3----------
4         0
5         5
6SYS@lhrdb21> SELECT WM_CONCAT_CLOB_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
7WM_CONCAT_LHR(D.USER_ID)
8--------------------------------------------------------------------------------
90,5


③　逗号分隔符，返回字符串类型
创建函数的脚本如下所示：
 1CREATE OR REPLACE TYPE WM_CONCAT_IMPL_STRINGS_LHR AUTHID CURRENT_USER AS OBJECT
 2(
 3  CURR_STR VARCHAR2(32767),
 4  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)
 5    RETURN NUMBER,
 6  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
 7                                       P1   IN VARCHAR2) RETURN NUMBER,
 8  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_STRINGS_LHR,
 9                                         RETURNVALUE OUT VARCHAR2,
10                                         FLAGS       IN NUMBER)
11    RETURN NUMBER,
12  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
13                                     SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)
14    RETURN NUMBER
15);
16/
17CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_STRINGS_LHR IS
18  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)
19    RETURN NUMBER IS
20  BEGIN
21    SCTX := WM_CONCAT_IMPL_STRINGS_LHR(NULL);
22    RETURN ODCICONST.SUCCESS;
23  END;
24  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
25                                       P1   IN VARCHAR2) RETURN NUMBER IS
26  BEGIN
27    IF (CURR_STR IS NOT NULL) THEN
28      CURR_STR := CURR_STR || ',' || P1;
29    ELSE
30      CURR_STR := P1;
31    END IF;
32    RETURN ODCICONST.SUCCESS;
33  END;
34  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_STRINGS_LHR,
35                                         RETURNVALUE OUT VARCHAR2,
36                                         FLAGS       IN NUMBER) RETURN NUMBER IS
37  BEGIN
38    RETURNVALUE := CURR_STR;
39    RETURN ODCICONST.SUCCESS;
40  END;
41  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
42                                     SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)
43    RETURN NUMBER IS
44  BEGIN
45    IF (SCTX2.CURR_STR IS NOT NULL) THEN
46      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
47    END IF;
48    RETURN ODCICONST.SUCCESS;
49  END;
50END;
51/
52CREATE OR REPLACE FUNCTION WM_CONCAT_STRINGS_LHR(P1 VARCHAR2) RETURN VARCHAR2
53  AGGREGATE USING WM_CONCAT_IMPL_STRINGS_LHR;
54/
55CREATE PUBLIC SYNONYM WM_CONCAT_STRINGS_LHR FOR WM_CONCAT_STRINGS_LHR;
56GRANT EXECUTE ON WM_CONCAT_STRINGS_LHR TO PUBLIC;


以上函数的测试示例如下所示，函数的返回值是以逗号为分隔符的字符串：
1SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
2   USER_ID
3----------
4         0
5         5
6SYS@lhrdb21> SELECT WM_CONCAT_STRINGS_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
7WM_CONCAT_STRINGS_LHR(D.USER_ID)
8---------------------------------------------------
90,5


其实，与WM_CONCAT相似的还有一个函数是LISTAGG。这是一个Oracle的列转行函数，使用示例如下所示：
 1WITH TEMP AS(
 2  SELECT 'China' NATION ,'Guangzhou' CITY FROM DUAL UNION ALL
 3  SELECT 'China' NATION ,'Shanghai' CITY FROM DUAL UNION ALL
 4  SELECT 'China' NATION ,'Beijing' CITY FROM DUAL UNION ALL
 5  SELECT 'USA' NATION ,'New York' CITY FROM DUAL UNION ALL
 6  SELECT 'USA' NATION ,'Bostom' CITY FROM DUAL UNION ALL
 7  SELECT 'USA' NATION ,'Bostom' CITY FROM DUAL UNION ALL
 8  SELECT 'Japan' NATION ,'Tokyo' CITY FROM DUAL
 9)
10SELECT NATION,LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY)
11FROM TEMP
12GROUP BY NATION;


输出结果如下所示：
 
对于LISTAGG函数，如果聚合的内容太多就会报“ORA-01489: result of string concatenation is too long”的错误，那么这个时候可以从业务的角度去修改SQL，也可以使用WM_CONCAT函数返回CLOB类型来解决这个问题。
& 说明：
有关字符串中是否含义汉字和多字节字符的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2124931/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。