ORACLE Collect Function

Up to 9i you had to use Tom Kyte's Stragg function to get the children of the level to a single string. From 10GR1 you can use the collection function. Based on this article: http://www.oracle-developer.net/display.php?id=306 I played around with this function.

First make our own table type:

CREATE OR REPLACE TYPE collect_table AS TABLE OF VARCHAR2 (4000);

SELECT deptno, CAST (COLLECT (distinct ename) AS collect_table) AS emps
FROM emp
GROUP BY deptno;

Now make a collection to string conversion function:

CREATE OR REPLACE FUNCTION collect_to_string (
nt_in IN collect_table,
delimiter_in IN VARCHAR2 DEFAULT ','
)
RETURN VARCHAR2
IS
v_idx PLS_INTEGER;
v_str VARCHAR2 (32767);
v_dlm VARCHAR2 (10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL
LOOP
v_str := v_str v_dlm nt_in (v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT (v_idx);
END LOOP;
RETURN v_str;
END collect_to_string;

SELECT deptno,
collect_to_string (CAST (COLLECT (distinct ename order by ename desc)
AS collect_table) ,',')AS emps
FROM emp
GROUP BY deptno;

Till Next Time