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 ...

7. Januar 2014

Oracle12c für Entwicker: Januar 2014 - Stuttgart, Berlin, Düsseldorf

Zunächst wünsche ich allen Lesern meines Blogs ein gutes und erfolgreiches Jahr 2014. Im ersten Posting des Jahres fange ich noch etwas gemächlich an und möchte vor allem auf eine Veranstaltung hinweisen, die mein Kollege Sebastian Solbach und ich Ende Januar gemeinsam in Stuttgart, Berlin und Düsseldorf durchführen werden.
Es geht um das neue Datenbankrelease Oracle12c - für Anwendungsentwickler. An einem halben Tag widmen wir uns den neuen SQL- und PL/SQL Erweiterungen und zeigen, wie man diese praktisch einsetzen kann. Einige Themen wie die neuen Identity Columns oder die PL/SQL WITH Klausel habe ich schon in Blog Postings erläutert - aber auf der Veranstaltung gibt es noch viel mehr - mit SQL Pattern Matching (hochinteressant), Application Continuity oder "Flashback Data Archives für alle" seien nur einige Stichworte genannt. Schaut mal in die Agenda rein, und wenn Ihr Zeit und Lust habt: gleich anmelden!

Januar 2014: Database Application Development
Alles, was Entwickler zur Datenbank wissen müssen

Wie jedes Release bringt auch die neue Datenbankversion 12c jede Menge neuer Funktionen für Anwendungsentwickler mit. So wurden in der Datenbank selbst neue SQL-Funktionen und PL/SQL Packages eingeführt, die für nahezu jeden Entwickler wichtig und hilfreich sind. Aber auch Nutzer spezieller Datentypen wie Texte, Bildern oder Geodaten finden neue Entwicklungen vor.
Es kommen aber nicht nur Neuerungen in der Datenbank - auch für Java-Entwickler auf der Mittelschicht werden völlig neue Möglichkeiten für hochverfügbare Systeme angeboten.
This blog posting is about an Oracle12c event in germany - in german language, and therefore in german only.

Beliebte Postings