Saturday, August 16, 2008

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


5 comments:

fortune8 said...

I don't see any difference betweenn COLLECT and collect_to_string function. Am I missing something?

John Minkjan said...

Hi,

The difference that collect is native 10G and should be a little bit faster then the original stragg function.

regards

John

Vish said...

Thank you for posting this, this was the only solution that worked for me for aggregating rows of strings. Kudos to your resourcefulness!

Rajesh said...

I already had this solution working for a couple of months but was not able to get proper sorting. Your post helped get the sort fixed. Thanks a lot for this.

Tony Dunsworth said...

Is there a way to order the string collected by another field? I have run this and it will aggregate the strings for me, but it brings them back out of order.