COLLECT and DISTINCT

In regular SQL, you can use the DISTINCT qualifier with the COLLECT function.
Here it removes the duplicates for PETER and DAVID.

SQL> select cast(collect(first_name) as  sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(FIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Peter', 'David', 'Peter', 'Christopher', 'Nanette', 'Oliver'
, 'Clara', 'Danielle', 'Mattea', 'David', 'Sundar', 'Amit')


SQL>
SQL> select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(DISTINCTFIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Amit', 'Christopher', 'Clara', 'Danielle', 'David', 'Mattea'
, 'Nanette', 'Oliver', 'Peter', 'Sundar')

However, if you get into PL/SQL, things are different. Without the DISTINCT, it works fine.


SQL> declare
  2    v_tab sys.dbms_debug_vc2coll;
  3  begin
  4    select cast(collect(first_name) as  sys.dbms_debug_vc2coll)
  5    into v_tab
  6    from HR.employees
  7    where job_id = 'SA_REP'
  8    and manager_id in (147,145);
  9    dbms_output.put_line(v_tab.count);
 10  end;
 11  /
12

With the DISTINCT an error is reported.


SQL> declare
  2    v_tab sys.dbms_debug_vc2coll;
  3  begin
  4    select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
  5    into v_tab
  6    from HR.employees
  7    where job_id = 'SA_REP'
  8    and manager_id in (147,145);
  9  end;
 10  /
  select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

That's the case in 11.2.0.2. I'm not sure if there's a patch for it, or whether it will be fixed in 12c. [And if they want to get that out in the twelfth month of 2012, they don't have long left.]