Inhaltsverzeichnis
Ein Listen von Werten in SQL erstellen
11g Lösung mit listagg
Ab 11g kann das gut mit einer ANA Funktion gelöst werden.
SQL>SELECT deptno , listagg(ename,':') WITHIN GROUP (ORDER BY ename) AS emp_list FROM emp GROUP BY deptno / DEPTNO EMP_LIST ---------- --------------------------------- 10 CLARK:KING:MILLER 20 ADAMS:JONES 30 ALLEN:MARTIN:TURNER:WARD
Mit listagg kann eine Liste bis maximal 4000 Zeichen aggregiert werden.
Quelle, siehe http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm und http://www.oracle-developer.net/display.php?id=515
Ab Oracle 12r2 - LISTAGG overflow
Ab der Version 12c R2 kann auf den ärgerlichen „ORA-01489: result of string concatenation is too long“ Fehler reagiert werden.
SQL>SELECT deptno , listagg(ename,':') WITHIN GROUP (ORDER BY ename) ON overflow TRUNCATE '...' WITH COUNT AS emp_list FROM emp GROUP BY deptno / DEPTNO EMP_LIST ---------- --------------------------------- 10 CLARK:KING:MILLER 20 ADAMS:JONES 30 ALLEN:MARTIN:TURNER:WARDxxxxx ... (100)
Ab Oracle 19- LISTAGG distinct
Ab 19c können auch Duplicate in der Liste aussortiert werden!
siehe auch ⇒ https://oracle-base.com/articles/19c/listagg-distinct-19c
Eigene Aggregat Funktion verwenden um einen ORA-01489 zu vermeiden
Eine Alternative Lösung ist eine eigene Aggregate Funktion.
siehe dazu ⇒ Eine eigene Aggregat Funktion erstellen
SYS.STRAGG verwenden
Die SYS Funktion STRAGG kann auch verwendet werden, allerdings wird hier der zuvor ermittelte Wert aller Zielen jeweils pro Zeile mit ausgegeben.
Die Funktion scheint all Input Variable einfach zuvor zu summieren, unabhängig von der aktuellen Zeile.
SELECT deptno , sys.stragg(ename||';') AS emp_list FROM scott.emp WHERE deptno=10 GROUP BY deptno / DEPTNO EMP_LIST ------------ -------------------------------------------------- 10 CLARK;KING;MILLER; -- Bei mehr als einer Treffer Zeile wird das Ergebnis mehrdeutig! SELECT deptno , sys.stragg(ename||';') AS emp_list FROM scott.emp WHERE deptno IN (10,20) GROUP BY deptno / DEPTNO EMP_LIST ------------ -------------------------------------------------- 10 CLARK;KING;ADAMS;FORD;MILLER; 20 CLARK;KING;ADAMS;FORD;MILLER;
D.h. ein Einsatz ist eher zu vermeiden, zumal das keine offizielle Funktion ist.
Die Collect Methode
Collect Methode:
SQL>SELECT deptno , collect(ename) AS emp_list FROM emp GROUP BY deptno / DEPTNO EMP_LIST ------ ---------------------------------------------------------------------- 10 SYSTPFOeGQC1UQMGz4nUc6QoSaw==('CLARK', 'MILLER', 'KING') 20 SYSTPFOeGQC1UQMGz4nUc6QoSaw==('JONES', 'ADAMS') 30 SYSTPFOeGQC1UQMGz4nUc6QoSaw==('ALLEN', 'TURNER', 'MARTIN', 'WARD')
Die wmsys.wm_concat Funktion
Die wmsys.wm_concat:
SQL>SELECT deptno,wmsys.wm_concat(ename) AS emp_list FROM emp GROUP BY deptno; DEPTNO EMP_LIST ------ ---------------------------------------------------------------------- 10 CLARK,MILLER,KING 20 JONES,ADAMS 30 ALLEN,TURNER,MARTIN,WARD
XML verwenden
Daten in XML verwandeln und wieder in Text zerlegen:
SQL>SELECT deptno , rtrim ( xmlagg (xmlelement (c, ename || ',') ORDER BY ename).extract ('//text()'), ',' ) AS AS emp_list FROM emp GROUP BY deptno; DEPTNO EMP_LIST ------ ---------------------------------------------------------------------- 10 CLARK,MILLER,KING 20 JONES,ADAMS 30 ALLEN,TURNER,MARTIN,WARD
in PL/SQL eine Liste wieder zerlegen
Mit Hilfe von dbms_utility.comma_to_table lässt sich eine Liste in ein Array transformieren.
Beispiel:
DECLARE v_tab_length BINARY_INTEGER; v_s_array DBMS_UTILITY.lname_array; BEGIN v_list:='A,B,C,D,E,F'; -- split servicenames in a table dbms_utility.comma_to_table( list => v_list , tablen => v_tab_length , tab => v_s_array); .... END; /
siehe auch: Oracle PL/SQL - Strings zerlegen
Eine Liste in Ergebniszeilen einer Abfrage umwandeln
Mit sys.dbms_debug_vc2coll (siehe Das DBMS_DEBUG Package) kann eine Ergebnisliste wieder in Zeilen ausgegeben werden.
Beispiel:
SELECT * FROM TABLE(sys.dbms_debug_vc2coll('a','b','c','d','e')); COLUMN_VALUE -------------- a b c d e
Kann zum Beispiel für eine Pivot Abfrage verwendet werden.
Gleiches kann auch mit XMLTABLE und einem XMLTYPE erreicht werden:
SELECT * FROM xmltable ('/emails/email' passing xmltype ('<emails><email><m>12345@12345.ru</m></email><email><m>6789@67890.com</m></email></emails>') COLUMNS "mail" varchar2 (255) path 'm' ); mail ----- 12345@12345.ru 6789@67890.com
SYS.ODCINUMBERLIST
Min seit 10g siehe ⇒Database Data Cartridge Developer's Guide https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcicnstnts.htm
SELECT * FROM TABLE ( SYS.ODCINUMBERLIST(1,2,3,4,5,6,1,2,3,4,5) ) T ; COLUMN_VALUE ------------ 1 2 3 4 ....
Alternativ ODCIDATELIST,ODCIVARCHAR2LIST
Nach möglichen Kollections suchen:
SQL> DESC sys.odcinumberlist sys.odcinumberlist VARRAY(32767) OF NUMBER SQL> DESC sys.odcivarchar2list sys.odcivarchar2list VARRAY(32767) OF VARCHAR2(4000) SQL> DESC sys.ODCIDATELIST sys.ODCIDATELIST VARRAY(32767) OF DATE SQL> DESC sys.dbms_debug_vc2coll sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000) SELECT type_name , owner FROM all_types WHERE typecode = 'COLLECTION' AND owner != USER /