Create the wm_concat function on Oracle 12C

Create the wm_concat function on Oracle 12C

The wm_concat function has been abandoned on Oracle 11gR2 and 12C. At that time, many of our programmers used this function in the program, leading to program errors. To reduce the workload of programmers to modify programs, you can only manually create a wm_concat function to solve this problem temporarily. However, note that the function is created in time and the to_char (wm_concat () method is also required during use, in order to completely replace the previous applications.

1. Unlock the wmsys user
Alter user wmsys account unlock;

Ii. Create packages, packages, and functions
Log on to the database as a wmsys user and execute the following command

Create or replace type WM_CONCAT_IMPL AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2 (32767 ),
Static function odciaggresponinitialize (sctx in out WM_CONCAT_IMPL) return number,
Member function odciaggregateiterate (self in out WM_CONCAT_IMPL,
P1 IN VARCHAR2) return number,
Member function odciaggresponterminate (self in WM_CONCAT_IMPL,
Returnvalue out VARCHAR2,
Flags in number)
Return number,
Member function odciaggregatemerge (self in out WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/

-- Define the type body:
Create or replace type body WM_CONCAT_IMPL
IS
Static function odciaggresponinitialize (sctx in out WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX: = WM_CONCAT_IMPL (NULL );
Return odciconst. SUCCESS;
END;
Member function odciaggregateiterate (self in out 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 odciaggresponterminate (self in 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 WM_CONCAT_IMPL,
SCTX2 IN 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;
/
-- UDF for changing columns of a custom row:
Create or replace function wm_concat (P1 VARCHAR2)
RETURN VARCHAR2 aggregate using WM_CONCAT_IMPL;
/

3. Create and authorize Synonyms

Create public synonym WM_CONCAT_IMPL for wmsys. WM_CONCAT_IMPL
/
Create public synonym wm_concat for wmsys. wm_concat
/

Grant execute on WM_CONCAT_IMPL to public
/
Grant execute on wm_concat to public
/