关于wm_concat函数的奇怪问题

[robin_ares]

一个查询语句我这样写的： wm_concat(distinct 字段)
这个语句直接执行是好用的，但是放到存储过程中，CURSOR CUR_MAIN IS 这样的写法中，
就会报ORA-30482: DISTINCT option not allowed for this function的错误，编译不通过

这事为什么呢

[ANN521029]

楼主
你能不能先告诉我wm_concat这个函数的用法啊

[YY_MM_DD]

去这里看看吧..

ERROR at line 2:
ORA-30482: DISTINCT option not allowed for this function


This doesn't work and it is known as bug #3263979
We apply the workaround from the bug document


http://www.orafaq.com/forum/t/58508/2/

[robin_ares]

引用 1 楼 ann521029 的回复:

楼主
你能不能先告诉我wm_concat这个函数的用法啊

table
a b c
a b c1
a b c2

select a,b,wm_concat(c)
from table
group by a,b

结果
a b c1,c2

[小灰狼W]

我也试了，确实不行..

引用http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1058232381458#11384357177468:

Ok, got it working as SQL. My select now looks like this:

SELECT JOB_ID,
STRAGG (DISTINCT LOCATION),
STRAGG (DISTINCT AREA),
STRAGG (DISTINCT DISTRICT)
FROM T
GROUP BY JOB_ID.

Problem I have is that this does not seem to work inside a PL/SQL Block. I get the error

ORA-06550: line 15, column 47:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 12, column 4:
PL/SQL: SQL Statement ignored

Is there a different way to do the distinct? I thought maybe inside the ODCIAGGREGATEMERGE
function, but I could not figure out how.

Thanks.





Followup July 28, 2003 - 10am Central time zone:

Interesting, looks like a "product issue" to me. use a dynamically opened ref cursor as a
temporary solution:

variable x refcursor
declare
l_cursor sys_refcursor;
begin
open l_cursor for '
SELECT JOB_ID,
STRAGG (DISTINCT LOCATION) l,
STRAGG (DISTINCT AREA) a,
STRAGG (DISTINCT DISTRICT) d
FROM T
GROUP BY JOB_ID';

:x := l_cursor;
end;
/

print x


(please file an issue with support as well!)

或者使用execute immeidate,动态解决

[小灰狼W]

引用 4 楼 wildwave 的回复:

我也试了，确实不行..
引用http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1058232381458#11384357177468:Ok, got it working as SQL. My select now looks like this: SELECT JOB_ID, STRAGG (DISTINCT LOCATION), STRAGG (DISTINCT AREA), STRAGG (DISTINCT DISTRICT) FROM T GROUP BY JOB_ID. Problem I have is that this does not seem to work inside a PL/SQL Block. I get the error ORA-06550: line 15, column 47: PL/SQL: ORA-30482: DISTINCT option not allowed for this function ORA-06550: line 12, column 4: PL/SQL: SQL Statement ignored Is there a different way to do the distinct? I thought maybe inside the ODCIAGGREGATEMERGE function, but I could not figure out how. Thanks. Followup July 28, 2003 - 10am Central time zone: Interesting, looks like a "product issue" to me. use a dynamically opened ref cursor as a temporary solution: variable x refcursor declare l_cursor sys_refcursor; begin open l_cursor for ' SELECT JOB_ID, STRAGG (DISTINCT LOCATION) l, STRAGG (DISTINCT AREA) a, STRAGG (DISTINCT DISTRICT) d FROM T GROUP BY JOB_ID'; :x := l_cursor; end; / print x (please file an issue with support as well!)
或者使用execute immeidate,动态解决

Ok, got it working as SQL.  My select now looks like this:

SELECT  JOB_ID, 
    STRAGG (DISTINCT LOCATION), 
    STRAGG (DISTINCT AREA),
        STRAGG (DISTINCT DISTRICT)
FROM T
GROUP BY JOB_ID.

Problem I have is that this does not seem to work inside a PL/SQL Block.  I get the error

ORA-06550: line 15, column 47:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 12, column 4:
PL/SQL: SQL Statement ignored

Is there a different way to do the distinct?  I thought maybe inside the ODCIAGGREGATEMERGE 
function, but I could not figure out how.

Thanks.

 



Followup   July 28, 2003 - 10am Central time zone:

Interesting, looks like a "product issue" to me.  use a dynamically opened ref cursor as a 
temporary solution:

variable x refcursor
declare
    l_cursor sys_refcursor;
begin
    open l_cursor for '
SELECT  JOB_ID,
        STRAGG (DISTINCT LOCATION) l,
        STRAGG (DISTINCT AREA) a,
        STRAGG (DISTINCT DISTRICT) d
   FROM T
  GROUP BY JOB_ID';

  :x := l_cursor;
end;
/

print x


(please file an issue with support as well!)

[tangren]

用子查询先去重再wm_concat吧

[小灰狼W]

没明白为什么会出现这种情况
pl/sql与sql执行机制不同？

[robin_ares]

引用 6 楼 tangren 的回复:

用子查询先去重再wm_concat吧

是呀，目前只能先这样了

[ANN521029]

太高深了，看不明白啊

[mgan]

也可以先建立一个VIEW 然后去调用VIEW