• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: LISTAGG

ORACLE 12.2 – LISTAGG Enhancements

30 Friday Sep 2016

Posted by troroblog in ORACLE, ORACLE 12c Release 2, SQL

≈ Leave a comment

Tags

LISTAGG, New Feature, ORACLE 12c Release 2, SQL

As in earlier versions LISTAGG still returns a VARCHAR2 datatype with the known limitations (4 or 32 K). But instead of ending in an error on a size overflow we now have the chance to tell the function to truncate the return string in case it gets to long.

The syntax has changed slightly between the beta version of ORACLE 12.2 and the productive one, there some changes had to be done to this post.


LISTAGG default behaviour

The default behaviour is still the same resulting in an Oracle error on size overflow.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X') 
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id, LISTAGG(str,'-') WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ORA-01489: result of string concatenation is too long

ON OVERFLOW TRUNCATE

The new option on overflow truncated leeds to a trunctated string having three dots at the end followed by the number of truncated characters in brackets.

The result lists only the end of the strings created.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE) WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- ------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1068)
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1069)
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1069)

Define overflow characters

We are free to define the text that should be shown as the overflow indicator. The text we want to see in case of an overflow is defined following the “ON OVERFLOW TRUNCATE” option.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE ' [more] ') WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- -----------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1068)
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1069)
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1069)

Omit the counting

The number of truncated characters can be omitted by adding WITHOUT COUNT (default is with count).

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE ' [more] ' WITHOUT COUNT) WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- -----------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 

Conclusion

Truncating the result of a LISTAGG function to avoid the overflow error is a pretty cool feature of Oracle 12.2 SQL. If you need to concatenate strings beyond the size limit of the VARCHAR2 datatype you will still need to write your own CLOB aggregate function but if it is enough to know, that there was more in the string before having concatenated it the on overflow truncate is really handy. 

5 ways to aggregate columns into a comma separated string

05 Monday Oct 2009

Posted by troroblog in ORACLE 11g Release 2, Uncategorized

≈ Leave a comment

Tags

CONNECT BY, LISTAGG, ORACLE, SQL, WM_CONCAT, XMLAGG

Connect By

The first example uses a recursive query which starts with the first node of every group and only shows those rows, where the whole string has been aggregated (connect_by_isleaf = 1).

SELECT deptno
     , rn
     , TRIM(leading ',' FROM SYS_CONNECT_BY_PATH(ename,',')) 
  FROM (SELECT deptno
             , ename
             , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) rn
          FROM emp)
  WHERE connect_by_isleaf = 1
  START WITH rn = 1
 CONNECT BY PRIOR deptno = deptno
       AND PRIOR rn + 1 = rn
 /

XMLAGG

another solution is to use xml functions like xmlagg (aggregate) and xmlelement.

SELECT deptno
      ,RTRIM(XMLAGG(XMLELEMENT(e,ename,',').EXTRACT('//text()')),',') name
  FROM emp
 GROUP BY deptno
 /

 WM_CONCAT

one more solution even if not documented…

SELECT deptno, WM_CONCAT(ename)
  FROM emp
 GROUP by deptno
 /

and if you need the enames to be ordered…

SELECT deptno
     , WM_CONCAT(ename)
 FROM (SELECT deptno, ename 
         FROM emp 
        ORDER BY ename)
 GROUP by deptno
 /

 User defined aggregator

Another solution is to write your own group function

CREATE OR REPLACE TYPE ListAggregator IS OBJECT (
   vList VARCHAR2(4000)
  ,STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT ListAggregator) RETURN NUMBER
  ,MEMBER FUNCTION ODCIAggregateIterate (SELF  IN OUT ListAggregator
                                       , value IN     VARCHAR2 ) RETURN NUMBER
  ,MEMBER FUNCTION ODCIAggregateTerminate (self        IN OUT ListAggregator
                                         , returnValue    OUT VARCHAR2
                                         , flags       IN     NUMBER ) RETURN NUMBER
  ,MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
                                     , ctx2 IN     ListAggregator ) RETURN NUMBER
 );
 /

CREATE OR REPLACE TYPE BODY ListAggregator IS
   STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ListAggregator) RETURN NUMBER IS
   BEGIN
      sctx := ListAggregator(NULL);
      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateInitialize;

   MEMBER FUNCTION ODCIAggregateIterate (self  IN OUT ListAggregator
                                       , value IN     VARCHAR2 ) RETURN NUMBER IS
   BEGIN
      IF value IS NOT NULL
      THEN
         self.vList := concat(concat(self.vList,', '),value);
      END IF;

      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateIterate;

   MEMBER FUNCTION ODCIAggregateTerminate (self        IN OUT ListAggregator
                                         , returnValue    OUT VARCHAR2
                                         , flags       IN     NUMBER ) RETURN NUMBER IS
   BEGIN
      returnValue := LTrim(self.vList, ', ');
      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateTerminate;

   MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
                                     , ctx2 IN     ListAggregator) RETURN NUMBER IS
   BEGIN
      IF self.vList IS NULL
      THEN
         self.vList := ctx2.vList;
      ELSIF ctx2.vList IS NOT NULL
      THEN
         self.vList := concat(concat(self.vList,', '),ctx2.vList);
      END IF;

      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateMerge;
END;
/

CREATE OR REPLACE FUNCTION LIST(vValue IN VARCHAR2) RETURN VARCHAR2
   PARALLEL_ENABLE
   AGGREGATE
   USING ListAggregator;
/

SELECT deptno
     , LIST(ename)
  FROM emp
 GROUP BY deptno
/

LISTAGG

and finally if you are on ORACLE 11R2, there is a built-in function to do list aggregation

SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename)
 FROM emp
 GROUP BY deptno
 /

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...