WMSYS.WM_CONCAT函数将不再出现在12c中。该函数是一个在workspace manager组件(wmsys用户)中包含的Oracle内部函数,随着数据库版本的升级、优化性能等原因,可能会造成该函数的使用问题,故而不建议用户作为数据库函数来使用。从11.2开始,Oracle推荐使用LISTAGG函数作为替代。

真的可以很好的替代吗?我们先来看一个例子吧。

Step 1:创建一个简单的测试表ALEX,并做一下数据初始化。

 concat1

Step 2:做一个查询测试,100行记录长度为100的字符串连接,即输出一个10,099长度的字符串。

concat2

我们可以清晰的看到使用LISTAGG函数出现了长度溢出的错误,因为LISTAGG函数的输出结果是最大长度为4000的VARCHAR2类型的字符串,而在WMSYS.WM_CONCAT函数输出的则是CLOB字段,其长度限制远大于VARCHAR2。

如果简单的使用LISTAGG函数作为替代,是会出现明确问题的,除非对应用程序SQL进行大动作的改造,这显然不是我们希望做的事情。

然而,WMSYS.WM_CONCAT函数是没有长度限制的吗?我们再来做进一步测试看看吧。

Step 3:再做一个查询测试,100行记录长度为1000的字符串连接,即输出一个100,099长度的字符串。

concat3

结果是出乎意料之外的,ORA-01489的错误出现了。究其根本原因是因为在WMSYS.WM_CONCAT函数内部仍使用了VARCHAR2类型字段作为中间变量,导致最终结果的输出长度受限。当然,这样的做法是可以保证该函数的运行效率的,但不知道Oracle是不是因此而不推荐使用该函数呢?

面对这样的坑有没有填法呢?答案是肯定的。我们自己来构造一个函数SA_CONCAT来完全模拟并加强其特性(突破最终输出结果的长度限制)。

Step 4:基于SA_CONCAT自建函数,再做一个查询测试,100行记录长度为1000的字符串连接,即输出一个100,099长度的字符串。成功输出!

concat4

下面我们再来看一下SA_CONCAT函数是如何构建的吧。如下所示,在主体程序过程中,中间变量均为CLOB类型,这样的做法虽然规避了长度溢出的风险,但是需要考虑到性能的影响,也请谨慎使用。

concat5

concat6

,
Trackback

no comment untill now

Add your comment now

切换到手机版