WM_CONCAT [message #494660] |
Wed, 16 February 2011 06:56  |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |

|
|
Hi,
Consider the following query
SELECT WM_CONCAT(DISTINCT '"'||ename||'"') E
FROM emp
E
-------
"ADAMS","ALLEN","BLAKE","CLARK","FORD","JACK","JAMES","JONES","KING","MACK","MARTIN","MIKE","MILLER","SCOTT","SMITH","TURNER","WARD"
This is running fine outside a procedure but inside a procedure it says
SQL> CREATE OR REPLACE PROCEDURE test23 IS
2 coll VARCHAR2(100);
3 BEGIN
4 SELECT WM_CONCAT(DISTINCT '"'||ename||'"') E
5 INTO coll
6 FROM emp;
7 END;
8 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE TEST23:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/7 PL/SQL: SQL Statement ignored
4/14 PL/SQL: ORA-30482: DISTINCT option not allowed for this function
Please tell a solution for this?
Regards,
Ritesh
|
|
|
|
Re: WM_CONCAT [message #494667 is a reply to message #494666] |
Wed, 16 February 2011 07:43  |
 |
Michel Cadot
Messages: 68757 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> CREATE OR REPLACE PROCEDURE test23 IS
2 coll VARCHAR2(100);
3 BEGIN
4 select wm_concat('"'||ename||'"')
5 INTO coll
6 from (select distinct ename from emp)
7 ;
8 END;
9 /
Procedure created.
Regards
Michel
|
|
|