User defined aggregate function

Hello,
I would like to write a string aggregation function (that's an idea!). Using an Oracle 9.2, I naturally tried to do that with the ODCIAggregate interface and there were many code samples to do that. However I want to concatenate the string values with a custom string, while all the samples use hard-coded values like ','.

I first thought this separator should be defined in the aggregation context instance, but I did not find any way to specify additional parameters to ODCIAggregateInitialize, or to build the aggregation instance before it is passed to ODCIAggregateInitialize. Is it possible ?

Then I tried to pass the separator to ODCIAggregateIterate calls. Since the function prototypes cannot be changed, I ended creating another type containing a string value to concat and the separator value, passing the whole thing to every Iterate call. I do not think this is really efficient but well I cannot do better. Finally, I needed to add the separator before every string value, *even the first one*, since I cannot store the separator in the aggregation context (well I can, but I have to do it at every iteration), and the separator is not passed during Merge calls.

So, to mimic a perl/python join routine, I ended writing something like:

---

select ltrim(stragg(strtoagg(values, ', ')), ', ') from table;
---

Where stragg is the aggregation function and strtoagg a compound type containing the new value to concat and the separator.

Is there any way to write something like:
---

select stragg(values, ', ') from table;
---

with a bit of efficiency ?

Patrick Mézard Received on Tue Nov 23 2004 - 13:01:48 CST