Define "WM_CONCAT(col)" as "LISTAGG(col, ',')" for Oracle Migration
We are in the process of migrating from Oracle 10g to 18c for our environment databases. To complicate things, not all environments are planned to be migrated at once and so the app must support both for a time. 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. Thus, I'm looking for an implementation that will work in both database versions for the time being.
My thought is that wm_concat(myColumn) in 10g is equivalent to listagg(myColumn, ',') in 18c, so I'd like to define wm_concat(myColumn) to be a function in the new 18c databases that passes-through to listagg(myColumn, ',') behind the scenes and returns the result. 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.
To summarize, what is the proper way to define wm_concat so that wm_concat(myColumn) will behave exactly the same as listagg(myColumn, ',') in a query?
[TL;DR] 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.
WM_CONCAT has the syntax:
WM_CONCAT( expr )
You can see that WM_CONCAT is lacking the ability to specify a delimiter or an ORDER BY clause.
If you want to redefine WM_CONCAT in later versions then you will probably end up using a user-defined aggregation function:
User-Defined Object:
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
);
/
User-Defined Object Body:
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 := SUBSTR( SELF.g_string, 2 );
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;
/
User-Defined Aggregation Function:
CREATE OR REPLACE FUNCTION wm_concat (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
Test Data:
CREATE TABLE test_data ( id, value ) AS
SELECT 1, 'C' FROM DUAL UNION ALL
SELECT 1, 'A' FROM DUAL UNION ALL
SELECT 1, 'B' FROM DUAL UNION ALL
SELECT 2, 'D' FROM DUAL UNION ALL
SELECT 2, 'E' FROM DUAL;
Test Query:
SELECT id,
wm_concat( value ) AS wm_concat,
LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS listagg
FROM test_data
GROUP BY id;
Output:
ID | WM_CONCAT | LISTAGG -: | :-------- | :------ 1 | C,B,A | C,A,B 2 | D,E | D,E
As you can see the ordering of the output is different; so you can get close but not an exact match.
db<>fiddle here
Update:
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);
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
strings stringlist,
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( stringlist() );
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT t_string_agg,
value IN VARCHAR2
) RETURN NUMBER
IS
BEGIN
SELF.strings.EXTEND;
SELF.strings( SELF.strings.COUNT ) := value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(
self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER
) RETURN NUMBER
IS
BEGIN
SELECT LISTAGG( column_value, ',' ) WITHIN GROUP ( ORDER BY column_value )
INTO returnValue
FROM TABLE( SELF.strings );
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(
self IN OUT t_string_agg,
ctx2 IN t_string_agg
) RETURN NUMBER
IS
BEGIN
SELF.strings := SELF.strings MULTISET UNION ALL ctx2.strings;
RETURN ODCIConst.Success;
END;
END;
/
CREATE OR REPLACE FUNCTION wm_concat (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
Then:
SELECT id,
wm_concat( value ) AS wm_concat,
LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY value ) AS listagg
FROM test_data
GROUP BY id;
outputs:
ID | WM_CONCAT | LISTAGG -: | :-------- | :------ 1 | A,B,C | A,B,C 2 | D,E | D,E
It will give the same output as LISTAGG if (and only if) you want to sort the values alphabetically; you can't specify a different ordering. It also requires a context switch from PL/SQL to SQL to preform the aggregation in the final step so it is likely to be slower than a pure PL/SQL aggregation function and it will keep the collection in memory and keep extending it so there will be additional overheads as the collection grows (or gets merged in parallel systems) that will slow it further.
So it's still not LISTAGG but its about as close as you can get if you are willing to put up with issues around performance.
db<>fiddle here