Is there another command for wm_concat in oracle 9i?

Question

I have a table containing dept id, employee names and date of joining. I want to get a list of all the employees who joined on a given day in a given dept.

wm_concat is not working.

What version of Oracle? Can you show your query and the *exact* error message? — Mat, Mar 15 '13 at 06:02
I have Oracle 9i. I have not figured out how to do this in 9i. — user2172699, Mar 15 '13 at 06:07
See [**Oracle String Aggregation Techniques**](https://lalitkumarb.wordpress.com/2015/10/12/oracle-string-aggregation-techniques/) and [**Why not use WM_CONCAT function in Oracle?**](https://lalitkumarb.wordpress.com/2015/04/29/why-not-use-wm_concat-function-in-oracle/) — Lalit Kumar B, Apr 10 '16 at 11:13

score 2 · Answer 1 · edited Mar 15 '13 at 07:54

2

According to this, WM_CONCAT is not supported.

WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications

You can use a user defined aggregate function described in that link.

edited Mar 15 '13 at 07:54

Ben

51,770
36
127
149

answered Mar 15 '13 at 06:05

Iswanto San

18,263
13
58
79

score 1 · Answer 2 · answered Mar 15 '13 at 06:05

1

Try wmsys.wm_concat (10g+) or listagg (11g+)

answered Mar 15 '13 at 06:05

Egor Skriptunoff

23,359
2
34
64

I have Oracle 9i. Can you please suggest something for that? – user2172699 Mar 15 '13 at 06:07

peterm · Answer 3 · 2013-03-15T06:57:19.793

1

Try sys.STRAGG

SELECT department_id, 
       hire_date, 
       TRIM(TRAILING ',' FROM sys.STRAGG(last_name || ',')) names
FROM employees
GROUP BY department_id, hire_date

SQLFiddle

edited Mar 15 '13 at 06:57

answered Mar 15 '13 at 06:06

peterm

91,357
15
148
157

This is trimming the results, in some cases this could be not acceptable – Michal Kordas Oct 13 '21 at 13:04

score 1 · Answer 4 · edited May 23 '17 at 11:58

See the String Aggregation Techniques page.

For 9i, you can declare a UDAG function as in the page cited:

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

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

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

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

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



CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/



CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/

And using it is like:

SELECT deptno, string_agg(ename) AS employees
FROM   emp
GROUP BY deptno;

You can also try the method from this question(which is slow if you have big table)

Art · Answer 5 · 2013-03-15T13:08:15.497

0

Look here: http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

Look for example of ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i on that page.

If PL/SQL is an option then TABLE_TO_COMMA Procedures may work: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_util.htm#ARPLS73271

edited Mar 15 '13 at 13:08

answered Mar 15 '13 at 12:49

Art

5,616
1
20
22

score 0 · Answer 6 · answered Apr 10 '16 at 11:16

First of all, do not use WM_CONCAT since it is an undocumented feature and it has been removed from the latest 12c version. Any application which has had been relying on wm_concat function will not work once upgraded to 12c. See Why not use WM_CONCAT function in Oracle?

SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT object_name
  2  FROM dba_objects
  3  WHERE owner='WMSYS'
  4  AND object_name LIKE 'WM\_%' ESCAPE '\';

OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES

20 rows selected.

You will receive an “invalid identifier” error:

SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
               *
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier

Therefore, there is no point relying on an undocumented feature which is no more made available in latest versions.

There are various string aggregation techniques:

LISTAGG in 11gR2 and up

For example,

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in 9i and up

For example,

SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

User-defined aggregate function STRAGG described in AskTom.
COLLECT function in 10g and up

A few good examples by Tim Hall here.

Is there another command for wm_concat in oracle 9i?

6 Answers6

Linked