ORA-00904: "wm_concat":invalid identifier错误如何解决？
ORA-00904: "wm_concat":invalid identifier错误如何解决？
AiDBA宝典
发布于 2023-05-23 01:18:22
发布于 2023-05-23 01:18:22
代码可运行
运行总次数：0
代码可运行
题目部分
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视图，会有如下的信息：
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
5
SYS@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
5
SYS@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
5
SYS@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类型来解决这个问题。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2023-05-16，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
推荐阅读
相关推荐
Oracle｜19C升级WM_CONCAT函数失效
更多 >
腾讯云开发者
