SELF.strings := SELF.strings MULTISET UNION ALL ctx2.strings ĬREATE OR REPLACE FUNCTION wm_concat (p_input VARCHAR2) SELECT LISTAGG( column_value, ',' ) WITHIN GROUP ( ORDER BY column_value ) If we go for an inefficient aggregation function that stores all the values in a collection and then calls LISTAGG then we can get closer: CREATE TYPE stringlist IS TABLE OF VARCHAR2(4000) ĬREATE OR REPLACE TYPE t_string_agg AS OBJECTĬREATE OR REPLACE TYPE BODY t_string_agg IS LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS listaggĪs you can see the ordering of the output is different so you can get close but not an exact match. Test Data: CREATE TABLE test_data ( id, value ) AS PARALLEL_ENABLE AGGREGATE USING t_string_agg User-Defined Aggregation Function: CREATE OR REPLACE FUNCTION wm_concat (p_input VARCHAR2) SELF.g_string := SELF.g_string || ctx2.g_string ReturnValue := SUBSTR( SELF.g_string, 2 ) SELF.g_string := self.g_string || ',' || value User-Defined Object Body: CREATE OR REPLACE TYPE BODY t_string_agg IS User-Defined Object: CREATE OR REPLACE TYPE t_string_agg AS OBJECT If you want to redefine WM_CONCAT in later versions then you will probably end up using a user-defined aggregation function: You can see that WM_CONCAT is lacking the ability to specify a delimiter or an ORDER BY clause. You can't implement a custom version of WM_CONCAT in Oracle 18c to behave exactly the same as LISTAGG but you can get close with a user-defined aggregation function. To summarize, what is the proper way to define wm_concat so that wm_concat(m圜olumn) will behave exactly the same as listagg(m圜olumn, ',') in a query? That way, the app can safely continue using wm_concat as normal on both the 10g and 18c databases until all environments are on 18c, after which the app can be swapped to use listagg and the temporary custom wm_concat function can be removed from the 18c databases, completing the migration. My thought is that wm_concat(m圜olumn) in 10g is equivalent to listagg(m圜olumn, ',') in 18c, so I'd like to define wm_concat(m圜olumn) to be a function in the new 18c databases that passes-through to listagg(m圜olumn, ',') behind the scenes and returns the result. Thus, I'm looking for an implementation that will work in both database versions for the time being. One of the incompatibilities uncovered is that WM_Concat is supported in 10g but not 18c, and ListAgg (the new equivalent function) is supported in 18c but not 10g. To complicate things, not all environments are planned to be migrated at once and so the app must support both for a time. We are in the process of migrating from Oracle 10g to 18c for our environment databases.