Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PL/SQL Collect Function

Posted on 2011-09-19
12
Medium Priority
?
1,529 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:GreenLeghorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36562630
What does your collect function look like?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36562702
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36562708
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36562713
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
 

Author Comment

by:GreenLeghorn
ID: 36562890
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36562915
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36562933
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
 

Author Comment

by:GreenLeghorn
ID: 36562935
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36562943
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
 

Author Comment

by:GreenLeghorn
ID: 36563007
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36563045
change

getstringval

to

getclobval


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

Author Comment

by:GreenLeghorn
ID: 36563240
that worked! thank you so much. :)
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question