PL/SQL Collect Function

I am trying to use the collect function in pl/sql and keep getting data type errors.

err: ORA-00932: inconsistent datatypes: expected NUMBER got - 00932. 00000 -  "inconsistent datatypes: expected %s got %s"

our current database version:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production

I need to try and avoid using stored procedures due to my limited db access.

Any ideas? Thank you


--all of these queries return data type errors

--number data type error listed above
SELECT setid, collect(manager_id) as manager
from dept_tbl
group by setid;

--number data type error listed above
SELECT setid, cast(collect(manager_id) as number(7,2)) as manager
from ps_dept_tbl
group by setid;

--number data type error listed above
SELECT setid, cast(collect(cast(manager_id as number(7,2))) as number(7,2)) as manager
from ps_dept_tbl
group by setid;

Open in new window

GreenLeghornAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
sdstuberConnect With a Mentor Commented:
you can also do it with xml functions (even though the data isn't xml)

this isn't as efficient as the options above, but it doesn't require construction of extra objects



select setid, RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", manager_id|| ',')), '/x/text()').getstringval(),',')
from dept_tbl
group by setid
0
 
slightwv (䄆 Netminder) Commented:
What does your collect function look like?
0
 
sdstuberCommented:
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
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

SEE THE WINNERS
 
sdstuberCommented:
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?
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
GreenLeghornAuthor Commented:
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?
0
 
sdstuberCommented:
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;
0
 
sdstuberCommented:
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;
0
 
GreenLeghornAuthor Commented:
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.
0
 
GreenLeghornAuthor Commented:
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"
0
 
sdstuberCommented:
change

getstringval

to

getclobval


that means you must have a lot of values for some of them
0
 
GreenLeghornAuthor Commented:
that worked! thank you so much. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.