HI,

Create Table



when I use WM_CONCAT(distinct column_Name) , It work fine in Query ,
But IF i write same Statement in Stored Procedure then It giving Error .


Error: PL/SQL: ORA-30482: DISTINCT option not allowed for this function


CREATE TABLE city( city_name VARCHAR(20));

INSERT INTO city(city_name) VALUES ('ABC');

INSERT INTO city(city_name) VALUES ('PQR');

INSERT INTO city(city_name) VALUES ('XYZ');

INSERT INTO city(city_name) VALUES ('ABC');


4 row inserted


SELECT city_name FROM city;

CITY_NAME
--------------------
ABC
PQR
XYZ
ABC


Select WM_CONCAT( city_name) AS city_name FROM city;

CITY_NAME
-----------------------------------------------------
ABC,PQR,XYZ,ABC




-- This is not Work in SP

Select WM_CONCAT( DISTINCT city_name) AS city_name FROM city;

CITY_NAME
-----------------------------------------------------
ABC,PQR,XYZ


__________________________________________________ _______________________________________
Stroed Procedure Is AS(This is only Example)
__________________________________________________ _______________________________________


create or replace
PROCEDURE "TEST_CONCAT"
(
OUT_RESULTSET1 OUT SYS_REFCURSOR
)

AS
BEGIN
OPEN OUT_RESULTSET1 FOR
Select WM_CONCAT( DISTINCT city_name) AS city_name FROM city; -- NOT Work

/* Select WM_CONCAT( city_name) AS city_name FROM city;*/ -- This Work

END TEST_CONCAT;

What release of the database are you running? WM_CONCAT is an undocumented, unsupported function and not being able to use distinct in a procedure is a known problem. Depending on your version, there are alternatives that work fine.

Mr.Vivek,

I fully agree with beilstwh concerning the usage of unsupported, undocumented functions in productin environments.

But, just for the science: