CODEWITHSUNDEEP
oracle-databaseplsqlaggregateuser-defined-functionsora-06553
alessandroasm
alessandroasm

Reputation: 282

Issue with Oracle user-defined aggregate functions

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

Upvotes: 3

Views: 1730

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

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.

Upvotes: 3

Related Questions