Search code examples
sqloracle-databaseoracle10ganalytic-functions

Oracle Collect Over Analytical Function

Is it possible to use the 10g collect command as an analytical function by using OVER PARTITION or some other way?

e.g.

SELECT COLLECT(x) OVER (PARTITION BY y)
FROM table

Every time I try this there is a ora 3113 exception saying 'end-of-file on communication channel'

PS. I know I need to cast the result to make it useful, but for simplicities sake I'm leaving it out for now

Solution

  • COLLECT is not an analytical function, but can be used as a group function.

    SELECT y, COLLECT(x) FROM table GROUP BY y

    (Interesting, though: COLLECT is not listed as being an analytical function, but if I try to use it that way as in your example it appears to work at first, then stops after outputting some of the results and gives ORA-03113 as you said.)