The listparts function (Oracle) implements String concatenation and separation, and implements lelistagg

The listparts function (Oracle) implements String concatenation and separation, and implements lelistagg

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

This article introduces the second type: listparts function, which is a string aggregate function officially launched by Oracle11gR2 and has very powerful functions.

0. test sample and basic usage

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

Now, you want to group OBJECT_TYPE and assemble OBJECT_NAME into a string similar to logistics information in chronological order:

2013-10-09 18:23:43 @ DUAL
2013-10-09 18:23:44 @ SYSTEM_PRIVILEGE_MAP
2013-10-09 18:23:45 @ TABLE_PRIVILEGE_MAP

The implementation method is simple:

SQL> select object_type  2        ,LISTAGG(TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS')||'@'||OBJECT_NAME,CHR(10)) WITHIN GROUP(ORDER BY CREATED) AS TXT  3    FROM T_STRAGG  4    GROUP BY object_type;OBJECT_TYPE         TXT------------------- --------------------------------------------------------------------------------TABLE               2013-10-09 18:23:43@DUAL                    2013-10-09 18:23:44@SYSTEM_PRIVILEGE_MAP                    2013-10-09 18:23:45@TABLE_PRIVILEGE_MAP                    2013-10-09 18:23:47@STMT_AUDIT_OPTION_MAPVIEW                2013-10-09 18:23:53@ALL_XML_SCHEMAS                    2013-10-09 18:23:54@V_$ADVISOR_CURRENT_SQLPLAN                    2013-10-09 18:23:56@ALL_XML_SCHEMAS2

Compared with WM_CONCAT, listparts has the following features (advantages and disadvantages ):

1. the delimiters of the returned values can be customized.

This greatly facilitates developers to pose various positions as long as necessary.

2. the return value type is VARCHAR2.

In contrast to WM_CONCAT, the speed is faster, but an error is reported if the data volume is large.

ORA-01489: result of string concatenation is too long

(The result of string connection is too long)

SQL> INSERT INTO T_STRAGG select object_type,CREATED,OBJECT_NAME FROM ALL_OBJECTS where rownum<1000;999 rows insertedSQL> select object_type  2        ,LISTAGG(TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS')||'@'||OBJECT_NAME,CHR(10)) WITHIN GROUP(ORDER BY CREATED) AS TXT  3    FROM T_STRAGG  4    GROUP BY object_type;ORA-01489: result of string concatenation is too long

No way, use WM_CONCAT?

We recommend that you use the third method: Custom aggregate function.

3. DISTINCT is not supported.

Direct error ORA-30482: DISTINCT option not allowed for this function

(The DISTINCT option is disabled in this function)

SQL> select object_type,LISTAGG(DISTINCT OBJECT_NAME) WITHIN GROUP(ORDER BY CREATED) AS TXT  2    FROM T_STRAGG  3    GROUP BY object_type;ORA-30482: DISTINCT option not allowed for this function

Solution:

1. First distinct, and then listparts

2. Use a custom aggregate function to replace the listparts function.

Conclusion

When listparts can meet the requirements, listparts should be used. If wm_concat listparts cannot meet the requirements, it is better to use a custom aggregate function on the Internet.