【DB笔试面试459】ORA-00904:
16lz
2021-04-18
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类型来解决这个问题。
©著作权归作者所有：来自51CTO博客作者小麦苗DB宝的原创作品，如需转载，请注明出处，否则将追究法律责任更多相关文章
- 【函数】wm_concat包的订制
- ubuntu下安装交叉编译的环境脚本
- Take Command 脚本语言基础
- C语言基础知识点有哪些（最全大总结）
- 用两张图，详解FreeModbus在单片机串口上的数据收发过程-FreeModbu
- makefile使用.lds链接脚本以及 常用命令 解析
- Oracle行列互换总结
- Oracle结果集缓存（Result Cache）--服务器、客户端、函数缓存
- MySQL分库备份与分表备份