24. Januar 2014

SQL LISTAGG mit CLOB-Ausgabe - kein VARCHAR2-Limit mehr

CLOB output for LISTAGG function: No VARCHAR2 limit any more
Mit User Defined Aggregates, also der Möglichkeit, eigene SQL-Aggregatsfunktionen zu bauen, die sich dann gemeinsam mit GROUP BY nutzen lassen, habe ich mich schon etwas häufiger beschäftigt ...
Speziell das erste Beispiel ist eigentlich mit Oracle11g überflüssig geworden, denn die neue Funktion LISTAGG ist nun out-of-the-box vorhanden. Allerdings bin ich auch selbst schon einige Male gegen die VARCHAR2-Grenze gelaufen, welche die Verwendung von LISTAGG mit sich bringt - der zusammenkonkatenierte String kann nicht länger als 4000 Byte sein ...
SQL> select listagg(cust_last_name) within group (order by cust_last_name) 
  2  from sh.customers;
select listagg(cust_last_name) within group (order by cust_last_name) from sh.customers
                                                                              *
FEHLER in Zeile 1:
ORA-01489: Das Ergebnis der Zeichenfolgenverkettung ist zu lang
Das schränkt die Nutzung von LISTAGG doch ein wenig ein - schließlich können sich in der Praxis recht schnell Zeichenketten ergeben, die länger als 4000 Byte sind. Die Antwort darauf ist wohl wieder ein User Defined Aggregate. Die müsste genauso aussehen wie die oben genannte "LISTAGG"-Funktion für Oracle10g und früher, allerdings muss sie ein CLOB anstelle eines VARCHAR2 zurückgeben.
Und dabei sind ein paar kleine Details zu beachten ...
  • Bis 32767 Byte kann ein CLOB genauso zusammengesetzt (konkateniert) werden wie ein VARCHAR2
  • Ab 32767 bytes muss ein temporärer LOB erzeugt und mit dem Package DBMS_LOB weiterbearbeitet werden. Diese Funktionen sind aber wesentlich langsamer.
Ich habe mich also entschieden, die Aggregatsfunktion blockweise arbeiten zu lassen. Das Aggregat wird stets in einer VARCHAR2-Variablen verkettet - und wenn dieser "Buffer" die Grenze von 4000 Byte überschreitet, dann wird er an den CLOB angehangen und das Ganze beginnt von vorn. Aber genug der Vorrede - hier ist das User Defined Aggregate LISTAGG_CLOB:
drop function listagg_clob;
drop type listagg_clob_t;

create or replace package list_const_p
is
  list_sep varchar2(10) := ',';
end list_const_p;
/
sho err

create type listagg_clob_t as object(
  v_liststring varchar2(32767),
  v_clob       clob,
  v_templob    number,

  static function ODCIAggregateInitialize(
    sctx IN OUT listagg_clob_t
  ) return number,
  member function ODCIAggregateIterate(
    self IN OUT listagg_clob_t, value IN varchar2
  ) return number,
  member function ODCIAggregateTerminate(
    self IN OUT listagg_clob_t, returnValue OUT clob, flags IN number
  ) return number,
  member function ODCIAggregateMerge(
    self IN OUT listagg_clob_t, ctx2 IN OUT listagg_clob_t
  ) return number
 );
/
sho err

create or replace type body listagg_clob_t is

static function ODCIAggregateInitialize(sctx IN OUT listagg_clob_t)
return number is
begin
  sctx := listagg_clob_t('', '', 0);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
  self IN OUT listagg_clob_t, 
  value IN varchar2
) return number is
begin
  if nvl(lengthb(v_liststring),0) + nvl(lengthb(value),0) <= 4000 then 
    self.v_liststring:=self.v_liststring || value || list_const_p.list_sep;
  else 
    if self.v_templob = 0 then
      dbms_lob.createtemporary(self.v_clob, true, dbms_lob.call);
      self.v_templob := 1;
    end if;
    dbms_lob.writeappend(self.v_clob, length(self.v_liststring), v_liststring);
    self.v_liststring := value || list_const_p.list_sep;
  end if;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
  self IN OUT listagg_clob_t, 
  returnValue OUT clob, 
  flags IN number
) return number is
begin
  if self.v_templob != 0 then
    dbms_lob.writeappend(self.v_clob, length(self.v_liststring), self.v_liststring);
    dbms_lob.trim(self.v_clob, dbms_lob.getlength(self.v_clob) - 1);
  else
    self.v_clob := substr(self.v_liststring, 1, length(self.v_liststring) - 1);
  end if;
  returnValue := self.v_clob;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT listagg_clob_t, ctx2 IN OUT listagg_clob_t) return number is
begin
  if ctx2.v_templob != 0 then
    if self.v_templob != 0 then
      dbms_lob.append(self.v_clob, ctx2.v_clob);
      dbms_lob.freetemporary(ctx2.v_clob);
      ctx2.v_templob := 0;
    else 
      self.v_clob := ctx2.v_clob;
      self.v_templob := 1;
      ctx2.v_clob := '';
      ctx2.v_templob := 0;
    end if;
  end if;
  if nvl(lengthb(self.v_liststring),0) + nvl(lengthb(ctx2.v_liststring),0) <= 4000 then
    self.v_liststring := self.v_liststring || ctx2.v_liststring;
    ctx2.v_liststring := '';
  else 
    if self.v_templob = 0 then
      dbms_lob.createtemporary(self.v_clob, true, dbms_lob.call);
      self.v_templob := 1;
    end if;
    dbms_lob.writeappend(self.v_clob, length(self.v_liststring), self.v_liststring);
    dbms_lob.writeappend(self.v_clob, length(ctx2.v_liststring), ctx2.v_liststring);
    self.v_liststring := '';
    ctx2.v_liststring := '';
  end if;
  return ODCIConst.Success;
end;
end;
/
sho err

CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob
PARALLEL_ENABLE AGGREGATE USING listagg_clob_t;
/
sho err
Diese Funktion arbeitet mit kleinen ...
SQL> select listagg_clob(ename) from scott.emp;

LISTAGG_CLOB(ENAME)
--------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL
LER

1 Zeile wurde ausgewählt.
... und mit großen Datenmengen ...
SQL> select listagg_clob(cust_last_name) from sh.customers;

LISTAGG_CLOB(CUST_LAST_NAME)
--------------------------------------------------------------------------------
Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Everett,Everet
t,Everett,Everett,Everett,Everett,Everett,Everett,Everett,Everett,Everett,Everet
t,Everett,Everett,Everett,Everett,Kenney,Kenney,Kenney,Kenney,Kenney,Kenney,Kenn
ey,Kenney,Kenney,Kenney,Kenney,Kenney,Kenney,Kenney,Kenney,Robbinette,Robbinette
,Robbinette,Robbinette,Robbinette,Robbinette,Robbinette,Robbinette,Robbinette,Ro
:
:

1 Zeile wurde ausgewählt.

SQL> select dbms_lob.getlength(listagg_clob(cust_last_name)) from sh.customers;

DBMS_LOB.GETLENGTH(LISTAGG_CLOB(CUST_LAST_NAME))
------------------------------------------------
                                          394256 mehr als 4000 Byte!

1 Zeile wurde ausgewählt.
Natürlich muss man im Hinterkopf behalten, dass das Generieren von sehr großen Verkettungen durch die vielen LOB-Operationen auch mehr Zeit kostet - aber es funktioniert - und darauf kommt es an. Viel Spaß beim Ausprobieren - Feedback bitte einfach per Kommentar.
In the past, I already published a few blob postings on User Defined Aggregates - which allow to create custom aggregate functions. An "aggregate" function can be used in a SQL Query together with GROUP BY ...
Particularly the first example is obsolete nowadays: Oracle provides the LISTAGG function out-of-the-box since version 11. But customers told me (and I experienced it myself as well), that they frequently run into the VARCHAR2 limitation of 4000 bytes when using LISTAGG. Sure, LISTAGG returns a VARCHAR2, it's a SQL function and therefore, the resulting string is limited to 4000 byte.
SQL> select listagg(cust_last_name) within group (order by cust_last_name) 
  2  from sh.customers;
select listagg(cust_last_name) within group (order by cust_last_name) from sh.customers
                                                                              *
ERROR at line 1:
ORA-01489: result of string concatenation is too long
This seems to be a real showstopper in some situations - in practice, concatenated strings might be longer than 4000 bytes ... so no LISTAGG any more ...? The answer is - again - a user defined aggregate. It should look similar to the above mentioned "LISTAGG" function for "pre 11g" databases, but it will return a CLOB instead of a VARCHAR2. My implementation is led by the following two thoughts ...
  • Until the length of 32767 bytes, a CLOB can be treated the same (performant) way as a VARCHAR2
  • CLOBs longer than 32767 bytes must be created as a temporary lob and further processed with DBMS_LOB But these procedures are (of course) significantly slower.
So I decided to let my aggregate function with a "buffer". The concatenation primarily takes place on a VARCHAR2 instance (as the "buffer"). When its length exceeds 4000 bytes, it will be concatenated to the final CLOB instance. And this is done, until all values have been concatenated. This should be enough for explanation, here is the code ...
drop function listagg_clob;
drop type listagg_clob_t;

create or replace package list_const_p
is
  list_sep varchar2(10) := ',';
end list_const_p;
/
sho err

create type listagg_clob_t as object(
  v_liststring varchar2(32767),
  v_clob       clob,
  v_templob    number,

  static function ODCIAggregateInitialize(
    sctx IN OUT listagg_clob_t
  ) return number,
  member function ODCIAggregateIterate(
    self IN OUT listagg_clob_t, value IN varchar2
  ) return number,
  member function ODCIAggregateTerminate(
    self IN OUT listagg_clob_t, returnValue OUT clob, flags IN number
  ) return number,
  member function ODCIAggregateMerge(
    self IN OUT listagg_clob_t, ctx2 IN OUT listagg_clob_t
  ) return number
 );
/
sho err

create or replace type body listagg_clob_t is

static function ODCIAggregateInitialize(sctx IN OUT listagg_clob_t)
return number is
begin
  sctx := listagg_clob_t('', '', 0);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
  self IN OUT listagg_clob_t, 
  value IN varchar2
) return number is
begin
  if nvl(lengthb(v_liststring),0) + nvl(lengthb(value),0) <= 4000 then 
    self.v_liststring:=self.v_liststring || value || list_const_p.list_sep;
  else 
    if self.v_templob = 0 then
      dbms_lob.createtemporary(self.v_clob, true, dbms_lob.call);
      self.v_templob := 1;
    end if;
    dbms_lob.writeappend(self.v_clob, length(self.v_liststring), v_liststring);
    self.v_liststring := value || list_const_p.list_sep;
  end if;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
  self IN OUT listagg_clob_t, 
  returnValue OUT clob, 
  flags IN number
) return number is
begin
  if self.v_templob != 0 then
    dbms_lob.writeappend(self.v_clob, length(self.v_liststring), self.v_liststring);
    dbms_lob.trim(self.v_clob, dbms_lob.getlength(self.v_clob) - 1);
  else
    self.v_clob := substr(self.v_liststring, 1, length(self.v_liststring) - 1);
  end if;
  returnValue := self.v_clob;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT listagg_clob_t, ctx2 IN OUT listagg_clob_t) return number is
begin
  if ctx2.v_templob != 0 then
    if self.v_templob != 0 then
      dbms_lob.append(self.v_clob, ctx2.v_clob);
      dbms_lob.freetemporary(ctx2.v_clob);
      ctx2.v_templob := 0;
    else 
      self.v_clob := ctx2.v_clob;
      self.v_templob := 1;
      ctx2.v_clob := '';
      ctx2.v_templob := 0;
    end if;
  end if;
  if nvl(lengthb(self.v_liststring),0) + nvl(lengthb(ctx2.v_liststring),0) <= 4000 then
    self.v_liststring := self.v_liststring || ctx2.v_liststring;
    ctx2.v_liststring := '';
  else 
    if self.v_templob = 0 then
      dbms_lob.createtemporary(self.v_clob, true, dbms_lob.call);
      self.v_templob := 1;
    end if;
    dbms_lob.writeappend(self.v_clob, length(self.v_liststring), self.v_liststring);
    dbms_lob.writeappend(self.v_clob, length(ctx2.v_liststring), ctx2.v_liststring);
    self.v_liststring := '';
    ctx2.v_liststring := '';
  end if;
  return ODCIConst.Success;
end;
end;
/
sho err

CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob
PARALLEL_ENABLE AGGREGATE USING listagg_clob_t;
/
sho err
This will work with small ...
SQL> select listagg_clob(ename) from scott.emp;

LISTAGG_CLOB(ENAME)
--------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL
LER

1 row selected.
... as well as with large datasets.
SQL> select listagg_clob(cust_last_name) from sh.customers;

LISTAGG_CLOB(CUST_LAST_NAME)
--------------------------------------------------------------------------------
Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Ruddy,Everett,Everet
t,Everett,Everett,Everett,Everett,Everett,Everett,Everett,Everett,Everett,Everet
t,Everett,Everett,Everett,Everett,Kenney,Kenney,Kenney,Kenney,Kenney,Kenney,Kenn
ey,Kenney,Kenney,Kenney,Kenney,Kenney,Kenney,Kenney,Kenney,Robbinette,Robbinette
,Robbinette,Robbinette,Robbinette,Robbinette,Robbinette,Robbinette,Robbinette,Ro
:
:

1 row selected.

SQL> select dbms_lob.getlength(listagg_clob(cust_last_name)) from sh.customers;

DBMS_LOB.GETLENGTH(LISTAGG_CLOB(CUST_LAST_NAME))
------------------------------------------------
                                          394256 more than 4000 bytes!

1 row selected.
Keep in mind that using this function on large datasets will lead to the construction of very large CLOBs - this will take its time. But it works - and that is the importang thing. Have fun trying it out ...

Kommentare:

Ғаьіаԉ Ԍӷоье hat gesagt…

Vielen Dank!
Bin schon zu oft über ORA-01489 im Zusammenhang mit Listagg gestolpert..

Sven Lieber hat gesagt…

Super, genau was ich gesucht habe =)

Wünschenswert wäre natürlich noch eine Parametrisierung mit dem gewünschen Separator. (Manchmal erzeuge ich JSON Objekte die Komma Separiert sein sollen und manchmal HTML Elemente die einfach durch einen leeren String separiert sein sollen)

Leider ist der 11gR2 Doku folgendes zu entnehmen:
Restriction on AGGREGATE USING If you specify this clause, then you can specify only one input argument for the function.

Die Built-In Function LISTAGG besitzt diese Restriktion nicht.

Gibt es mit der hier vorgestellten Möglichkeit ein Workaround? Also ohne den list_sep im list_const package on demand ändern zu müssen?

Carsten Czarski hat gesagt…

Hallo Sven,

leider nein. Die Package Variable ist schon mein Workaround dafür, dass man in ein User Defined Aggregate eben nur die eine Tabellenspalte hereingeben kann.

Eine andere Variante (für den Fall mit dem Trennzeichen aber nicht relevant) wäre die Übergabe bzw. das Aggregieren eines Objekttypen, der ja mehrere Attribute haben kann. Wie das aussehen kann, habe ich auch mal in einem Blog Posting beleuchtet: http://sql-plsql-de.blogspot.co.uk/2011/03/noch-ein-szenario-mit-user-defined.html

Hier werden Instanzen eines Objekttypen an das User Defined Aggregate übergeben - das heißt, in jeder Iteration steht nicht ein Wert, sondern drei Werte zur Verfügung. Für "globale" Dinge wie das Trennzeichen ist dieser Ansatz aber weniger passend ...

Ich hoffe, das hilft Dir weiter.

Beste Grüße

Carsten

Scott Wesley hat gesagt…

This is an amazing solution, well done.

Anton Scheffer hat gesagt…

Do you think is a bug in Oracle or a bug in the implementation of the aggregate function?

with t as
( select/*+ materialize */ level n from dual x connect by level <= 4
)
select /*+ parallel( 2 ) */ length( listagg_clob( rpad( 'test', 4000 ) ) )
from t

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 1146
ORA-06512: at "SCOTT.LISTAGG_CLOB_T", line 67

Carsten Czarski hat gesagt…

Hi Anton,

hmmm ... tjis happens only when The PARALLEL hint is active? If yes, the problem might be in my ODCIAggrgateMerge function. Let me have a look ....


Best regards

Carsten

Anton Scheffer hat gesagt…

Hi, as far as I know this error occurs always when ODCIAggrgateMerge is executed, and that function is executed when the query is executed in parallel. The easiest way to force that is using the materialize and parallel hints as in my example. I found this error in an implementation of my own, https://technology.amis.nl/2015/03/13/using-an-aggregation-function-to-query-a-json-string-straight-from-sql and thought you knew more about it.

Carsten Czarski hat gesagt…

Hi Anton,

I just looked into this on a 11.2.0.4 database. There I get another error as soon as I use the PARALLEL hint:

with t as
*
FEHLER in Zeile 1:
ORA-22922: Nicht vorhandener LOB-Wert

I did some few debugging and noticed that the only "ODCIAggregateInitialize" is being called successfully. The Iterate function is not being called any more - so the error seems to be somewhere within the Aggregate Framework ...

Best regards

-Carsten

Carsten Czarski hat gesagt…

Hi Anton,

hey ... I just noticed. Have tried to remove the MATERIALIZE hint while keeping PARALLEL ...

SQL> with t as
2 ( select/* materialize */ level n from dual x connect by level <= 4
3 )
4 select /*+ parallel( 2 ) */ length( listagg_clob( rpad( 'test', 4000 ) ) )
5 from t
6 ;

LENGTH(LISTAGG_CLOB(RPAD('TEST',4000)))
---------------------------------------
16003

1 Zeile wurde ausgewählt.

Iterate
Iterate
Iterate
Iterate

So I'd assume some weird issue of the aggregation framework in combination with PARALLEL and MATERIALIZE hints (and the latter one is de-jure undocumented ...)


regards

-Carsten

Dorian hat gesagt…

Hallo,

ich bekomme (ab einer gewissen Anzahl von Daten) den Fehler in der Funktion ODCIAggregateIterate,
Error at line 0
ORA-06502: PL/SQL: numerischer oder Wertefehler
ORA-06512: in "SYS.DBMS_LOB", Zeile 1146
ORA-06512: in "DILOPER.LISTAGG_CLOB_T", Zeile 22

eine Idee woran das liegen könnte?

Carsten Czarski hat gesagt…

Hallo Dorian,

hmmm ... hast Du da einen Testcase für mich ... müsste ich mir im Detail ansehen ...

Grüße

-Carsten

Beliebte Postings