原因
当前oracle库中没有WM_CONCAT函数导致。
因开发环境跟目前环境的oracle版本不一致导致的。
解决办法
法1:直接迁移此函数
执行脚本
create or replace function wmsys.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==
create or replace type body wmsys.Wm_Concat_Impl wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
e
431 1d6
4E5KBnNGi9ZdccetOt5Qas902MUwgzsJr0gVfHRVWGSl8CvsyI9XoIRaVeA+MgrbNy0MKs17
YaSfXYacoF8yX8sh94R2lTMSM+u3LvBLTcKy2MTwPxIQP5IREfj5ZWZ44bhRRiFdQQ1plWkB
7MPMVFaJyn+9PoTQdVdiKGeou0Q8AYzVPPKrxfa3OY5tBmId+MA3bxWAbe3drFfb3dOYu7nQ
e7Yz1Bp8IAwzL/ckiVdYV2Qqex8DhTHPjuD0YvndS6zfRttr+q6uIwXTMrBB1OyNwiMmilYC
macOerMZg9T0sj6P8g66Z6eFS8Yiw0Z5KLtVy9TEhgT83sRiqwdJe8ve/+FVjUa9RYnbEN5A
AYhXVXTVAXphRT8pnzX2SMPuZt6w9oOa3mn8Ig8PhdU+CVFi+SGebpoKgoGKxG6cIyZ5T26/
w3bHJXj6ctAgIKT7+cahCLw6NnMqtSQ=
create or replace type wmsys.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=
法2
使用 listagg(合并字段,‘连接符号’) within group (order by 字段) 来实现列转行(需要oracle是11或以上)
举个例子:
WM_CONCAT()函数,默认用,隔开
select t.table_key, wmsys.wm_concat(t.name) AS result
from TEST_TABLE t
where t.id = #{id}
group by t.table_key
order by t.create_time
替换后 listagg()函数
select t.table_key,
listagg(t.name, ',') within group(order by t.create_time) AS result
from TEST_TABLE t
where t.id = #{id}
group by t.table_key