获取两个表的公共字段,使用了wm_concat() 函数,但是打开行的时候报错,因为是clob的类型。
解决方法:
1、 使用to_char函数,转成字符类型:
select to_char(wm_concat(COLUMN_NAME)) from all_tab_columns a where a.TABLE_NAME=‘表名1’ and a.OWNER=‘本地用户1’
AND exists(select 1 from all_tab_columns@dj b where b.TABLE_NAME=‘表名2’ and b.OWNER=‘本地用户2’
AND A.COLUMN_NAME=B.COLUMN_NAME) GROUP BY A.TABLE_NAME;
2、使用LISTAGG分析函数(11g Release 2),允许我们使用指定连接字符串:
逗号分隔:
select listagg(COLUMN_NAME, ‘,’) within group (order by COLUMN_NAME) as 列名 from all_tab_columns a where a.TABLE_NAME=‘表名1’ and a.OWNER=‘本地用户1’
AND exists(select 1 from all_tab_columns@dj b where b.TABLE_NAME=‘表名2’ and b.OWNER=‘本地用户2’
AND A.COLUMN_NAME=B.COLUMN_NAME) GROUP BY A.TABLE_NAME;
横杠分隔:
select listagg(COLUMN_NAME, ‘-’) within group (order by COLUMN_NAME) as 列名 from all_tab_columns a where a.TABLE_NAME=‘表名1’ and a.OWNER=‘本地用户1’
AND exists(select 1 from all_tab_columns@dj b where b.TABLE_NAME=‘表名2’ and b.OWNER=‘本地用户2’
AND A.COLUMN_NAME=B.COLUMN_NAME) GROUP BY A.TABLE_NAME;
需要注意的是 wm_concat()函数有些版本不支持。