Oracle12 Listagg and Wm_concat ranks conversion

11GR2 and 12C have abandoned the Wm_concat function, when many of our programmers used the function in the program, resulting in a program error, in order to alleviate the programmer to modify the work of the program, only by manually creating a Wm_concat function, to temporarily solve the problem, but note that Even if the function is created, it is necessary to use To_char (Wm_concat ()) in order to completely replace the previous application.
I. Unlocking Wmsys users

Alter user Wmsys account unlock;

Two. Create package, package body, and function
To log in to the database as a Wmsys user, execute the following command

CREATE OR REPLACETYPE Wm_concat_impl asOBJECT--AUTHID Current_User as OBJECT(Curr_strVARCHAR2(32767), STATICFUNCTIONOdciaggregateinitialize (sctxinchOut Wm_concat_impl)RETURN  Number, MEMBERFUNCTIONOdciaggregateiterate (selfinchOut Wm_concat_impl, P1inch VARCHAR2)RETURN  Number, MEMBERFUNCTIONOdciaggregateterminate (selfinchWm_concat_impl, returnvalue outVARCHAR2, FLAGSinch  Number)  RETURN  Number, MEMBERFUNCTIONOdciaggregatemerge (selfinchOut Wm_concat_impl, SCTX2inchWm_concat_impl)RETURN  Number  ); /    --definition type body:CREATE OR REPLACETYPE BODY Wm_concat_impl isSTATICFUNCTIONOdciaggregateinitialize (sctxinchOut Wm_concat_impl)RETURN  Number   is  BEGINSctx:=Wm_concat_impl (NULL) ; RETURNOdciconst.  SUCCESS; END; MEMBERFUNCTIONOdciaggregateiterate (selfinchOut Wm_concat_impl, P1inch VARCHAR2)  RETURN  Number   is  BEGIN  IF(Curr_str is  not NULL) ThenCurr_str:=Curr_str|| ',' ||P1; ELSECurr_str:=P1; END IF; RETURNOdciconst.  SUCCESS; END; MEMBERFUNCTIONOdciaggregateterminate (selfinchWm_concat_impl, returnvalue outVARCHAR2, FLAGSinch  Number)  RETURN  Number   is  BEGINreturnvalue:=Curr_str; RETURNOdciconst.  SUCCESS; END; MEMBERFUNCTIONOdciaggregatemerge (selfinchOut Wm_concat_impl, SCTX2inchWm_concat_impl)RETURN  Number   is  BEGIN  IF(SCTX2. Curr_str is  not NULL) ThenSelf . CURR_STR:=Self. Curr_str|| ',' ||SCTX2.  CURR_STR; END IF; RETURNOdciconst.  SUCCESS; END; END; /  --Custom Row-variable-column functions:CREATE OR REPLACE FUNCTIONWm_concat (P1VARCHAR2)  RETURN VARCHAR2AGGREGATE USING Wm_concat_impl; /

Three. Create synonyms and authorize

Create  Public  for Wmsys. Wm_concat_impl; Create  Public  for Wmsys.wm_concat; Grant Execute  on  to  Public ; Grant Execute  on  to public;

or use the Listagg function

The following are the analogy methods

SelectDeptno, Listagg (ename,',') withinGroup(Order  byename) fromscott.empGroup  bydeptno deptno PATH---------- -------------------------------------------------------------------------------- TenClark,king,miller -Adams,ford,jones,scott,smith -Allen,blake,james,martin,turner,wardSelectDeptno,wm_concat (ename) asPath fromscott.empGroup  bydeptno deptno PATH---------- -------------------------------------------------------------------------------- Tenclark,miller,king -Smith,ford,adams,scott,jones -Allen,james,turner,blake,martin,ward

Oracle12 Listagg and Wm_concat ranks conversion