According to the 11g documentation for COLLECT this function supports DISTINCT and ORDER BY in an SQL setting, however, they do not appear to work together. Here is a working example (I am specifically interested in the first SELECT). My question is: is this a bug in COLLECT or a gap in the documentation? To me it clearly reads that DISTINCT and ORDER BY are supported together INSIDE the COLLECT function.
DROP TABLE t;
DROP TYPE number_table_t;
CREATE TABLE t (n NUMBER);
INSERT INTO t
SELECT MOD(LEVEL,
10)
FROM dual
CONNECT BY LEVEL <= 1000;
CREATE OR REPLACE TYPE number_table_t AS TABLE OF NUMBER;
-- this orders, but does not distinct
SELECT CAST(COLLECT(DISTINCT n ORDER BY n) AS number_table_t)
FROM t;
-- this distincts, but doesn't explicitly order
SELECT CAST(COLLECT(DISTINCT n) AS number_table_t)
FROM t;
-- this works
SELECT CAST(COLLECT(DISTINCT n) AS number_table_t)
FROM t
ORDER BY n;