problem max size of oracle's listagg is 4000 characters. I've copied some code from tom kyte, changed some varchars in clobs and now we're happyly concatenatin large strings again.
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,
returnValue OUT CLOB,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER
);
/
SHOW ERROR
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,
returnValue OUT CLOB,
flags IN NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := 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;
/
SHOW ERROR
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERROR
VERPLICHT:
ODCIAggregateInitialize
This function typically initializes the aggregation context (an instance of the implementation object type) and returns it (as an OUT parameter) to Oracle.
ODCIAggregateIterate
The ODCIAggregateIterate function is invoked by Oracle to process the next input row.
The routine is invoked by passing in the aggregation context and the value of the next input to be aggregated. This routine processes the input value,
updates the aggregation context accordingly, and returns the context back to Oracle. This routine is invoked by Oracle for every value in the underlying
group, including NULL values.
ODCIAggregateTerminate
The ODCIAggregateTerminate function is invoked by Oracle as the final step of aggregation.
This routine takes the aggregation context as input and returns the resultant aggregate value to Oracle. This routine also typically performs any necessary
cleanup operations such as freeing memory, and so on.
OPTIONEEL:
ODCIAggregateMerge
The ODCIAggregateMerge function is invoked by Oracle to merge two aggregation contexts into a single object instance.
Two aggregation contexts may need to be merged during either serial or parallel evaluation of the user-defined aggregate. This function takes the two
aggregation contexts as input, merges them, and returns the single, merged instance of the aggregation context.
ODCIAggregateInitialize: This function is called to create a new context for a set of values. The purpose of this static method is to return a new, initialized instance for the type.
ODCIAggregateIterate: Each value is passed to this method which is responsible for making the necessary calculations, etc.
ODCIAggregateMerge: This method is used when two contexts are merged into a single one. Merging happens if the original set of values are iterated using several different contexts.
ODCIAggregateTerminate: When all values have been handled and contexts are merged, this function returns the result.
PROBLEEM......
Aggragatie sorteert numeriek.