am getting error error when am using wm_concat funcation.

883038

hi all
am getting error error when am using wm_concat funcation.

SQL> DECLARE
2 ex VARCHAR2(200);
3 BEGIN
4 SELECT wm_concat(ename) INTO EX FROM EMP WHERE deptno=30;
5 DBMS_OUTPUT.PUT_LINE(EX);
6 END;
7 
8 .
SQL> 
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06550: line 4, column 10:
PL/SQL: ORA-00904: "WM_CONCAT": invalid identifier
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

Thanks for all in advance

Edited by: maddy on 9 Jan, 2012 10:02 PM

BluShadow

Cool wrote:
Hi,

Please check the database version you are using.

wm_concat is available from 11g Release 2.

Incorrect.

wm_concat is undocumented and shouldn't be used.

Even Tom Kyte says so... 9967699

From 11gR2, there is a new documented function called LISTAGG...

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm

Lokanath Giri

wm_concat is un-documented hence not supported by oracle.
Try some alternative

select 
rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
from 
emp
where deptno=30

Some good example available check this.
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

Edited by: Lokanath Giri on १० जनवरी, २०१२ १२:५४ अपराह्न

Jhon

correct name of ur Function:

SELECT lengt(ename) FROM EMP WHERE deptno=10;
ORA-00904: "LENGT": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 8 Column: 7

SELECT length(ename) FROM EMP WHERE deptno=10;
it return rows sucessfully

AlexAnd

>
WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems.
>
so change your way

btw

for me

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> set serveroutput on
SQL> DECLARE
  2   ex varchar2(200);
  3   BEGIN
  4   SELECT wm_concat(ename) INTO EX FROM EMP WHERE deptno=30;
  5   DBMS_OUTPUT.PUT_LINE(EX);
  6   END;
  7  /
ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

PL/SQL procedure successfully completed.

SQL> 

try wmsys.wm_concat

cool-2466583

Hi,

Please check the database version you are using.

wm_concat is available from 11g Release 2.

Regards,
Wilson

BluShadow

Cool wrote:
Hi,

Please check the database version you are using.

wm_concat is available from 11g Release 2.

Incorrect.

wm_concat is undocumented and shouldn't be used.

Even Tom Kyte says so... 9967699

From 11gR2, there is a new documented function called LISTAGG...

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm