一个查询语句我这样写的: wm_concat(distinct 字段)
这个语句直接执行是好用的,但是放到存储过程中,CURSOR CUR_MAIN IS 这样的写法中,
就会报ORA-30482: DISTINCT option not allowed for this function的错误,编译不通过这事为什么呢
这个语句直接执行是好用的,但是放到存储过程中,CURSOR CUR_MAIN IS 这样的写法中,
就会报ORA-30482: DISTINCT option not allowed for this function的错误,编译不通过这事为什么呢
你能不能先告诉我wm_concat这个函数的用法啊
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/
a b c
a b c1
a b c2select a,b,wm_concat(c)
from table
group by a,b结果
a b c1,c2
或者使用execute immeidate,动态解决
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 errorORA-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 ignoredIs 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!)
pl/sql与sql执行机制不同?