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.