Trying to write custom aggregation function in Oracle

Question

Basically I'm re-writing some stuff I've already found around the internet and StackOverflow.

My aim is to write a custom aggregate function (in Oracle) as COLLECT is inadequate (I'm dealing with very large strings 4000 characters +), I don't have enough confidence in WM_CONCAT, and not even sure if it would be adequate.

It looks like a custom aggregate function is the best solution I have for building a JSON string of data from nested sub queries, building up the object, and so I need to be able to specify the delimiter between values - to put this into context.

The current ORACLE SQL I have is this:

CREATE OR REPLACE TYPE parms AS TABLE OF CLOB;

CREATE OR REPLACE TYPE string_agg_TYPE AS OBJECT
(
    total CLOB,
    l_delimiter CLOB,

    STATIC FUNCTION
        ODCIAggregateInitialize(sctx IN OUT string_agg_TYPE )
        RETURN NUMBER,

    MEMBER FUNCTION
        ODCIAggregateIterate(self IN OUT string_agg_TYPE ,
                           value IN parms )
        RETURN NUMBER,

    MEMBER FUNCTION
        ODCIAggregateTerminate(self IN string_agg_TYPE,
                             RETURNValue OUT  CLOB,
                             flags IN NUMBER)
        RETURN NUMBER,

    MEMBER FUNCTION
        ODCIAggregateMerge(self IN OUT string_agg_TYPE,
                         ctx2 IN string_agg_TYPE)
        RETURN NUMBER
);

CREATE OR REPLACE TYPE body string_agg_TYPE
IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_TYPE)
RETURN NUMBER
IS
BEGIN
    sctx := string_agg_TYPE( NULL );
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_agg_TYPE,
                                   value IN parms )
RETURN NUMBER
IS
BEGIN
    IF (value.count = 2)
    THEN
        self.l_delimiter := value(2);
ELSE
  self.l_delimiter := ',';
    END IF;
    self.total := self.total || self.l_delimiter || value(1);
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN string_agg_TYPE,
                                     RETURNValue OUT CLOB,
                                     flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
    RETURNValue := ltrim(self.total,self.l_delimiter);
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT string_agg_TYPE,
                                 ctx2 IN string_agg_TYPE)
RETURN NUMBER
IS
BEGIN
    self.total := self.total || ctx2.total;
    RETURN ODCIConst.Success;
END;

END;

When I try to compile the body definition, Oracle gives me the following error:

Error(8,11): PLS-00306: wrong number or types of arguments in call to 'STRING_AGG_TYPE'

So, anything obvious I've missed?

Thanks for the advice, should it be offered.

Nick Krasnov · Accepted Answer · 2013-08-12 12:24:55Z

2

The object type string_agg_TYPE has two properties: total and l_delimiter, there is no reason for the latter to be of CLOB data type by the way, so, initializing the instance of that object data type you should pass in two initializing values for those properties in the constructor:

sctx := string_agg_TYPE(NULL, NULL);

answered Aug 12, 2013 at 12:24

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AndyPC Over a year ago

Funnily enough, I just found this out just now. Thanks anyway.

Collectives™ on Stack Overflow

Trying to write custom aggregation function in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related