Issue with Oracle user-defined aggregate functions

Question

I'm having this annoying issue with aggregate udfs where it always fails to invoke the iteration method. My code is just like the other examples around the web (including the ones on oracle docs and asktom). I tried to change the UDF type and the same happens everytime. It says:

ORA-29925: cannot execute DBAODB.WMCONCATROUTINES.ODCIAGGREGATEITERATE
ORA-06553: PLS-306: wrong number or types of arguments in call to 'ODCIAGGREGATEITERATE'

Oracle version is 11.1.0.7.0 and Here's my code:

CREATE OR REPLACE TYPE WmConcatRoutines as object (
    tmpData number,

    STATIC FUNCTION ODCIAggregateInitialize( wmInst IN OUT WmConcatRoutines) RETURN number,
    MEMBER FUNCTION ODCIAggregateIterate(wmInst IN OUT WmConcatRoutines, value number) return number,
    MEMBER FUNCTION ODCIAggregateMerge(wmInst IN OUT WmConcatRoutines, wmInst2 IN WmConcatRoutines) return number,
    MEMBER FUNCTION ODCIAggregateTerminate(wmInst IN WmConcatRoutines, returnValue OUT number, flags IN number) return number
);
/

CREATE OR REPLACE TYPE BODY WmConcatRoutines IS 
    STATIC FUNCTION ODCIAggregateInitialize( wmInst IN OUT WmConcatRoutines) RETURN number IS
    BEGIN
        wmInst := WmConcatRoutines(0);
        return ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(wmInst IN OUT WmConcatRoutines, value number) return number IS
    BEGIN
        wmInst.tmpData := wmInst.tmpData + value;
        return ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(wmInst IN WmConcatRoutines, returnValue OUT number, flags IN number) return number IS
    BEGIN
        returnValue := wmInst.tmpData;
        return ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(wmInst IN OUT WmConcatRoutines, wmInst2 IN WmConcatRoutines) return number IS
    BEGIN
        wmInst.tmpData := wmInst.tmpData + wmInst2.tmpData;
        return ODCIConst.Success;
    END;
END;
/

CREATE OR REPLACE FUNCTION WM_CONCAT_test (input number) RETURN number 
    parallel_enable 
    AGGREGATE USING WmConcatRoutines;
/

Any ideas on what may be causing this? thanks in advance

I was trying to implement a function that works just like WM_concat... so I thought it had something to do with the type being varchar2, then I changed everything to number... It's almost identical to the sample in oracle documentation. I tested this in the latest oracle express version also (that's the one that doesn't have the wm_concat function) — alessandroasm
– alessandroasm, Commented Jan 31, 2012 at 17:52
just thought of a better example: select wm_concat_test(1) from dual — alessandroasm
– alessandroasm, Commented Jan 31, 2012 at 18:00

Alex Poole · Accepted Answer · 2012-01-31 18:13:27Z

3

The parameter names in your implementations of the ODCI functions have to match those in the documentation; so you have to use sctx, self, and ctx2 rather than wminst and wminst2.

Edit As APC briefly mentioned, it only seems to be self that is required; you can use something else in place of sctx and ctx2. I can't find a documentation reference...

Edit 2 Yes, I can... APC's reference to it being an object thing led me to this.

edited Jan 31, 2012 at 18:13

answered Jan 31, 2012 at 18:06

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

2 Comments

alessandroasm Over a year ago

yep, that was it... just changed everything but the one in Initialize to self and worked just fine :) thanks for the quick reply

Alex Poole Over a year ago

@alessandroasm - glad to help, and always a bonus to learn something new on the way.

Collectives™ on Stack Overflow

Issue with Oracle user-defined aggregate functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related