ORA-00904: “WM_CONCAT”: invalid identifier

I’ve never run into wm_concat before when using Oracle, but I saw it in a statement similar to this earlier today:

SELECT WM_CONCAT(id)
FROM chris.item
WHERE item_type LIKE '%floppy%';

Of course, it didn’t work:

ORA-00904: "WM_CONCAT": invalid identifier

It looks like WM_CONCAT isn’t a supported command (Error when using wm_concat function), and possibly the code I was debugging stopped working when the database was upgraded to 12c.

WM_CONCAT is intended to return the results as a comma-separated list. A strange idea, but it this particular case it almost made sense. A supported replacement that aggregates results into a single string is LISTAGG.

SELECT LISTAGG(id, ',')
WITHIN GROUP (ORDER BY id)
FROM chris.item
WHERE item_type LIKE '%floppy%'
GROUP BY 1;

In this case, I did not want the results separated into groups, so I used GROUP BY 1 to put all results in the same group.

There are a number of other string aggregation techniques for Oracle, if you ever find yourself with such a need.