ORA-00904: "WM_CONCAT": invalid identifier错误解决

0    831    3

Tags:

👉 本文共约1557个字,系统预计阅读时间或需6分钟。

现象

若在创建数据库的时候没有创建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视图,会有如下的信息:

image-20220218095313894

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

解决方案

解决办法分情况

若Oracle版本是10g或11g,那么可以使用Oracle自带的脚本(owminst.plb)来创建WM_CONCAT函数;

若Oracle版本大于等于12c,那么只能自己创建函数来解决这个问题。虽然,在Oracle 12c中,脚本owminst.plb依然存在,但是执行完成后,并不会创建WM_CONCAT函数,所以只能使用自建函数来解决这个问题。

Oracle 10g或11g

若Oracle版本是10g或11g,用Oracle自带脚本重建WMSYS用户的WMSYS.WM_CONCAT函数

运行如下脚本可以卸载WMSYS用户的数据:

若Oracle版本是10g或11g,运行如下脚本安装WMSYS用户即可创建WMSYS.WM_CONCAT函数:

解锁WMSYS用户:

Oracle 12c及以上

如果只是单个用户使用,那么不用刻意去创建WMSYS用户,可以在所需的用户下运行订制脚本,生成WM_CONCAT函数。另外,为了和系统的函数名区别开来,也可以修改函数名称。如果是多个用户使用,也可以运行自己定制的脚本,然后创建同义词,这样多个用户都可以使用。

下面按照返回值的不同分为几种情况来订制不同的脚本。

首先创建WMSYS用户,如下:

返回无分隔符,返回CLOB类型

创建函数的脚本如下所示,使用SYS用户运行:

以上函数的测试示例如下所示,函数的返回值是无分隔符的CLOB,在PL/SQL中要使用TO_CHAR进行转换:

返回逗号分隔符,返回CLOB,和11g一样的效果

创建函数的脚本如下所示,使用SYS用户运行:

以上函数的测试示例如下所示,函数的返回值是以逗号为分隔符的CLOB,在PL/SQL中需要使用TO_CHAR进行转换:

逗号分隔符,返回字符串类型,和10g一样的效果

创建函数的脚本如下所示,使用SYS用户运行:

以上函数的测试示例如下所示,函数的返回值是以逗号为分隔符的字符串:

另外,需要注意的是,在Oracle 12c中,若存在PDB,那么以上脚本也应该在所有PDB运行。

其实,与WM_CONCAT相似的还有一个函数是LISTAGG。这是一个Oracle的列转行函数,从Oracle 12c开始,推荐使用LISTAGG函数,使用示例如下所示:

输出结果如下所示:

ORA-00904: "WM_CONCAT": invalid identifier错误解决

对于LISTAGG函数,如果聚合的内容太多就会报“ORA-01489: result of string concatenation is too long”的错误,那么这个时候可以从业务的角度去修改SQL,也可以使用WM_CONCAT函数返回CLOB类型来解决这个问题。

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

11 + 20 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部