Back in 2009 , I blogged about 'LISTAGG' in 11g Release 2 ( http://mfzahirdba.blogspot.de/2009/10/analytics-20.html) to concatenate the columns into one string.
It was neat but we ran into a error if the resultant was more than 4000 characters.
With this release ( 12c release 2) , it has gotten little bit better . We can avoid the ORA-01489 by adding few clauses .
Let us create a test case.
1 SELECT owner ,
2 object_type ,
3 listagg(object_name , '~' ) within GROUP (
4 ORDER BY object_name )
5 FROM t
6 WHERE owner IN ('ZAHIR' )
7 GROUP BY owner ,
8 object_type
9* ORDER BY object_type
SQL> /
ERROR:
ORA-01489: result of string concatenation is too long
When we add the overflow clause as highlighed below, it does not produce error but truncates the result with the wordcount of how many characters left in the string.
1 SELECT owner ,
2 object_type ,
3 listagg(object_name , '~' ON overflow
4 TRUNCATE ) within GROUP (
5 ORDER BY object_name )
6 FROM t
7 WHERE owner IN ('ZAHIR' , 'OUTLN')
8 GROUP BY owner ,
9 object_type
10* ORDER BY object_type
SQL> /
OWNER OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
LISTAGG(OBJECT_NAME,'~'ONOVERFLOWTRUNCATE)WITHINGROUP(ORDERBYOBJECT_NAME)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ZAHIR INDEX
IDX_EMP
ZAHIR INDEX PARTITION
BIN$Sh
....
...
....
~BIN$ShGc5cGpFengUwEAAH80sg==$0~...(600)
By default , the wordcount appears . We can remove the wordcount as below.
1 SELECT owner ,
2 object_type ,
3 listagg(object_name , '~' ON overflow
4 TRUNCATE WITHOUT COUNT ) within GROUP (
5 ORDER BY object_name )
6 FROM t
7 WHERE owner IN ('ZAHIR' )
8 GROUP BY owner ,
9 object_type
10* ORDER BY object_type
SQL> /
ZAHIR INDEX
IDX_EMP
ZAHIR INDEX PARTITION
BIN$Sh
....
...
....
~BIN$ShGc5cGpFengUwEAAH80sg==$0~...
We can add explicit information with some static text . In my example below , I have added a text as 'Characters Trimmed'
1 SELECT owner ,
2 object_type ,
3 listagg(object_name , '~' ON overflow
4 TRUNCATE 'Characters Trimmed'
5 WITH COUNT ) within GROUP (
6 ORDER BY object_name )
7 FROM t
8 WHERE owner IN ('ZAHIR' )
9 GROUP BY owner ,
10 object_type
11* ORDER BY object_type
ZAHIR INDEX
IDX_EMP
ZAHIR INDEX PARTITION
BIN$Sh
....
...
....
~BIN$ShGc5cGpFengUwEAAH80sg==$0~Characters Trimmed(601)
These are good enhancements . I wish , we have a distinct options in the LISTAGG results . That will be immensely helpful. Hopefully , we will see this in the next few releases.
Comments welcome.
Comments welcome.
