Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to create wm_concat functions in Oracle12C database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

When our program needs to connect to the 12C database, there will be an error where the wm_concat function is used. This is because 12C has abandoned the wm_concat function, and there are ways to use the listagg function instead of the wm_concat function, but all the places in the program that involve the wm_concat function have to be written as the listagg function, so in order to reduce the workload of modifying the program You can solve this problem by creating wm_concat functions by hand.

Log in to the database as a sys user as SYSDBA Execute the following command CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT-- AUTHID CURRENT_USER 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 the type bodyCREATE OR REPLACE TYPE BODY WM_CONCAT_IMPLISSTATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBERISBEGINSCTX: = WM_CONCAT_IMPL (NULL); RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT WM_CONCAT_IMPL,P1 IN VARCHAR2) RETURN NUMBERISBEGINIF (CURR_STR IS NOT NULL) THENCURR_STR: = CURR_STR | |','| P1 alternative ELSECURRRER STR: = P1udend IF;RETURN ODCICONST.SUCCESS;END MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN WM_CONCAT_IMPL,RETURNVALUE OUT VARCHAR2,FLAGS IN NUMBER) RETURN NUMBERISBEGINRETURNVALUE: = CURR_STR; RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT WM_CONCAT_IMPL,SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBERISBEGINIF (SCTX2.CURR_STR IS NOT NULL) THENSELF.CURR_STR: = SELF.CURR_STR | |','| | SCTX2.CURR_STR; END IF;RETURN ODCICONST.SUCCESS;END;END /-- Custom function list CREATE OR REPLACE FUNCTION wm_concat (P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL; / 2. 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/ "how to create wm_concat functions in Oracle12C database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.