PL/SQL Collect Function

What does your collect function look like?

the collect function returns a collection (a nested table) of your elements

define your own collection type


create type NUMTABLE as table of number;

then cast your COLLECT results to be of that type

I'm not sure what you're really trying to do with the COLLECT function though.


once you have a set of numbers for each SETID, is that really what you want?

Did not know collect was a built-in function.  I bib a quick look before I posted and missed it somehow.

Please ignore my post.

here's what i want and i don't know if i should be using a collect function. i have setid (or department number) and a manager id (2 columns). there can be multiple managers for each department.

record example: setid column = dept001155, manager_id column = '0011', '0023', '15238'

so i want to partition by the setid and then for each set id show a concatenated string of all of the manager_id's associated with that setid.

does that make sense?

you can use collect for that, but you need another function along with it.

because you can't use the collection itself "as is"

try this...

create type vcarray as table of varchar2(4000);


CREATE OR REPLACE FUNCTION tbl2str(p_tbl IN vcarray, p_delimiter IN VARCHAR2 DEFAULT ',' )
    RETURN VARCHAR2
    DETERMINISTIC
IS
    v_str VARCHAR2(32767);
BEGIN
    IF p_tbl.COUNT > 0
    THEN
        v_str := p_tbl(1);

        FOR i IN 2 .. p_tbl.COUNT
        LOOP
            v_str := v_str || p_delimiter || p_tbl(i);
        END LOOP;
    END IF;

    RETURN v_str;
END;
/


SELECT setid, tbl2str(cast(collect(manager_id) as vcarray)) as manager
from dept_tbl
group by setid;

note,  if you upgrade to 11gR2  you can use list_agg built in to do all of that for you


SELECT setid, listagg(manager_id,',') within group (order by manager_id) as manager
from dept_tbl
group by setid;

thanks sdstuber! i have insufficient privileges right now to create types but i will ask someone to help me out and will try out that snippet. i appreciate your help and will get back to you.

i get this ugly error when i run that query, "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.XMLTYPE", line 169"

change

getstringval

to

getclobval


that means you must have a lot of values for some of them

that worked! thank you so much. :)