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;

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

Oracle Forms: Avoid duplicated records in a block

 The purpose is to reject two records that contain duplicated values

The technique used to solve this problem comes from the Kevin D Clarke’s calculated item famous solution.

 

It uses two calculated items, one in the data bock and another in a control block.

The first calculated item (:DEPT.MATCH_FOUND) is added to the DEPT block. It contains the formula as follow:

 

Comparaison(:ctrl.charsave, :dept.deptno||:dept.dname)

 

Notice in this case,that we want to avoid duplicates on both DEPTNO and DNAME values.

 

Function COMPARAISON (val1 varchar2, val2 varchar2)

Return number

Is

   answer number := 0;

Begin

   if val1 = val2 then

      answer := 1;

   end if;

   return(answer);

End;

 

COMPARAISON is a program unit stored in the Forms module.

 

The two values are compared to each other, then the function returns 1 (a value greatest than 0) if both the values are identical.

The first value (:ctrl.charsave) contains the bakup value of the current record.

 

The DEPT block must have the following properties setting:

 

Query all records

YES

 

 

The CTRL block must have the following properties setting:

 

Query all records	YES
Single record	YES
Database data block	NO

 

 

The second calculated item (:CTRL.MATCH_FOUND) is added to the CTRL block.

It summarize the values contained in all the rows of the DEPT block (dept.match_found).

If the total is greater than 1, we have two duplicated data.

Error When Insert Graph In Oracle Report 10g

 

Normally this type of error raised when graph is using in Oracle 10g Reports which is configured on Windows 10. In order to avoid above error, please follow below steps:

1. Go to the properties of reports builder.
2. Switch tab to “Compatibility”.
3. Check on “Run this program in compatibility mode for”.
4. Select “Windows 98/Windows Me” from list.
5. Press apply and save.

It will surely work.

Courtesy By: Muhammed Azfar Nawaz