Cause:

In 11gr2 and 12C, we have abandoned the wm_concat function. At that time, many of our programmers did use this function in the program, resulting in errors in the program. In order to reduce the workload of programmers modifying the program, we only create a wm_concat function by hand, and solve the problem in time. However, we should use to_char (wm_con) to create the function in time Cat () mode can completely replace the previous application.

terms of settlement:

sys login

1. Unlock sys user

alter user sys account unlock;
2. Create package, package body and function

Log in to the database as sys user and execute the following command

CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
(
CURR_STR VARCHAR2(32767), 
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(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 type body:

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(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 ODCIAGGREGATETERMINATE(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;

Custom function: Custom row variable column function:

CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)

RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;

3. Create synonyms and authorize

create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL
create public synonym wm_concat for sys.wm_concat
grant execute on WM_CONCAT_IMPL to public
grant execute on wm_concat to public