PLSQL aggregation function using type object PARALLEL_ENABLE AGGREGATE

280 Views Asked by At

I have question. Already i have avg_new function, which include nulls (as 0) in the result. I have start from linkedin link. Code:

select avg(a),avg_new(nvl(a,-9999)) from
(select 'test' h, 2 a from dual
union all
select 'test' h, null a from dual
union all
select 'test' h, 2 a from dual
union all
select 'test' h ,2 a from dual)

the results are 2; 1,5

I would like to extend avg_new function by adding denominator parameter ex.:

avg_new(nvl(a,-9999),10)

Te result should be then 0.6

Default value of the parameter would be null, then function works as previous example. If the parameter would be >0 then I would divide sum of 'a' by value of this parameter. How i could do this? I would like to pass this parameter to used type object and to perform further calculations there. Is it possible?

create or replace FUNCTION avg_new (input NUMBER , denominator NUMBER DEFAULT NULL) RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING T_avg_new;

Right now the type could proper read only the first parameter. After adding i have errors:

ORA-29925: cannot execute T_avg_new.ODCIAGGREGATEINITIALIZE

ORA-06553: PLS-306: wrong number or types of arguments in call to "ODCIAGGREGATEINITIALIZE"

  1. 00000 - "cannot execute %s"

*Cause: The specified function does not exist or does not have an appropriate signature.

*Action: Implement the function with the appropriate signature.

0

There are 0 best solutions below