Methods for Oracle to implement String concatenation and separation functions (WM_CONCAT function), oraclewm_concat

Methods for Oracle to implement String concatenation and separation functions (WM_CONCAT function), oraclewm_concat

String Aggregation Techniques, or assemble the handler of each link in the process into a string.

There are multiple methods to implement this function in Oracle, which are listed here. For detailed usage, refer to the following article:

WM_CONCAT Function

Listparts Function

Custom Aggregate functions

0. test example

The first type is described here: WM_CONCAT, which is an internal Oracle function. It is not described in the official document (undefinented function) and WM_CONCAT function has been canceled in the version starting with Oracle12.2.

Use four table records and three view records from the all_objects view as test data:

SQL> CREATE TABLE T_STRAGG AS  2    select OBJECT_TYPE,CREATED,OBJECT_NAME from ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' AND rownum<5  3    UNION ALL  4    select OBJECT_TYPE,CREATED,OBJECT_NAME from ALL_OBJECTS WHERE OBJECT_TYPE='VIEW' AND rownum<4;Table createdSQL> select OBJECT_TYPE,TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS') CREATED,OBJECT_NAME from T_STRAGG;OBJECT_TYPE         CREATED             OBJECT_NAME------------------- ------------------- ------------------------------TABLE               2013-10-09 18:23:43 DUALTABLE               2013-10-09 18:23:44 SYSTEM_PRIVILEGE_MAPTABLE               2013-10-09 18:23:45 TABLE_PRIVILEGE_MAPTABLE               2013-10-09 18:23:47 STMT_AUDIT_OPTION_MAPVIEW                2013-10-09 18:23:53 ALL_XML_SCHEMASVIEW                2013-10-09 18:23:56 ALL_XML_SCHEMAS2VIEW                2013-10-09 18:23:54 V_$ADVISOR_CURRENT_SQLPLAN

The syntax for concatenating an OBJECT_NAME string by OBJECT_TYPE is as follows:

SQL> select object_type,WM_CONCAT(OBJECT_NAME) FROM T_STRAGG group by object_type;OBJECT_TYPE         WM_CONCAT(OBJECT_NAME)------------------- --------------------------------------------------------------------------------TABLE               DUAL,STMT_AUDIT_OPTION_MAP,TABLE_PRIVILEGE_MAP,SYSTEM_PRIVILEGE_MAPVIEW                ALL_XML_SCHEMAS,V_$ADVISOR_CURRENT_SQLPLAN,ALL_XML_SCHEMAS2

Here we find several features (or problems) of the WM_CONCAT function ):

1. return values can only be separated by commas

This cannot be changed. <喎? kf ware vc " target="_blank" class="keylink"> Signature + signature/u0rsa0vdOjrLbMwcu/Signature + signature + 3cG/tPPKsb/Signature + signature/Signature = "write the image description here" src = "/uploadfile/Collfiles/20180324/20180324090629241 .png" title = "\"/>

3. Unable to sort

For example, I want to sort by created to produce the following result:

DUAL was created on 18:23:43, and SYSTEM_PRIVILEGE_MAP was created on

18:23:44 .....

Although the WM_CONCAT function itself does not support sorting, there is still a work und to solve the Sorting Problem:

SQL> select object_type, TXT from 2 (3 select object_type 4, WM_CONCAT (OBJECT_NAME | 'CREATED in' | TO_CHAR (CREATED, 'yyyy-MM-DD HH24: MI: SS ') OVER (partition by OBJECT_TYPE order by created) as txt 5, ROW_NUMBER () OVER (partition by OBJECT_TYPE order by created desc) RN 6 FROM t_str1_7) where rn = 1; OBJECT_TYPE TXT partition Partition table dual created on 18:23:43, partition created on 18:23:44, TABLE_PRIVVIEW ALL_XML_SCHEMAS created on 18:23:53, V _ $ ADVISOR_CURRENT_SQLPLAN created on 18

Some articles on the Internet do not use row_number () to get the number, but use the MAX function to get the maximum value.

In fact oracle11g will report ORA-00932 error:

SQL> select object_type, MAX (TXT) from 2 (3 select object_type 4, WM_CONCAT (OBJECT_NAME | 'created on '| TO_CHAR (CREATED, 'yyyy-MM-DD HH24: MI: SS ') OVER (partition by OBJECT_TYPE order by created) as txt 5 FROM t_str1_6) group by OBJECT_TYPE; ORA-00932: inconsistent datatypes: expected-got CLOB

The reason is that the clob field does not support the max function. The article on the internet is based on the environment before oracle11g. At that time, the WM_CONCAT function returned the VARCHAR2 type.

4. partial support for DISTINCT

In the SQL environment, WM_CONCAT supports DISTINCTFor example:

SQL> insert into t_stragg select * from t_stragg where OBJECT_NAME='DUAL';1 row insertedSQL> select * from t_stragg where OBJECT_NAME='DUAL';OBJECT_TYPE         CREATED     OBJECT_NAME------------------- ----------- ------------------------------TABLE               2013/10/9 1 DUALTABLE               2013/10/9 1 DUALSQL> select object_type,WM_CONCAT(DISTINCT OBJECT_NAME) AS TXT  2    FROM T_STRAGG  3   GROUP BY OBJECT_TYPE;OBJECT_TYPE         TXT------------------- --------------------------------------------------------------------------------TABLE               DUAL,STMT_AUDIT_OPTION_MAP,SYSTEM_PRIVILEGE_MAP,TABLE_PRIVILEGE_MAPVIEW                ALL_XML_SCHEMAS,ALL_XML_SCHEMAS2,V_$ADVISOR_CURRENT_SQLPLAN

However, in PLSQL, WM_CONCAT uses distinct to report an error.

ORA-30482: DISTINCT option not allowed for this function

This is part of my support.

SQL> create or replace function F_WMCONCAT(V_OBJTYPE VARCHAR2) return clob is  2    FunctionResult clob;  3  begin  4    select WM_CONCAT(DISTINCT OBJECT_NAME) AS TXT  5    6     INTO FunctionResult  7     FROM T_STRAGG  8     WHERE OBJECT_TYPE=V_OBJTYPE;  9   10     return(FunctionResult); 11  end F_WMCONCAT; 12  /Warning: Function created with compilation errorsSQL> SHOW ERRErrors for FUNCTION DONGFENG.F_WMCONCAT:LINE/COL ERROR-------- ----------------------------------------------------------------4/10     PL/SQL: ORA-30482: DISTINCT option not allowed for this function4/3      PL/SQL: SQL Statement ignored

Of course, there are also solutions:

1. One solution is to do distinct first and then wm_concat;

2. solution 2: use dynamic SQL to avoid PLSQL compilation.

For example:

SQL> create or replace function F_WMCONCAT(V_OBJTYPE VARCHAR2) return clob is  2    FunctionResult clob;  3  begin  4  execute immediate  5   'select WM_CONCAT(DISTINCT OBJECT_NAME) AS TXT  6     FROM T_STRAGG  7     WHERE OBJECT_TYPE='''||V_OBJTYPE||''''  8     INTO FunctionResult;  9   10     return(FunctionResult); 11  end F_WMCONCAT; 12  /Function createdSQL> select F_WMCONCAT('TABLE') from DUAL;F_WMCONCAT('TABLE')--------------------------------------------------------------------------------DUAL,STMT_AUDIT_OPTION_MAP,SYSTEM_PRIVILEGE_MAP,TABLE_PRIVILEGE_MAP

It is recommended that the WM_CONCAT function is not recommended for Oracle, so use as few as possible.