Do-it-yourself implementation of any_value

diy_any_value.sql

-- ------------------------------------------------------------------------------------------------
-- do-it-yourself implementation of any_value for strings 
-- based on http://db-oriented.com/2021/02/20/diy-any_value/
-- use Oracle Database 9i or higher, connected as HR user.
-- ------------------------------------------------------------------------------------------------

create or replace type any_value_string_t as object
(
   v_value varchar2(4000),
   static function odciaggregateinitialize(sctx in out any_value_string_t) return number,
   member function odciaggregateiterate(
      self  in out any_value_string_t,
      value in     varchar2
   ) return number,
   member function odciaggregatemerge(
      self in out any_value_string_t,
      ctx2 in     any_value_string_t
   ) return number,
   member function odciaggregateterminate(
      self        in  any_value_string_t,
      returnvalue out varchar2,
      flags       in  number
   ) return number
);
/

create or replace type body any_value_string_t as
   static function odciaggregateinitialize(sctx in out any_value_string_t) return number is
   begin
      sctx := any_value_string_t(null);
      return odciconst.success;
   end;

   member function odciaggregateiterate(
      self  in out any_value_string_t,
      value in     varchar2
   ) return number is
   begin
      if self.v_value is null then
         self.v_value := value;
      end if;
      return odciconst.success;
   end;

   member function odciaggregateterminate(
      self        in  any_value_string_t,
      returnvalue out varchar2,
      flags       in  number
   ) return number is
   begin
      returnvalue := self.v_value;
      return odciconst.success;
   end;

   member function odciaggregatemerge(
      self in out any_value_string_t,
      ctx2 in     any_value_string_t
   ) return number is
   begin
      if self.v_value is null then
         self.v_value := ctx2.v_value;
      end if;
      return odciconst.success;
   end;
end;
/

create or replace function diy_any_value(p_value varchar2) return varchar2
   parallel_enable
   aggregate using any_value_string_t;
/

select d.department_id,
       diy_any_value(d.department_name) department_name,
       count(*) number_of_employees
  from employees e,
       departments d
 where d.department_id = e.department_id
 group by d.department_id;
 
-- ------------------------------------------------------------------------------------------------
-- do-it-yourself implementation of any_value for numbers
-- ------------------------------------------------------------------------------------------------

create or replace type any_value_number_t as object
(
   v_value varchar2(4000),
   static function odciaggregateinitialize(sctx in out any_value_number_t) return number,
   member function odciaggregateiterate(
      self  in out any_value_number_t,
      value in     number
   ) return number,
   member function odciaggregatemerge(
      self in out any_value_number_t,
      ctx2 in     any_value_number_t
   ) return number,
   member function odciaggregateterminate(
      self        in  any_value_number_t,
      returnvalue out number,
      flags       in  number
   ) return number
);
/

create or replace type body any_value_number_t as
   static function odciaggregateinitialize(sctx in out any_value_number_t) return number is
   begin
      sctx := any_value_number_t(null);
      return odciconst.success;
   end;

   member function odciaggregateiterate(
      self  in out any_value_number_t,
      value in     number
   ) return number is
   begin
      if self.v_value is null then
         self.v_value := value;
      end if;
      return odciconst.success;
   end;

   member function odciaggregateterminate(
      self        in  any_value_number_t,
      returnvalue out number,
      flags       in  number
   ) return number is
   begin
      returnvalue := self.v_value;
      return odciconst.success;
   end;

   member function odciaggregatemerge(
      self in out any_value_number_t,
      ctx2 in     any_value_number_t
   ) return number is
   begin
      if self.v_value is null then
         self.v_value := ctx2.v_value;
      end if;
      return odciconst.success;
   end;
end;
/

-- overwrites existing function, no overloading possible for standalone functions
create or replace function diy_any_value(p_value number) return number
   parallel_enable
   aggregate using any_value_number_t;
/

select d.department_name,
       diy_any_value(d.department_id) department_id,
       count(*) number_of_employees
  from employees e,
       departments d
 where d.department_id = e.department_id
 group by d.department_name;

-- ------------------------------------------------------------------------------------------------
-- using package instead of standalone functions?
-- ------------------------------------------------------------------------------------------------

create or replace package diy is
   function any_value(p_value varchar2) return varchar2
      parallel_enable
      aggregate using any_value_string_t;

   function any_value(p_value number) return number
      parallel_enable
      aggregate using any_value_number_t;
end diy;
/

-- throws ORA-00979: not a GROUP BY expression in 19c/21c
-- throws ORA-06553: PLS-801: internal error [1419] in 9iR2
select d.department_id,
       diy.any_value(d.department_name) department_name,
       count(*) number_of_employees
  from employees e,
       departments d
 where d.department_id = e.department_id
 group by d.department_id;

-- throws ORA-00979: not a GROUP BY expression in 19c/21c
-- throws ORA-06553: PLS-801: internal error [1419] in 9iR2
select d.department_name,
       diy.any_value(d.department_id) department_id,
       count(*) number_of_employees
  from employees e,
       departments d
 where d.department_id = e.department_id
 group by d.department_name;

Thanks Philipp!
BTW, why "use Oracle 19c or higher"? I created it originally for pre-19c versions.
Oren.

@DBoriented thanks for the hint. I've updated the comment. The script should run in any version >= 9i. I've run it in 9.2.0.8, 19c and 21c.