[20221227]19c LISTAGG Enhancements.txt

[20221227]19c LISTAGG Enhancements.txt

--//學習瞭解19c以上版本LISTAGG的新特性.
--//http://blog.dbi-services.com/oracle-12cr2-sql-new-feature-listagg-overflow/
After the delimiter string you can add:

ON OVERFLOW ERROR which is the default. Same behavior as in previous releases.
ON OVERFLOW TRUNCATE with a string that is added in case of truncation (default: '…') and optionally WITH COUNT (or
WITHOUT COUNT which is the default)

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> create table tx as select object_id,object_name,object_type from all_objects where rownum<=3e3;
Table created.

2.測試:
SCOTT@test01p> select listagg(object_name, ',' on overflow truncate) within group(order by object_id) c200 from tx;
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C_OBJ#,I_OBJ#,TAB$,CLU$,C_TS#,I_TS#,C_FILE#_BLOCK#,I_FILE#_BLOCK#,C_USER#,I_USER#,FET$,UET$,SEG$,UNDO$,TS$,FILE$,OBJ$,IND$,ICOL$,COL$,USER$,PROXY_DATA$,I_PROXY_DATA$,PROXY_ROLE_DATA$,I_PROXY_ROLE_DATA
$_1,I_PROXY_ROLE_DATA$_2,CON$,C_COBJ#,I_COBJ#,CDEF$,CCOL$,I_TAB1,I_UNDO1,I_UNDO2,I_OBJ1,I_OBJ2,I_OBJ3,I_OBJ4,I_OBJ5,I_IND1,I_ICOL1,I_FILE1,I_FILE2,I_TS1,I_USER1,I_USER2,I_COL1,I_COL2,I_COL3,I_CON1,I_C
ON2,I_CDEF1,I_CDEF2,I_CDEF3,I_CDEF4,I_CCOL1,I_CCOL2,BOOTSTRAP$,OBJERROR$,OBJAUTH$,I_OBJAUTH1,I_OBJAUTH2,C_OBJ#_INTCOL#,I_OBJ#_INTCOL#,HISTGRM$,I_H_OBJ#_COL#,HIST_HEAD$,I_HH_OBJ#_COL#,I_HH_OBJ#_INTCOL#
,FIXED_OBJ$,I_FIXED_OBJ$_OBJ#,TAB_STATS$,I_TAB_STATS$_OBJ#,IND_STATS$,I_IND_STATS$_OBJ#,OBJECT_USAGE,I_STATS_OBJ#,PARTOBJ$,I_PARTOBJ$,DEFERRED_STG$,I_DEFERRED_STG1,DEPENDENCY$,ACCESS$,I_DEPENDENCY1,I_
...
CS,V$SEGMENT_STATISTICS,V_$SEGSTAT_NAME,V$SEGSTAT_NAME,V_$SEGSTAT,V$SEGSTAT,V_$LIBRARY_CACHE_MEMORY,V$LIBRARY_CACHE_MEMORY,V_$JAVA_LIBRARY_CACHE_MEMORY,V$JAVA_LIBRARY_CACHE_MEMORY,V_$SHARED_POOL_ADVIC
E,V$SHARED_POOL_ADVICE,V_$JAVA_POOL_ADVICE,V$JAVA_POOL_ADVICE,V_$STREAMS_POOL_ADVICE,V$STREAMS_POOL_ADVICE,V_$GOLDENGATE_CAPABILITIES,V$GOLDENGATE_CAPABILITIES,V_$SGA_CURRENT_RESIZE_OPS,V$SGA_CURRENT_
RESIZE_OPS,V_$SGA_RESIZE_OPS,V$SGA_RESIZE_OPS,V_$SGA_DYNAMIC_COMPONENTS,V$SGA_DYNAMIC_COMPONENTS,V_$SGA_DYNAMIC_FREE_MEMORY,...(985)
--//被截斷了,顯示還有985個記錄沒有顯示.

SCOTT@test01p> select listagg(object_name, ',' on overflow truncate 'string is too long') within group(order by object_id) c200 from tx;
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C_OBJ#,I_OBJ#,TAB$,CLU$,C_TS#,I_TS#,C_FILE#_BLOCK#,I_FILE#_BLOCK#,C_USER#,I_USER#,FET$,UET$,SEG$,UNDO$,TS$,FILE$,OBJ$,IND$,ICOL$,COL$,USER$,PROXY_DATA$,I_PROXY_DATA$,PROXY_ROLE_DATA$,I_PROXY_ROLE_DATA
$_1,I_PROXY_ROLE_DATA$_2,CON$,C_COBJ#,I_COBJ#,CDEF$,CCOL$,I_TAB1,I_UNDO1,I_UNDO2,I_OBJ1,I_OBJ2,I_OBJ3,I_OBJ4,I_OBJ5,I_IND1,I_ICOL1,I_FILE1,I_FILE2,I_TS1,I_USER1,I_USER2,I_COL1,I_COL2,I_COL3,I_CON1,I_C
ON2,I_CDEF1,I_CDEF2,I_CDEF3,I_CDEF4,I_CCOL1,I_CCOL2,BOOTSTRAP$,OBJERROR$,OBJAUTH$,I_OBJAUTH1,I_OBJAUTH2,C_OBJ#_INTCOL#,I_OBJ#_INTCOL#,HISTGRM$,I_H_OBJ#_COL#,HIST_HEAD$,I_HH_OBJ#_COL#,I_HH_OBJ#_INTCOL#
,FIXED_OBJ$,I_FIXED_OBJ$_OBJ#,TAB_STATS$,I_TAB_STATS$_OBJ#,IND_STATS$,I_IND_STATS$_OBJ#,OBJECT_USAGE,I_STATS_OBJ#,PARTOBJ$,I_PARTOBJ$,DEFERRED_STG$,I_DEFERRED_STG1,DEPENDENCY$,ACCESS$,I_DEPENDENCY1,I_
...
EMORY_TARGET_ADVICE,GV_$MEMORY_RESIZE_OPS,GV$MEMORY_RESIZE_OPS,GV_$MEMORY_CURRENT_RESIZE_OPS,GV$MEMORY_CURRENT_RESIZE_OPS,GV_$MEMORY_DYNAMIC_COMPONENTS,GV$MEMORY_DYNAMIC_COMPONENTS,V_$SEGMENT_STATISTI
CS,V$SEGMENT_STATISTICS,V_$SEGSTAT_NAME,V$SEGSTAT_NAME,V_$SEGSTAT,V$SEGSTAT,V_$LIBRARY_CACHE_MEMORY,V$LIBRARY_CACHE_MEMORY,V_$JAVA_LIBRARY_CACHE_MEMORY,V$JAVA_LIBRARY_CACHE_MEMORY,V_$SHARED_POOL_ADVIC
E,V$SHARED_POOL_ADVICE,V_$JAVA_POOL_ADVICE,V$JAVA_POOL_ADVICE,V_$STREAMS_POOL_ADVICE,V$STREAMS_POOL_ADVICE,V_$GOLDENGATE_CAPABILITIES,V$GOLDENGATE_CAPABILITIES,V_$SGA_CURRENT_RESIZE_OPS,V$SGA_CURRENT_
RESIZE_OPS,V_$SGA_RESIZE_OPS,V$SGA_RESIZE_OPS,V_$SGA_DYNAMIC_COMPONENTS,V$SGA_DYNAMIC_COMPONENTS,string is too long(986)
--//結尾加入string is too long代替...,並且顯示計數,怎麼比前面多1個呢.

SCOTT@test01p> select listagg(object_name, ',' on overflow truncate without count) within group(order by object_id) c200 from tx;
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C_OBJ#,I_OBJ#,TAB$,CLU$,C_TS#,I_TS#,C_FILE#_BLOCK#,I_FILE#_BLOCK#,C_USER#,I_USER#,FET$,UET$,SEG$,UNDO$,TS$,FILE$,OBJ$,IND$,ICOL$,COL$,USER$,PROXY_DATA$,I_PROXY_DATA$,PROXY_ROLE_DATA$,I_PROXY_ROLE_DATA
$_1,I_PROXY_ROLE_DATA$_2,CON$,C_COBJ#,I_COBJ#,CDEF$,CCOL$,I_TAB1,I_UNDO1,I_UNDO2,I_OBJ1,I_OBJ2,I_OBJ3,I_OBJ4,I_OBJ5,I_IND1,I_ICOL1,I_FILE1,I_FILE2,I_TS1,I_USER1,I_USER2,I_COL1,I_COL2,I_COL3,I_CON1,I_C
ON2,I_CDEF1,I_CDEF2,I_CDEF3,I_CDEF4,I_CCOL1,I_CCOL2,BOOTSTRAP$,OBJERROR$,OBJAUTH$,I_OBJAUTH1,I_OBJAUTH2,C_OBJ#_INTCOL#,I_OBJ#_INTCOL#,HISTGRM$,I_H_OBJ#_COL#,HIST_HEAD$,I_HH_OBJ#_COL#,I_HH_OBJ#_INTCOL#
...
EMORY_TARGET_ADVICE,GV_$MEMORY_RESIZE_OPS,GV$MEMORY_RESIZE_OPS,GV_$MEMORY_CURRENT_RESIZE_OPS,GV$MEMORY_CURRENT_RESIZE_OPS,GV_$MEMORY_DYNAMIC_COMPONENTS,GV$MEMORY_DYNAMIC_COMPONENTS,V_$SEGMENT_STATISTI
CS,V$SEGMENT_STATISTICS,V_$SEGSTAT_NAME,V$SEGSTAT_NAME,V_$SEGSTAT,V$SEGSTAT,V_$LIBRARY_CACHE_MEMORY,V$LIBRARY_CACHE_MEMORY,V_$JAVA_LIBRARY_CACHE_MEMORY,V$JAVA_LIBRARY_CACHE_MEMORY,V_$SHARED_POOL_ADVIC
E,V$SHARED_POOL_ADVICE,V_$JAVA_POOL_ADVICE,V$JAVA_POOL_ADVICE,V_$STREAMS_POOL_ADVICE,V$STREAMS_POOL_ADVICE,V_$GOLDENGATE_CAPABILITIES,V$GOLDENGATE_CAPABILITIES,V_$SGA_CURRENT_RESIZE_OPS,V$SGA_CURRENT_
RESIZE_OPS,V_$SGA_RESIZE_OPS,V$SGA_RESIZE_OPS,V_$SGA_DYNAMIC_COMPONENTS,V$SGA_DYNAMIC_COMPONENTS,V_$SGA_DYNAMIC_FREE_MEMORY,V$SGA_DYNAMIC_FREE_MEMORY,V_$RESUMABLE,...
--//截斷,並且不顯示計數.

--//還可以使用XMLAGG.
SCOTT@test01p> select  xmlagg(xmlelement(e,object_name||',')).extract('//text()') c200 from tx;
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I_FILE#_BLOCK#,I_OBJ3,I_TS1,I_CON1,IND$,CDEF$,C_TS#,I_CCOL2,I_PROXY_DATA$,I_CDEF4,I_TAB1,CLU$,I_PROXY_ROLE_DATA$_1,I_OBJ1,UNDO$,I_UNDO2,I_TS#,I_FILE1,I_COL2,I_OBJ#,C_OBJ#,I_CDEF3,C_COBJ#,CCOL$,I_OBJ5,
PROXY_ROLE_DATA$,I_CDEF1,C_USER#,C_FILE#_BLOCK#,FET$,I_CON2,I_OBJ4,CON$,I_CDEF2,ICOL$,I_COL3,I_CCOL1,COL$,I_ICOL1,UET$,PROXY_DATA$,USER$,I_PROXY_ROLE_DATA$_2,I_OBJ2,TAB$,I_COBJ#,I_USER#,FILE$,OBJ$,TS$
,I_UNDO1,BOOTSTRAP$,I_COL1,I_FILE2,I_IND1,I_USER2,I_USER1,SEG$,OBJERROR$,OBJAUTH$,I_OBJAUTH1,I_OBJAUTH2,C_OBJ#_INTCOL#,I_OBJ#_INTCOL#,HISTGRM$,I_H_OBJ#_COL#,HIST_HEAD$,I_HH_OBJ#_COL#,I_HH_OBJ#_INTCOL#
....
R,GV_$AQ_BACKGROUND_COORDINATOR,GV$AQ_BACKGROUND_COORDINATOR,V_$AQ_JOB_COORDINATOR,V$AQ_JOB_COORDINATOR,GV_$AQ_JOB_COORDINATOR,GV$AQ_JOB_COORDINATOR,V_$AQ_SERVER_POOL,V$AQ_SERVER_POOL,GV_$AQ_SERVER_PO
OL,GV$AQ_SERVER_POOL,V_$AQ_CROSS_INSTANCE_JOBS,V$AQ_CROSS_INSTANCE_JOBS,GV_$AQ_CROSS_INSTANCE_JOBS,GV$AQ_CROSS_INSTANCE_JOBS,GV_$CON_SYSMETRIC,GV$CON_SYSMETRIC,GV_$CON_SYSMETRIC_HISTORY,GV$CON_SYSMETR
IC_HISTORY,GV_$CON_SYSMETRIC_SUMMARY,GV$CON_SYSMETRIC_SUMMARY,GV_$SYSMETRIC_SUMMARY,GV$SYSMETRIC_SUMMARY,GV_$SESSMETRIC,GV$SESSMETRIC,GV_$FILEMETRIC,GV$FILEMETRIC,GV_$FILEMETRIC_HISTORY,GV$FILEMETRIC_
HISTORY,GV_$EVENTMETRIC,GV$EVENTMETRIC,GV_$WAITCLASSMETRIC,GV$WAITCLASSMETRIC,GV_$WAITCLASSMETRIC_HISTORY,GV$WAITCLASSMETRIC_HISTORY,GV_$SERVICEMETRIC,

--//全部顯示,並且結尾有1個,不熟悉語法,不知道如何按照object_id排序輸出.

select listagg(object_name, ',' on overflow truncate) within group(order by object_id) c200 from tx;
select listagg(object_name, ',' on overflow truncate 'string is too long') within group(order by object_id) c200 from tx;
select listagg(object_name, ',' on overflow truncate without count) within group(order by object_id) c200 from tx;

Select deptno,listagg(ename,'!') within group (order by sal) enames from emp group by deptno ;