3 Replies Latest reply: Aug 25, 2008 12:20 PM by 275989 RSS

    collect

    Laurent Schneider
      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
          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
            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
              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