Oracle: Difference between wm_concat and ListAgg

1. wm_concat is undocumented and unsupported by oracle, thus rendering production systems unsupported. listagg is documented and supported by oracle.

2. wm_concat allows distinct option. listagg does not allows it.
    select wm_concat(distinct ename) as enames from emp;

3. listagg allows to decide string concat order. wm_concat does not allows it.
    select listagg(ename,',') within group(order by empno desc) as enames from emp;

4. listagg allows to decide delimiter. wm_concat does not allows it.
    select listagg(ename,'***') within group(order by empno desc) as enames from emp;

5. wm_concat allows to be used olap function with order by listagg does not allows it. listagg allows only olap function without order by.
    select deptno,wm_concat(ename) over(order by deptno) as enames from emp;

6. wm_concat allows to be used keep. listagg does not allows it.
    select wm_concat(ename) keep(dense_rank first order by deptno) as enames from emp;

Original Post
http://forums.oracle.com/forums/thread.jspa?threadID=1088158

Related Links:

Oracle: New String Aggregation Techniques
http://nimishgarg.blogspot.com/2010/02/oracle-new-string-aggregation.html

Oracle: Difference between wm_concat and ListAgg
http://nimishgarg.blogspot.com/2010/07/oracle-differece-between-wmconcat-and.html

Oracle: Nth Record from Comma Separated String
http://nimishgarg.blogspot.com/2010/06/oracle-nth-record-from-comma-seprated.html

Oracle: Comma Separated String To Rows 11g
http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows.html

Oracle: Comma Separated String To Rows 10g Onwards
http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows_16.html

Oracle: Department wise Employee Ids Comma Separated (Analytical Functions )
http://nimishgarg.blogspot.com/2010/02/oracle-department-wise-employee-ids.html

Oracle: Comma Separated String To Rows
http://nimishgarg.blogspot.com/2009/12/oracle-sql-use-comma-seprated-string-to.html

6 comments:

  1. Listagg is a new function that has been added in Oracle 11g for the sting aggregation. You have shared a whole list of differences between these two functions. Thanks for sharing this useful post.

    ReplyDelete
    Replies
    1. Also check related links for other related informations

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Its really a usefulinformation. Thanks. But cold you please tell us more on what does "unsupported by Oracle" means? That function is introduced by Oracle itself right ?

    ReplyDelete
    Replies
    1. WM_CONCAT is undocumented and unsupported by Oracle, thus rendering production systems unsupported. LISTAGG is documented and supported by Oracle.

      Moreover an Oracle SR is not entertained, if they come to know that you are using unsupported features.

      Delete
  4. Si necesitan esa función, encontré esto en una página y lo probé y funciona muy bien:

    Luego cambién el wm_concat() de su select por stragg().

    Saludos,

    Nacho V.

    15/04/2013 10:46:53
    ===================
    * No existe la funcion wm_concat() en mi Oracle, la utilizaba antes del upgrade para poder concatenar los valores de los ...
    Se reemplaza por estas funciones y tipos:

    CREATE OR REPLACE TYPE string_agg_type AS OBJECT
    (
    total VARCHAR2(4000),

    STATIC FUNCTION
    ODCIAggregateInitialize(sctx IN OUT string_agg_type )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateIterate(self IN OUT string_agg_type ,
    value IN VARCHAR2 )
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateTerminate(self IN string_agg_type,
    returnValue OUT varchar2,
    flags IN number)
    RETURN NUMBER,

    MEMBER FUNCTION
    ODCIAggregateMerge(self IN OUT string_agg_type,
    ctx2 IN string_agg_type)
    RETURN NUMBER
    );
    /

    Type created.

    SQL> CREATE OR REPLACE TYPE BODY string_agg_type
    IS

    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
    RETURN NUMBER
    IS
    BEGIN
    sctx := string_agg_type( null );
    RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type,
    value IN varchar2 )
    RETURN NUMBER
    IS
    BEGIN
    self.total := self.total || ',' || value;
    RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(self IN string_agg_type,
    returnValue OUT varchar2,
    flags IN number)
    RETURN NUMBER
    IS
    BEGIN
    returnValue := ltrim(self.total,',');
    return ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type,
    ctx2 IN string_agg_type)
    RETURN NUMBER
    IS
    BEGIN
    self.total := self.total || ctx2.total;
    return ODCIConst.Success;
    END;

    end;
    /

    Type body created.

    SQL> CREATE or replace FUNCTION stragg(input VARCHAR2)
    RETURN varchar2
    PARALLEL_ENABLE AGGREGATE USING string_agg_type;
    /

    Function created.

    ReplyDelete