Optimized approach to addressing LISTAGG() and the ORA-01489 error

Two previous posts have discussed the topic of LISTAGG() and the ORA-01489: result of string concatenation is too long error. This occurs when aggregations of strings exceed the 32767 limit of the VARCHAR2 datatype. The first article, Oracle LISTAGG() — ORA-01489: result of string concatenation is too long, provides a way to “truncate” aggregations being passed to LISTAGG() to show as much data as possible without exceeding the VARCHAR2 size limit. The second, ORA-01489 – Aggregating using CLOBs to handle long strings, provides a user-defined aggregation function that works on CLOBs. This approach addresses the size limitations of VARCHAR2, but suffers heavily in performance due to CLOB processing. This article presents one additional alternative. This user-defined aggregate function is designed to operate on VARCHAR2 datatypes, but relies on CLOBs internally to move beyond the VARCHAR2 size limitation. A significant performance improvement is realized thanks to faster processing and appending of VARCHAR2s when available. CLOBs are only used once the length of data exceeds the VARCHAR2 limit.

SQL> CREATE OR REPLACE
  2  TYPE VarCharAggregator
  3  AS OBJECT
  4  (
  5    separator VARCHAR2( 20 ),     -- Delimeter for the fields
  6    separator_length NUMBER,      -- Length of the delimeter
  7    buffer VARCHAR2( 4000 ),      -- Used to aggregate strings until the maximum VARCHAR2 length is exceeded
  8    buffer_length NUMBER,         -- Current length of the data in buffer
  9    aggregate_clob CLOB,          -- The aggregated CLOB value into which the buffer is moved as it exceeds the VARCHAR2 length
 10    has_clob_data VARCHAR2( 1 ),  -- Flag to indicate whether any data has been moved to the aggregate_clob
 11
 12    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT VarCharAggregator )
 13      RETURN NUMBER,
 14    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT VarCharAggregator, value IN VARCHAR2 )
 15      RETURN NUMBER,
 16    MEMBER FUNCTION ODCIAggregateTerminate( self IN VarCharAggregator, returnValue OUT CLOB, flags IN number )
 17      RETURN NUMBER,
 18    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT VarCharAggregator, ctx2 IN VarCharAggregator )
 19      RETURN NUMBER
 20  );
 21  /

Type created.


SQL> CREATE OR REPLACE
  2  TYPE BODY VarCharAggregator
  3  IS
  4
  5    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT VarCharAggregator )
  6    RETURN NUMBER
  7    IS
  8    BEGIN
  9
 10      sctx := VarCharAggregator( ',', LENGTH( ',' ), '', 0, NULL, 'N' );
 11      DBMS_LOB.CREATETEMPORARY( sctx.aggregate_clob, TRUE, DBMS_LOB.CALL );
 12
 13      RETURN ODCIConst.Success;
 14
 15    END;
 16
 17    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT VarCharAggregator, value IN VARCHAR2 )
 18    RETURN NUMBER
 19    IS
 20      value_length NUMBER;
 21    BEGIN
 22
 23      value_length := LENGTH( value );
 24
 25      -- Move "buffer" into aggregate_clob once appending "value" to "buffer" will exceed the maximum VARCHAR2 length
 26      IF ( self.buffer_length > 0 ) AND ( value_length + self.buffer_length > ( 4000 - self.separator_length ) ) THEN
 27
 28        IF self.has_clob_data = 'Y' THEN
 29          DBMS_LOB.APPEND( self.aggregate_clob, TO_CLOB( self.separator ) );
 30        END IF;
 31
 32        DBMS_LOB.APPEND( self.aggregate_clob, TO_CLOB( self.buffer ) );
 33        self.has_clob_data := 'Y';
 34        self.buffer := '';
 35        self.buffer_length := 0;
 36
 37      END IF;
 38
 39      -- Insert a separator value if we already have data in the buffer
 40      IF self.buffer_length > 0 THEN
 41        self.buffer := self.buffer || self.separator;
 42      END IF;
 43
 44      -- Append "value" to our buffer
 45      self.buffer := self.buffer || value;
 46      self.buffer_length := self.buffer_length + value_length;
 47
 48      RETURN ODCIConst.Success;
 49
 50    END;
 51
 52    MEMBER FUNCTION ODCIAggregateTerminate( self IN VarCharAggregator, returnValue OUT CLOB, flags IN number)
 53    RETURN NUMBER
 54    IS
 55    BEGIN
 56
 57      returnValue := self.aggregate_clob;
 58
 59      IF LENGTH( self.buffer ) > 0 THEN
 60
 61        IF self.has_clob_data = 'Y' THEN
 62          DBMS_LOB.APPEND( returnValue, self.separator );
 63        END IF;
 64
 65        DBMS_LOB.APPEND( returnValue, self.buffer );
 66
 67      END IF;
 68
 69      RETURN ODCIConst.Success;
 70
 71    END;
 72
 73    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT VarCharAggregator, ctx2 IN VarCharAggregator )
 74    RETURN NUMBER
 75    IS
 76    BEGIN
 77
 78      DBMS_LOB.APPEND( self.aggregate_clob, self.separator );
 79      DBMS_LOB.APPEND( self.aggregate_clob, ctx2.aggregate_clob );
 80
 81      RETURN ODCIConst.Success;
 82
 83    END;
 84
 85  END;
 86  /

Type body created.


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

Function created.

When aggregating 500 rows, this revised version runs approximately 2.5 times faster than the CLOB-only function of the previous article. When aggregating 1000 rows, it runs approximately twice as fast. An example of usage follows:

SQL> SELECT VARCHARAGG( value )
  2  FROM test_strings;

VARCHARAGG(VALUE)
---------------------------------
test1,test2,test3
Advertisements

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.