aws rds 上oracle 因为wmsys.wm_concat 函数失效而导致包编译错误

问题描述:

有项目的 PGPA 包编译报错,因为包里调用了 wm_concat( ) 函数,而 wm_concat( ) 函数失效了,导致 PGPA包编译不过去。

 

关于 wm_concat() 函数:

 

wmsys.wm_concat( )   经常用来做行列转换,可以把列放在一行显示。在调用的时候写不写 wmsys.  都可以,因为wm_concat 有public 的同义词。效果如下:

image

一般系统中都有 wmsys 这个用户,但是都不是open的。

image

 

wm_concat 这个函数来自于WorkSpaceManager组件,如果没有安装这个组件,就没有这个函数,所以相同版本的oracle 也可能有的有有的没有这个函数。

 

本次升级后的报错:

升级后,包里调用了wm_concat()函数,所以编译包的时候报错因为 wm_concat 是无效表示符。在pl/sql里调用wm_concat 函数也报错,不管是否加wmsys. 用户名做前缀。

image

 

 

解决办法:

重建这个函数,有三个方法,本次使用了第三个方法,如下:

方法1、参考 http://www.askmaclean.com/archives/wmsys-wm_concat.html

此法要求有wmsys 用户,否则第二步报错。

CREATE OR REPLACE
type       WM_CONCAT_IMPL wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
d
270 160
bg9hz+fBwa888VEZGViHFs/oOP0wg433f64df3QCWE7VehmhTFcUe3y+JrsniV3cSuvmnE3g
Y93dtR+cCsU1N+UQDGbtzhCf2HIdr8lPzfgF2bmCTvmGlHQbTAjTftNrDq3p093ncwb32OyX
3ZFDTeH2jpjm3uWYyT8kZBfJIYxRwgLfRApoW32cpy0eRnvDBt2XfTAMXKCSNnqSoTiGA83W
6deKW+rWyBu9L/EPyFkmQZeBncNsiNDF8fa1Sm6vdQiEanlCQnaPJ11a0na8hK6psDSaey+x
fdMupCwSvg6gMrSV4QCguhOCqW2AmxRVMqpXJootPpTBxBFZc7hORGbriUI=
/

CREATE OR REPLACE function wm_concat wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
58 96
+CuW1MAfZPVR6NOnXKVmXY2o9pswg8eZgcfLCNL+XhZy8K7/cgzcVrPnfMPnx3TAM7h0ZSXD
j57Asr2ym9ZtFldFmFu+RdpAU8VGORKSvtVG+DmAOR4C+NTa+Pit2kDbEpI5zhZUgqameSkE
DQ==
/

 

 

方法2、参考 http://blog.csdn.net/totoov5/article/details/8154770

此法要求能访问到$ORACLE_HOME下的脚本。

1.SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmctab.plb
2.SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmaggrs.plb  
3.SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmaggrb.plb  

 

方法3、自建一个行列转置函数,为了让包编译通过,就也叫wm_concat

参考:https://oracle-base.com/articles/misc/string-aggregation-techniques#wm_concat

先删掉原来的wm_concat

SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE ‘WM_CONCAT%’; –查到后drop掉

drop function yewu.WM_CONCAT;

drop type yewu.WM_CONCAT_IMPL;

 

然后用系统管理员来创建这个函数,不要创建在业务用户上,因为可能由于升级破坏这个函数。

本例是用了aws 的rds ,没有sys,system用户,管理员用户假设为admin,就用admin用户如下操作:

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
										value  IN      VARCHAR2 )   RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)   RETURN NUMBER
);
/


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg, value  IN   VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                    ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/


CREATE OR REPLACE FUNCTION WM_CONCAT (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/

 

重建wm_concat 函数后,需要赋权和创建同义词

grant excute on admin.WM_CONCAT to public;

create or replace public synonym WM_CONCAT for admin.WM_CONCAT;

验证:如下图,函数可以正常使用

image

 

但应用程序还是报错,如下:

image

 

这是因为写 wmsys.wm_concat 时,因为aws rds 上没有wmsys 这个用户,在代码里去掉 wmsys. 前缀就好了,需要再发布一下程序。

关于aws rds 上的oracle,只开放了1521端口,sys,system,wmsys等用户都不开放。也不能访问$ORACLE_HOME下的脚本。而且开放给你的管理员连kill session 权限都没有了,限制很多。

Be the first to comment

Leave a Reply

Your email address will not be published.


*