ORA-30482: DISTINCT option not allowed for this function

Learnt something newly yesterday, thought of sharing it here.

I want to concatenate a column from multiple rows but i want only the DISTINCT values. so I did wm_concat(column_name) it works great.

rajesh@11GR2> select deptno, wm_concat(distinct ename) as enames
  2  from scott.emp
  3  group by deptno;

 DEPTNO ENAMES
------- --------------------------------------------------
     10 CLARK,KING,MILLER
     20 ADAMS,FORD,JONES,SCOTT,SMITH
     30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Elapsed: 00:00:00.03
rajesh@11GR2>

When i put this SQL inside a Pl/SQL block it ended up with error.

rajesh@11GR2>
rajesh@11GR2> begin
  2     insert into t(x,y)
  3     select deptno, wm_concat(distinct ename) as enames
  4     from scott.emp
  5     group by deptno;
  6  end;
  7  /
        select deptno, wm_concat(distinct ename) as enames
                       *
ERROR at line 3:
ORA-06550: line 3, column 17:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 2, column 2:
PL/SQL: SQL Statement ignored

Elapsed: 00:00:00.06
rajesh@11GR2>

The Solution to this problem is to use Dynamic SQL

rajesh@11GR2> declare
  2     v_sql varchar2(500);
  3  begin
  4     v_sql := 'insert into t(x,y)
  5     select deptno, wm_concat(distinct ename) as enames
  6     from scott.emp
  7     group by deptno ' ;
  8
  9     execute immediate v_sql;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> select * from t;
         X Y
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Elapsed: 00:00:00.03
rajesh@11GR2>
rajesh@11GR2>

Another solution is to use VIEW and hide the construct from Pl/SQL

rajesh@11GR2> truncate table t;

Table truncated.

Elapsed: 00:00:00.15
rajesh@11GR2>
rajesh@11GR2> select * from t;

no rows selected

Elapsed: 00:00:00.01
rajesh@11GR2> create or replace view v
  2  as
  3  select deptno, wm_concat(distinct ename) as enames
  4  from emp
  5  group by deptno;

View created.

Elapsed: 00:00:00.12
rajesh@11GR2>
rajesh@11GR2> begin
  2     insert into t(x,y)
  3     select deptno,enames
  4     from v;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> select * from t;

         X Y
---------- -----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Elapsed: 00:00:00.03
rajesh@11GR2>
rajesh@11GR2>