Oracle LISTAGG(), ORA-01489 – Aggregating using CLOBs to handle long strings

In a previous post, Oracle LISTAGG() — ORA-01489: result of string concatenation is too long, I wrote about long string concatenations exceeding the 32767 limit for VARCHAR2s. This article provided a query that limited the total length of the concatenation to cut itself off at 32767. This allows the user to use the advantages of LISTAGG() while returning as much data as possible.

The goal of this article is to provide an alternative that will make use of CLOBs to avoid character limits. Below is a user-defined aggregate function that is loosely based on askTom’s STRAGG() function. This function allows aggregation of very long strings, but note that this is an aggregate function, not an analytic function. It does not support the user-defined delimiter, WITHIN GROUP() clause and PARTITION BY clause that make LISTAGG() quite useful. The order in which strings are aggregated cannot be specified by the user.

SQL> CREATE OR REPLACE
  2  TYPE ClobAggregator
  3  AS OBJECT
  4  (
  5    aggregate_clob CLOB,
  6
  7    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT ClobAggregator )
  8      RETURN NUMBER,
  9    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ClobAggregator, value IN CLOB )
 10      RETURN NUMBER,
 11    MEMBER FUNCTION ODCIAggregateTerminate( self IN ClobAggregator, returnValue OUT CLOB, flags IN number )
 12      RETURN NUMBER,
 13    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ClobAggregator, ctx2 IN ClobAggregator )
 14      RETURN NUMBER
 15  );
 16  /

Type created.


SQL> CREATE OR REPLACE
  2  TYPE BODY ClobAggregator
  3  IS
  4
  5    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT ClobAggregator )
  6    RETURN NUMBER
  7    IS
  8      temp_clob CLOB;
  9    BEGIN
 10
 11      DBMS_LOB.CREATETEMPORARY( temp_clob, TRUE, DBMS_LOB.CALL );
 12
 13      sctx := ClobAggregator( temp_clob );
 14
 15      RETURN ODCIConst.Success;
 16
 17    END;
 18
 19    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ClobAggregator, value IN CLOB )
 20    RETURN NUMBER
 21    IS
 22    BEGIN
 23
 24      IF DBMS_LOB.GETLENGTH( self.aggregate_clob ) > 0 THEN
 25        DBMS_LOB.APPEND( self.aggregate_clob, ',' );
 26      END IF;
 27
 28      DBMS_LOB.APPEND( self.aggregate_clob, value );
 29
 30      RETURN ODCIConst.Success;
 31
 32    END;
 33
 34    MEMBER FUNCTION ODCIAggregateTerminate( self IN ClobAggregator, returnValue OUT CLOB, flags IN number)
 35    RETURN NUMBER
 36    IS
 37    BEGIN
 38
 39      returnValue := self.aggregate_clob;
 40
 41      RETURN ODCIConst.Success;
 42
 43    END;
 44
 45    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ClobAggregator, ctx2 IN ClobAggregator )
 46    RETURN NUMBER
 47    IS
 48    BEGIN
 49
 50      DBMS_LOB.APPEND( self.aggregate_clob, ctx2.aggregate_clob );
 51
 52      RETURN ODCIConst.Success;
 53
 54    END;
 55
 56  END;
 57  /

Type body created.


SQL> CREATE OR REPLACE
  2  FUNCTION CLOBAGG( input CLOB )
  3  RETURN CLOB
  4  PARALLEL_ENABLE AGGREGATE
  5  USING ClobAggregator;
  6  /

Function created.

Below is a small example demonstrating how the function aggregates. This function requires every row to be concatenated to an ongoing CLOB, so performance can be limited for large data sets.

SQL> SELECT CLOBAGG( value )
  2  FROM clob_test;

CLOBAGG(VALUE)
------------------------------------------------------------
Sally,Bob,James,Amelia,Sam,David,Phillip,Patrick,Scott,Bill

As a note of interest, STRAGG() was originally introduced to fill a void in string aggregation prior to the introduction of the LISTAGG() function. It gained widespread use throughout the Oracle community.

Advertisements