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

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.

Oracle – Handling PL/SQL errors in a static and dynamic context

PL/SQL error handling is done using the EXCEPTION block of the PL/SQL procedure. The construct of this block is fairly straightforward and well documented. The intent of this article is not to explain this block, but to present two approaches to error handling. The first is a “static” approach, in which possible error codes are defined at compile time. This is the most commonly used approach. However, sometimes it is useful to define procedures that can be passed dynamic error codes to be handled. This is sometimes useful for library functions where a single block of code my raise different errors based on the conditions in the calling function. The calling function may pass in an error code that is expected into the function where it can be handled. The example below encapsulates both of these approaches:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2
  3    -- Raise an expected error
  4    PROCEDURE div_by_zero
  5    IS
  6      result NUMBER;
  7    BEGIN
  8      result := 1/0;
  9    END;
 10
 11    -- Handle an error statically
 12    PROCEDURE handle_static_error
 13    IS
 14      div_ex EXCEPTION;
 15
 16      PRAGMA EXCEPTION_INIT( div_ex, -1476 );
 17    BEGIN
 18      div_by_zero;
 19    EXCEPTION
 20      WHEN div_ex THEN
 21        DBMS_OUTPUT.PUT_LINE( 'Captured static divide by zero' );
 22    END;
 23
 24    -- Handle a passed-in error code
 25    PROCEDURE handle_dynamic_error( expected_ex NUMBER )
 26    IS
 27    BEGIN
 28      div_by_zero;
 29    EXCEPTION
 30      WHEN OTHERS THEN
 31        IF SQLCODE = expected_ex THEN
 32          DBMS_OUTPUT.PUT_LINE( 'Captured dynamic error, code='
 33            || expected_ex );
 34        ELSE
 35          RAISE;
 36        END IF;
 37    END;
 38
 39  BEGIN
 40
 41    handle_static_error;
 42    handle_dynamic_error( -1476 );
 43
 44  END;
 45  /
Captured static divide by zero
Captured dynamic error, code=-1476

PL/SQL procedure successfully completed.

The div_by_zero procedure exists only as an example error to be handled. The error code for divide by zero is -1476, which will be of interest in the following paragraphs. Let’s first focus on the handle_static_error procedure. As the name suggests, this function handles a static error code defined at compile time. In the declaration block of the procedure, note the div_ex EXCEPTION; and PRAGMA EXCEPTION_INIT( div_ex, -1476 ); statements. The first simply defines an EXCEPTION variable and gives it a name. The call to EXCEPTION_INIT is key in that it maps the error code of interest to our EXCEPTION variable. With this information in hand, it is time to look to the EXCEPTION block of the handle_static_error procedure. The WHEN div_ex THEN statement defines where the divide by zero error will be captured and handled. The DBMS_OUTPUT statement simply writes to the console as an error handler. Defining EXCEPTION variables like this is the most common approach to error handling.
The handle_dynamic_error procedure demonstrates an alternative approach to error handling. This procedure takes an error code as a parameter. In the EXCEPTION block of this procedure, note that no EXCEPTION variable is referenced. Instead, the IF SQLCODE = expected_ex THEN uses a a special PL/SQL function. SQLCODE returns the code of the most recently raised error, which is then compared to the error code passed in as a parameter to the procedure. If a match exists, the DBMS_OUTPUT line is called as the error handler.
Generally speaking, the use of EXCEPTION variables and the PRAGMA EXCEPTION_INIT statement is the preferable way to implement error handling. However, the above does provide an alternative when dynamic error codes are necessary.