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.