WM_CONCAT with DISTINCT Clause - Compiled Package versus Stand-Alone Query Issue

Question

I was writing some program that uses the WM_CONCAT function. When I run this query:

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

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?

Solution 1

Problem is that WM_CONCAT is stored procedure written on pl/sql.

There is a open bug #9323679: PL/SQL CALLING A USER DEFINED AGGREGRATE FUNCTION WITH DISTINCT FAILS ORA-30482.

Workaround for problems like this is using dynamic 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.

Solution 2

PL/SQL will not let you to use distinct in an aggregated function, and this issue shows that the SQL-engine and the PL/SQL-engine do not use the same parser.

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,

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;