Aggregate function for geometric mean (GEOMEAN) for Oracle RDBMS

geomean.sql

ALTER SESSION SET plsql_compiler_flags = 'NATIVE';
--
ALTER TYPE  geomean_impl COMPILE;  
--
CREATE FUNCTION geomean( input NUMBER )
  RETURN NUMBER
  PARALLEL_ENABLE
  AGGREGATE USING geomean_impl;

geomean_impl_body.sql

CREATE OR REPLACE TYPE BODY geomean_impl IS
  STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT geomean_impl )
    RETURN NUMBER IS
  BEGIN
    sctx   := geomean_impl( 0, 0 );
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate( self IN OUT geomean_impl, value IN NUMBER )
    RETURN NUMBER IS
  BEGIN
    v_ln_sum      := v_ln_sum + ln( value );
    v_num_items   := v_num_items + 1;

    RETURN odciconst.success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate( self IN geomean_impl, returnvalue OUT NUMBER, flags IN NUMBER )
    RETURN NUMBER IS
  BEGIN
    returnvalue   := exp( v_ln_sum / v_num_items );

    RETURN odciconst.success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge( self IN OUT geomean_impl, ctx2 IN geomean_impl )
    RETURN NUMBER IS
  BEGIN
    self.v_ln_sum      := self.v_ln_sum + ctx2.v_ln_sum;
    self.v_num_items   := self.v_num_items + ctx2.v_num_items;

    RETURN odciconst.success;
  END;
END;
/

geomean_impl_spec.typ

CREATE OR REPLACE TYPE geomean_impl AS OBJECT
/*
 *  Aggregate function to calculate geometric mean values
 * 
 *  Tamas Foldi, Starschema Kft., (C) 2010
 */
 
  ( /** sum of ln(values) */
    v_ln_sum NUMBER
  , /** number of values */
    v_num_items NUMBER
  , 
   STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT geomean_impl )
      RETURN NUMBER
  , MEMBER FUNCTION ODCIAggregateIterate( self IN OUT geomean_impl, value IN NUMBER )
      RETURN NUMBER
  , MEMBER FUNCTION ODCIAggregateTerminate( self IN geomean_impl, returnvalue OUT NUMBER, flags IN NUMBER )
      RETURN NUMBER
  , MEMBER FUNCTION ODCIAggregateMerge( self IN OUT geomean_impl, ctx2 IN geomean_impl )
      RETURN NUMBER
   );