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 – REPLACE() for CLOBs

In earlier versions of Oracle, the REPLACE() function did not work natively with CLOBs. By implicitly casting to VARCHAR2s, this function would work, but failed when a CLOB exceeded the maximum VARCHAR2 length. REPLACE() now works directly with CLOBs.

Although it is now antiquated, the function below is an example of one approach to perform a search and replace on a CLOB without actually using the REPLACE() function. It may be useful on older instances of Oracle, but is also a good example of some of the basic DBMS_LOB functions that are still commonly used. It is also a launch-point for more advanced processing.

As a side note, I have seen a number of functions that perform similarly to this function, by simply breaking the CLOB into a series of VARCHAR2 strings and performing a REPLACE() on each of the chunks. The issue with this approach is that it does not address boundary conditions when the search string is split between two of these chunks.

CREATE OR REPLACE
FUNCTION clob_replace( i_src CLOB, i_match VARCHAR2, i_rep VARCHAR2 )
RETURN CLOB
IS
  MAX_VARCHAR2 NUMBER DEFAULT 15000;  -- depending on your character set, 32767 may not be your actual number of available characters

  l_ret_clob CLOB;                    -- temporary CLOB to be returned
  l_next_idx NUMBER;                  -- used to traverse the source CLOB, captures the next instance of the match string
  l_search_begin NUMBER DEFAULT 1;    -- used to traverse the source CLOB, indicates the starting point of the next search
  l_parse_begin NUMBER DEFAULT 1;     -- used to copy date from the source CLOB, indicates the beginning of the copy window
  l_parse_end NUMBER;                 -- used to copy date from the source CLOB, indicates the beginning of the end window

BEGIN

  -- Create a temporary CLOB to be returned
  DBMS_LOB.CREATETEMPORARY( l_ret_clob, TRUE );

  LOOP

    l_next_idx := DBMS_LOB.INSTR( i_src, i_match, l_search_begin, 1 );

    -- Exit once we do not find another occurance of the match string
    EXIT WHEN l_next_idx = 0;

    -- Special handling if we find a match at the first character of the source string
    IF l_next_idx = 1 THEN

      l_parse_begin := LENGTH( i_match ) + 1;

      IF LENGTH( i_rep ) > 0 THEN
        DBMS_LOB.APPEND( l_ret_clob, i_rep );
      END IF;

    ELSE
 
      -- Copy source data up to the match
      l_parse_end := l_next_idx - 1;
      DBMS_LOB.COPY( l_ret_clob, i_src, ( l_parse_end - l_parse_begin + 1 ), DBMS_LOB.GETLENGTH( l_ret_clob ) + 1, l_parse_begin );
      
      -- Replace the match string
      IF LENGTH( i_rep ) > 0 THEN
        DBMS_LOB.APPEND( l_ret_clob, i_rep );
      END IF;

      -- Move the beginning of the next parse window past our match string
      l_parse_begin := l_parse_end + LENGTH( i_match ) + 1;

    END IF;

    -- Move the beginning of the search window past our match string
    l_search_begin := l_next_idx + LENGTH( i_match );

  END LOOP;


  -- Special handling for the trailing final characters of the source CLOB
  IF DBMS_LOB.GETLENGTH( i_src ) > ( l_parse_end  + LENGTH( i_match ) ) THEN

    l_parse_begin := l_parse_end  + LENGTH( i_match ) + 1;
    l_parse_end := DBMS_LOB.GETLENGTH( i_src );
    DBMS_LOB.COPY( l_ret_clob, i_src, ( l_parse_end - l_parse_begin + 1 ), DBMS_LOB.GETLENGTH( l_ret_clob ) + 1, l_parse_begin );

  END IF;

  RETURN l_ret_clob;

END;
/