【数据库升级故障】函数wm_concat引发的ORA-01790错误

这周在梳理老旧系统，其中有十年+的系统需要迁移到新的服务器，鉴于各方面考虑，顺便将库从10.2.0.1版本迁移到11.2.0.4，一切都准备好后，在应用启动后，部分菜单出现报错：
ORA-01790: expression must have same datatype ascorresponding expression
怎么办呢？？

1. 问题排查

排查操作涉及的SQL简化如下：

select no,wm_concat(name) as name from t1 where no<10 group by no -- 子SQL1
UNION 
select no,name from t1 where no>10  -- 子SQL2

2个子SQL单独执行是不会报错的，但是一旦用了union 联合后，就会触发 ORA-01790 报错。
于是在进一步排查，是函数wm_concat导致的

2. 究其根源，发现是wm_concat函数惹的祸

是由于升级到11.2.0.4后，wm_concat函数返回的数据类型发生了变化，在这之前返回的是VARCHAR2字符串类型，而升级之后返回的是CLOB类型。

3. 解决措施：

1）【临时解决办法】若将函数包多一层转义，可以解决，子sql1于是改写成如下：

select no,to_char(wm_concat(name)) as name from t1 where no<10 group by no -- 子SQL1

PS：但是这需要修改代码，而且涉及的范围可能很广，不单单是此时发现的这一处，改起来费时费力，有没有更好好的办法呢？于是想到在函数 wm_concat 下功夫

2）【绝招】第一步：重新定义wm_concat函数，改变其返回的数据类型

-- 连接应用账号，查询需要创建的对象是否存在

select * from dba_objects where object_name in ('F_WM_CONCAT_IMPL','F_WM_CONCAT_IMPL','F_WM_CONCAT')

-- 创建自定义函数涉及的类
CREATE OR REPLACE TYPE F_WM_CONCAT_IMPL AS OBJECT  
-- AUTHID CURRENT_USER AS OBJECT  
(  
CURR_STR VARCHAR2(32767),   
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT F_WM_CONCAT_IMPL) RETURN NUMBER,  
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT F_WM_CONCAT_IMPL,  
P1 IN VARCHAR2) RETURN NUMBER,  
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN F_WM_CONCAT_IMPL,  
RETURNVALUE OUT VARCHAR2,  
FLAGS IN NUMBER)  
RETURN NUMBER,  
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT F_WM_CONCAT_IMPL,  
SCTX2 IN F_WM_CONCAT_IMPL) RETURN NUMBER  
);  
/  

--定义类型body:  
CREATE OR REPLACE TYPE BODY F_WM_CONCAT_IMPL  
IS  
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT F_WM_CONCAT_IMPL)  
RETURN NUMBER  
IS  
BEGIN  
SCTX := F_WM_CONCAT_IMPL(NULL) ;  
RETURN ODCICONST.SUCCESS;  
END;  
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT F_WM_CONCAT_IMPL,  
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 F_WM_CONCAT_IMPL,  
RETURNVALUE OUT VARCHAR2,  
FLAGS IN NUMBER)  
RETURN NUMBER  
IS  
BEGIN  
RETURNVALUE := CURR_STR ;  
RETURN ODCICONST.SUCCESS;  
END;  
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT F_WM_CONCAT_IMPL,  
SCTX2 IN F_WM_CONCAT_IMPL)  
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 F_WM_CONCAT(P1 VARCHAR2)  
RETURN VARCHAR2 AGGREGATE USING F_WM_CONCAT_IMPL ;  
/  

3)【绝招】最后一步：为了程序不做任何改动，我们直接干翻 WMSYS.WM_CONCAT函数的江湖地位

--查询同义词对象
select * from dba_objects where object_name in( 'WM_CONCAT','F_WM_CONCAT_IMPL','F_WM_CONCAT')

--删除公共同义词 (属主WMSYS)--使用sys用户（或授予相关权限）操作
DROP PUBLIC SYNONYM WM_CONCAT;

--创建公共同义词（连接自定义函数） -- 使用sys用户（或授予相关权限）操作
create public synonym WM_CONCAT for <username>.F_WM_CONCAT;

--查询同义词 属于<username>用户
select * from dba_synonyms where synonym_name='WM_CONCAT'
--测试
 select wm_concat('Hello') from dual
 
--(针对查询库用户的查询用户)授权执行存储过程 
--grant execute on WM_CONCAT to <username>;

至此，再次打开应用程序，不再报错。