oracle 带有 DISTINCT 子句的 WM_CONCAT - 编译包与独立查询问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19577257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
WM_CONCAT with DISTINCT Clause - Compiled Package versus Stand-Alone Query Issue
提问by Reimius
I was writing some program that uses the WM_CONCAT function. When I run this query:
我正在编写一些使用 WM_CONCAT 函数的程序。当我运行此查询时：
SELECT WM_CONCAT(DISTINCT employee_id)
FROM employee
WHERE ROWNUM < 20;
It works fine. When I try to compile the relatively same query in a package function or procedure, it produces this error: PL/SQL: ORA-30482: DISTINCT option not allowed for this function
它工作正常。当我尝试在包函数或过程中编译相对相同的查询时，它会产生以下错误：PL/SQL: ORA-30482: DISTINCT option not allowed for this function
FUNCTION fetch_raw_data_by_range
RETURN VARCHAR2 IS
v_some_string VARCHAR2(32000);
BEGIN
SELECT WM_CONCAT(DISTINCT employee_id)
INTO v_some_string
FROM employee
WHERE ROWNUM < 20;
RETURN v_some_string;
END;
I realize WM_CONCAT is not officially supported, but can someone explain why it would work as a stand alone query with DISTINCT, but not compile in a package?
我意识到 WM_CONCAT 不受官方支持，但有人可以解释为什么它可以作为 DISTINCT 的独立查询工作，但不能在包中编译吗？
回答by Nagh
Problem is that WM_CONCAT is stored procedure written on pl/sql.
问题是 WM_CONCAT 是写在 pl/sql 上的存储过程。
There is a open bug #9323679: PL/SQL CALLING A USER DEFINED AGGREGRATE FUNCTION WITH DISTINCT FAILS ORA-30482.
有一个未解决的错误 #9323679：PL/SQL 调用具有不同的用户定义的聚合函数失败 ORA-30482。
Workaround for problems like this is using dynamic sql.
解决此类问题的方法是使用动态 sql。
So if you wrap your query in
因此，如果您将查询包装在
EXECUTE IMMEDIATE '<your_query>';
Then it should work.
那么它应该工作。
But as OldProgrammer has suggested already, you better avoid using this WM_CONCAT at all.
但是正如 OldProgrammer 已经建议的那样，您最好完全避免使用此 WM_CONCAT。
回答by Dba
PL/SQL will not let you to use distinctin an aggregated function, and this issue shows that the SQL-engine and the PL/SQL-engine do not use the same parser.
PL/SQL 不会让你distinct在聚合函数中使用，这个问题表明 SQL-engine 和 PL/SQL-engine 没有使用相同的解析器。
One of the solutions to this problem is to use sub query as below,
此问题的解决方案之一是使用子查询如下，
SELECT WM_CONCAT(employee_id)
INTO v_some_string
FROM (select DISTINCT employee_id
FROM employee)
WHERE ROWNUM < 20;
Another solution is to use dynamic SQL as Nagh suggested,
另一种解决方案是使用 Nagh 建议的动态 SQL，
FUNCTION fetch_raw_data_by_range
RETURN VARCHAR2 IS
v_some_string VARCHAR2(32000);
v_sql VARCHAR2(200);
BEGIN
v_sql :='SELECT WM_CONCAT(DISTINCT employee_id)
FROM employee
WHERE ROWNUM < 20';
execute immediate v_sql INTO v_some_string;
RETURN v_some_string;
END;
