ORA-30482: DISTINCT 选项在此函数中禁用的解决方法

今天在编写存储的时候需要使用到wm_concat这个函数，进行编译的时候提示ORA-30482错误，存储过程大概是如下

create or replace procedure pd_test
( 
out_test_query  out   sys_refcursor
)
is
begin
  open out_test_query for 
  select
    wm_concat(distinct(a.name)) name,
    wm_concat(distinct(a.age)) age,
    wm_concat(distinct(b.school)) school,
    wm_concat(distinct(b.score)) score,
    wm_concat(distinct(b.course)) course
  from
  tb_pro_student a,
  tb_pro_cource b
  where a.name=b.name;
   
end pd_bill_query_para;

编译的时候提示ORA-30482: DISTINCT 选项在此函数中禁用的解决方法

解决方法，修改存储过程如下

create or replace procedure pd_test
(   
out_test_query  out   sys_refcursor
)
is
v_sql       varchar2(1024);
begin
  v_sql:='
  open out_test_query for 
  select
    wm_concat(distinct(a.name)) name,
    wm_concat(distinct(a.age)) age,
    wm_concat(distinct(b.school)) school,
    wm_concat(distinct(b.score)) score,
    wm_concat(distinct(b.course)) course
  from
  tb_pro_student a,
  tb_pro_cource b
  where a.name=b.name';
  
  open out_test_query for v_sql; 
end pd_bill_query_para;

该存储过程即将包含vw_concat的语句打包给v_sql，然后直接使用open for即可，测试编译通过，输出结果正确