Solved

PL/SQL Collect Function

Posted on 2011-09-19
12
1,481 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
  • 6
  • 4
  • 2
12 Comments
 
LVL 76

Expert Comment

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

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 73

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
 
LVL 76

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

Accepted Solution

by:
sdstuber earned 125 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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now