Solved

PL/SQL Collect Function

Posted on 2011-09-19
12
1,499 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 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
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 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 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 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

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.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

820 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