Home » SQL & PL/SQL » SQL & PL/SQL » WM_CONCAT (Oracle, 10g, Windows server 2003)
- WM_CONCAT [message #494660] Wed, 16 February 2011 06:56 Go to next message
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 #494666 is a reply to message #494660] Wed, 16 February 2011 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select wm_concat('"'||ename||'"')
from (select distinct ename from emp)
/

Regards
Michel

[Updated on: Wed, 16 February 2011 07:42]

Report message to a moderator

- Re: WM_CONCAT [message #494667 is a reply to message #494666] Wed, 16 February 2011 07:43 Go to previous message
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
Previous Topic: ORA-01410: invalid ROWID
Next Topic: Updating multiple ROWS with different values using single statement
Goto Forum:
  


Current Time: Tue Apr 29 19:58:24 CDT 2025