Archiv

Posts Tagged ‘LISTAGG’

Oracle LISTAGG-Funktion läuft auf Fehler ORA-01489

Die Ursache für den Fehler

SELECT ‚<ul><li>‘ || LISTAGG(text, ‚</li><li>‘) WITHIN GROUP (ORDER BY datum desc) || ‚</li></ul>‘
FROM   test1;

=> ORA-01489: Das Ergebnis der Zeichenfolgenverkettung ist zu lang

bei Verwendung der LISTAGG-Funktion liegt darin, dass Oracle intern den Datentyp VARCHAR2 verwendet und die Länge dieses Datentyps über den init-ora Parameter „MAX_STRING_SIZE“ festgelegt wird. Der Default für diesen Parameter ist „STANDARD“, was bedeutet, dass die Länge des Datentyps VARCHAR2 auf 4000 Zeichen limitiert ist (dieses Limit kennen wir ja schon sehr lange).

Wie kommen wir nun aus dieser Nummer raus?

Lösung

Im Prinzip gibt es aus meiner Sicht 3 mehr oder weniger sinnvolle Lösungen:

  1. Ersetzen der LISTAGG-Funktion durch die XMLAGG-Funktion.
  2. Umsetzen des init-ora Parameters MAX_STRING_SIZE.
  3. Schreiben einer eigenen Aggregat-Funktion.

Leider haben alle 3 Lösungen auch Nachteile, auf die ich kurz eingehen möchte.

zu 1. Ersetzen der LISTAGG-Funktion

Wir können anstelle der LISTAGG-Funktion die Funktion XMLAGG verwenden, wobei hier zu beachten ist, dass ein XML-Format zugrunde gelegt wird und wir dieses noch umwandeln müssen.

SELECT ‚<ul>‘ — nur für spezielle HTML-Formatierung „unordered list“
|| DBMS_XMLGEN.CONVERT
(XMLAGG
(XmlElement(e, ‚<li>’||text||'</li>‘, “).EXTRACT(‚//text()‘)
ORDER BY id desc
).getClobVal() — Clob-Value des XML-Aggregats holen
, 1) — 1 = unescape
|| ‚</ul>‘ — nur für spezielle HTML-Formatierung „unordered list“
FROM   test1;

Prinzipiell sollte das problemlos funktionieren. Leider sind wir nach der Inbetriebnahme dieser Lösung doch wieder auf ähnliche Probleme gestoßen, wie bei der LISTAGG-Funktion (gleicher Fehler, nur andere Situation). Was feststeht ist, dass unsere Fehler aus der Methode „.getClobVal()“ resultierten, allerdings bekomme ich die Fehler mit den Testdaten nicht mehr reproduziert. Es scheint irgendwie mit Umlauten zu tun zu haben (z.B. Stringlänge eines Feldes ist 4000-Zeichen mit CHAR-Semantik und nun sind mehrere Umlaute enthalten, aber auch das bekomme ich nur noch sporadisch reproduziert – also leider nicht aussagefähig).

zu 2. Umsetzen des init-ora Parameters MAX_STRING_SIZE

Diese Lösung wäre eigentlich die perfekte Lösung. Durch Umsetzen dieses Parameters auf „EXTENDED“ wir die maximale Länge für VARCHAR2-Felder auf 32k erhöht und in der Regel sollte das für solche Funktionen ausreichen (wie man das macht, kann man z.B. hier nachlesen Burleson Consulting – max_string_size tips.

Aber Vorsicht! Es gibt einige Risiken, über die man sich vorher Gedanken machen sollte. Wir haben diesen Schritt erst mal nicht durchgeführt, weil wir sofort auf Fehler im SQLDeveloper gelaufen sind (unsere Version des SQLDevelopers kann keine VARCHAR2-Felder mit Inhalt > 4000 verarbeiten) und wir nun befürchten, dass evtl. diverse Schnittstellen, die auf unsere Datenbank zugreifen, auf ähnliche Probleme stoßen könnten.

zu 3. Schreiben einer eigenen Aggregat-Funktion

Diese Lösung haben wir derzeit in Betrieb und funktioniert einwandfrei und ohne Risiken.

Wir haben eine eigene Aggregat-Funktion geschrieben, wie es im Internet auch mehrfach zu finden ist (z.B. hier) und verwenden diesen in unseren Select-Statements.

Neuer Object-Type: T_CLOB_AGGREGATOR

CREATE OR REPLACE TYPE t_clob_aggregator
AS OBJECT
(
aggregate_clob  CLOB,

STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT t_clob_aggregator )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate( self IN OUT t_clob_aggregator, value IN CLOB )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate( self IN t_clob_aggregator, returnValue OUT CLOB, flags IN number )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge( self IN OUT t_clob_aggregator, ctx2 IN t_clob_aggregator )
RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY t_clob_aggregator
IS

STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT t_clob_aggregator )
RETURN NUMBER
IS
temp_clob CLOB;
BEGIN

DBMS_LOB.CREATETEMPORARY( temp_clob, TRUE, DBMS_LOB.CALL );

sctx := t_clob_aggregator( aggregate_clob=>temp_clob );

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateIterate( self IN OUT t_clob_aggregator, value IN CLOB )
RETURN NUMBER
IS
BEGIN
IF DBMS_LOB.GETLENGTH( self.aggregate_clob ) > 0 THEN
DBMS_LOB.APPEND( self.aggregate_clob, ‚#PLCHLD_END#’||’#PLCHLD_START#‘ );
END IF;

DBMS_LOB.APPEND( self.aggregate_clob, value );

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateTerminate( self IN t_clob_aggregator, returnValue OUT CLOB, flags IN number)
RETURN NUMBER
IS
BEGIN

returnValue := ‚#PLCHLD_START#’||self.aggregate_clob||’#PLCHLD_END#‘;

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateMerge( self IN OUT t_clob_aggregator, ctx2 IN t_clob_aggregator )
RETURN NUMBER
IS
BEGIN

DBMS_LOB.APPEND( self.aggregate_clob, ctx2.aggregate_clob );

RETURN ODCIConst.Success;

END;
END;
/

Neue Aggregat-Funktion: CLOBAGG

CREATE OR REPLACE FUNCTION clobagg( input CLOB )
RETURN CLOB
PARALLEL_ENABLE AGGREGATE
USING t_clob_aggregator;
/

Verwendung der neuen Methoden und Funktionen:

SELECT REPLACE(REPLACE(‚<ul>’||clobagg(text)||'</ul>‘, ‚#PLCHLD_START#‘, ‚<li>‘), ‚#PLCHLD_END#‘, ‚</li>‘)
FROM (
SELECT text
FROM   test1
— Die Sortierung muss im inneren Statement erfolgen, damit es funktioniert!
ORDER BY id DESC
);

Wichtiger Hinweis!

Wir verwenden im Object-Type 2 Placeholder-Values, damit wir die Formatierungen später verändern können, wie wir es brauchen. Man könnte das auch beim Select-Statement direkt angeben, wobei man hier unbedingt die TO_CLOB-Konvertierung machen muss, da ansonsten wieder ein VARCHAR2(4000) zugrunde gelegt wird, was unseren Eingangsfehler verursachen würde:

SELECT ‚<ul>’||clobagg(TO_CLOB(‚<li>‘)||text||TO_CLOB(‚</li>‘))||'</ul>‘
FROM (
SELECT text
FROM   test1
— Die Sortierung muss im inneren Statement erfolgen, damit es funktioniert!
ORDER BY id DESC
);

 

Advertisements
Kategorien:Oracle 11g, Oracle 12c Schlagwörter: , ,