This discussion is archived
3 Replies Latest reply: Aug 25, 2008 10:20 AM by 275989 RSS

collect

Laurent Schneider Oracle ACE
Currently Being Moderated
Hi,

I have noticed the feedback area disappeared in 11gR1 online doc

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions024.htm#i1271564

Is it possible to use distinct or order by?
select  
  deptno, collect(distinct job)
from emp 
group by deptno;
    DEPTNO COLLECT(DISTINCTJOB)
---------- --------------------------------------------------------------
        10 SYSTPTR6rtx0Y8FjgQ59n0o3wWA==('CLERK', 'MANAGER', 'PRESIDENT')
        20 SYSTPTR6rtx0Y8FjgQ59n0o3wWA==('ANALYST', 'CLERK', 'MANAGER')
        30 SYSTPTR6rtx0Y8FjgQ59n0o3wWA==('CLERK', 'MANAGER', 'SALESMAN')

select  
  collect(ename order by ename desc)
from emp ;

COLLECT(ENAMEORDERBYENAMEDESC)
-------------------------------------------------------------------------------------
SYSTPTR6UIgTRILzgQ59n0o0gvA==('WARD', 'TURNER', 'SMITH', 'SCOTT', 'MILLER', 'MARTIN',
 'KING', 'JONES', 'JAMES', 'FORD', 'CLARK', 'BLAKE', 'ALLEN', 'ADAMS')
It seems to work, is it supposed to work?

Regards
Laurent
  • 1. Re: collect
    275989 Newbie
    Currently Being Moderated
    Hi Laurent. I'll ask about the missing feedback section.

    I'll also have to ask about the use of this function. The doc says you need to embed it in a CAST function to get the proper results. Still, if it seems to work, maybe we're missing out on a benefit. I'll try to find out more and let you know.

    Regards,
    Diana
  • 2. Re: collect
    559730 Newbie
    Currently Being Moderated
    Hi,

    I'm using 10g2 and I've found (empirically) the following:

    1. COLLECT (DISTINCT col) works in an SQL statement but not in a PL/SQL cursor.
    2. COLLECT (col ORDER BY col DESC) works in an SQL statement
    3. COLLECT (DISTINCT col ORDER BY col DESC) does not work (the elements are not unique although they are ordered)

    These are not documented Oracle options. Therefore, I recommend using the object-relation operators, e.g.
    CAST (COLLECT (DISTINCT col) AS MY_TABLE_TYPE)
    would be
    SET (CAST (COLLECT (col) AS MY_TABLE_TYPE))
    The queries are roughly with the same efficiency, but the SET operator is documented and works in PL/SQL.

    Greetings,
    Dimitar
  • 3. Re: collect
    275989 Newbie
    Currently Being Moderated
    Hi Laurent. Yes, this was a doc bug that has been fixed in the 11.1.0.7 doc. Both those clauses are permitted.

    Regards,
    Diana