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.
Pingback: Optimized approach to addressing LISTAGG() and the ORA-01489 error | brainFizzle
Pingback: Oracle LISTAGG-Funktion läuft auf Fehler ORA-01489 | Oracle APEX - Erfahrungen, Tipps und Tricks
Would be more useful if the separator character/string could be passed into the method.
LikeLike