Forum Stats

  • 3,759,507 Users
  • 2,251,556 Discussions
  • 7,870,689 Comments

Discussions

am getting error error when am using wm_concat funcation.

883038
883038 Member Posts: 78
edited Jan 10, 2012 4:08AM in SQL & PL/SQL
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
Tagged:
3415960

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    Accepted Answer
    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

Answers

  • Lokanath Giri
    Lokanath Giri Member Posts: 764 Silver Badge
    edited Jan 10, 2012 2:25AM
    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 १० जनवरी, २०१२ १२:५४ अपराह्न
    Lokanath Giri3415960
  • Jhon
    Jhon Member Posts: 231
    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
    Jhon
  • AlexAnd
    AlexAnd Member Posts: 2,554 Gold Trophy
    >
    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
    AlexAnd
  • cool-2466583
    cool-2466583 Member Posts: 135
    Hi,

    Please check the database version you are using.

    wm_concat is available from 11g Release 2.

    Regards,
    Wilson
    cool-2466583
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    Accepted Answer
    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
This discussion has been closed.