Skip to Main Content

 

Auswahl  

Komplett Übersicht aller Oracle Tipps

LISTAGG Alternative mit CLOB Datentyp (und damit 128TB Maximallänge) 

Oracle
PL/SQL:SQL
RDBMS 10.x:RDBMS 12.x:RDBMS 19.3:RDBMS 21.1
11.01.21 (MP)
04.07.23(MP)
listagg clob

Passende Schulungen zum Thema

Wer kennt es nicht, immer wenn man mal listagg benötigt, reciht der Speicherplatz nicht, weil listagg maximal 32767 Bytes zurückgeben kann.

Mit einem kleinen Trick klappt es auch mit CLOB als Rückggaabetyp. Dazu verwenden wir die XML Funktion XMLELEMENT.
Keine Angst, sie müssen kein XML dazu beherschen:-)

Wir haben das ganze in eine WITH Funktion gekappselt, für den Fall, dass Sie kein CREATE PROCEDURE RECHT haben.
Die Funktion hat bis zu 3 Parameter,
column_name ist der Name der Spalte, die zusammengefasst werden soll
table_name ist der Name der Tabelle, in der die Spalte enthalten ist
where_cond ist Optional und beinhaltet eine Filterklausel Default 1=1 also alles)
order_by Optional kann auch ein Sortierreihenfolge bei der Ausgabe definiert werden (Default: Sortiert nach der Ausgabespalte)
delimiter ist auch Optional und gibt das Trennzeichen zwischen der Ausgabe der Spaltenwerte an (Default ',' )

WITH
FUNCTION listagg_clob (
column_name IN VARCHAR2,
table_name  IN VARCHAR2,
where_cond  IN VARCHAR2 DEFAULT NULL,
order_by    IN VARCHAR2 DEFAULT NULL,
delimiter   IN VARCHAR2 DEFAULT ',')
RETURN CLOB
IS
ret_clob CLOB;
BEGIN
EXECUTE IMMEDIATE q'!select replace(replace(XmlAgg(
                  XmlElement("a", !' || column_name ||')
                  order by ' ||nvl(order_by,'1') ||
                  q'!)
                  .getClobVal(),
              '<a>', ''),
            '</a>','!'|| delimiter ||q'!') as aggname
   from !' || table_name || q'!
  where  !' || nvl(where_cond,' 1=1') INTO ret_clob;
RETURN ret_clob;
END;
SELECT listagg_clob('table_name','all_tables') FROM dual;



Wenn Sie das Ganze als eigenständige Funktion anlegen möchten:

CREATE OR REPLACE FUNCTION listagg_clob (
column_name IN VARCHAR2,
table_name  IN VARCHAR2,
where_cond  IN VARCHAR2 DEFAULT NULL,
order_by    IN VARCHAR2 DEFAULT NULL,
delimiter   IN VARCHAR2 DEFAULT ',')
RETURN CLOB
IS
ret_clob CLOB;
BEGIN
EXECUTE IMMEDIATE q'!select replace(replace(XmlAgg(
                  XmlElement("a", !' || column_name ||')
                  order by ' ||nvl(order_by,'1') ||
                  q'!)
                  .getClobVal(),
              '<a>', ''),
            '</a>','!'|| delimiter ||q'!') as aggname
   from !' || table_name || q'!
  where  !' || nvl(where_cond,' 1=1') INTO ret_clob;
RETURN ret_clob;
END;
/
 


Testcases:

SELECT listagg_clob('table_name','all_tables')
FROM dual;
SELECT listagg_clob('table_name','all_tables',order_by=>'table_name desc nulls last')
FROM dual;

SELECT length(listagg_clob(q'!owner||'.'||table_name!','all_tables', where_cond=>q'!table_name<>'MARCO'!'))
FROM dual;
=>60962

SELECT listagg_clob('table_name','all_tables',order_by=>'table_name desc nulls last', delimiter=>':')
FROM dual;
 


Damit können Sie nun (fast) unbegrenzt die Werte einer Spalte zusammenfassen und weiterverarbeiten.