Oracle SQL-PL/SQL: Create WM_CONCAT in ORACLE 12c

 WM_CONCAT  is not working in ORACLE 12c and Oracle suggested an alternative solution is to use LISTAGG.

LISTAGG working fine in Database and Oracle Reports level,

But Oracle Forms 11g (Version 11.1.2.0.0) have compilation error when using LISTAGG.

The Easiest solution in this issue …..

Create WM_CONCAT function in ORACLE 12c database:

You can try the following steps to create WM_CONCAT  in ORACLE 12c ……

Step 1: Create a TYPE and TYPE BODY

—————————Script ——————–

DROP TYPE WMSYS.WM_CONCAT_IMPL;

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=
/

DROP TYPE BODY WMSYS.WM_CONCAT_IMPL;

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=
/

—————————————————————–

Step 2: Create the function wm_concat

————————Script———————————-

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==
/
———————————————–

Step 3: GRANT it to PUBLIC
————————Script———————————-
GRANT EXECUTE ON WMSYS.wm_concat TO PUBLIC;

———————————————–