code:

CREATE OR REPLACE TYPE product_impl AS OBJECT
(
product NUMBER,
 
STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT product_impl) RETURN NUMBER,
 
MEMBER FUNCTION ODCIAggregateIterate(SELF  IN OUT product_impl,
VALUE IN NUMBER) RETURN NUMBER,
 
MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT product_impl,
ctx2 IN product_impl) RETURN NUMBER,
 
MEMBER FUNCTION ODCIAggregateTerminate(SELF        IN OUT product_impl,
returnvalue OUT NUMBER,
flags       IN NUMBER) RETURN NUMBER
)
/
 
CREATE OR REPLACE TYPE BODY product_impl IS
STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT product_impl) RETURN NUMBER IS
BEGIN
ctx := product_impl(1);
RETURN ODCIConst.Success;
END ODCIAggregateInitialize;
 
MEMBER FUNCTION ODCIAggregateIterate(SELF  IN OUT product_impl,
VALUE IN NUMBER) RETURN NUMBER IS
BEGIN
IF VALUE IS NOT NULL THEN
SELF.product := SELF.product * VALUE;
END IF;
RETURN ODCIConst.Success;
END ODCIAggregateIterate;
 
MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT product_impl,
ctx2 IN product_impl) RETURN NUMBER IS
BEGIN
SELF.product := SELF.product * ctx2.product;
RETURN ODCIConst.Success;
END ODCIAggregateMerge;
 
MEMBER FUNCTION ODCIAggregateTerminate(SELF        IN OUT product_impl,
returnvalue OUT NUMBER,
flags       IN NUMBER) RETURN NUMBER IS
BEGIN
returnvalue := SELF.product;
RETURN ODCIConst.Success;
END ODCIAggregateTerminate;
END;
/
 
CREATE OR REPLACE FUNCTION product(x IN NUMBER) RETURN NUMBER
PARALLEL_ENABLE
AGGREGATE USING product_impl;
/
 
DROP TABLE prod_test;
 
CREATE TABLE prod_test(val number);
 
INSERT INTO prod_test VALUES (1);
 
INSERT INTO prod_test VALUES (2);
 
INSERT INTO prod_test VALUES (3);
 
INSERT INTO prod_test VALUES (4);
 
COMMIT;
 
SELECT product(val) FROM prod_test;
 
PRODUCT(VAL)
------------
24