create type list_t is varray (10) of number;
/

create or replace type medagg_t as object (

    list_count number,
    list_agg list_t,

    static function ODCIAggregateInitialize(sctx in out medagg_t) 
        return number,
    member function ODCIAggregateIterate(self in out medagg_t, 
        value in number) return number,
    member function ODCIAggregateTerminate(self in medagg_t, 
        return_value out number, flags in number) return number,
    member function ODCIAggregateMerge(self in out medagg_t, 
        ctx2 in medagg_t) return number
);
/

create or replace type body medagg_t is 

    static function ODCIAggregateInitialize(sctx in out medagg_t) 
        return number is 
    begin
        sctx := medagg_t(0, null);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(
      self in out medagg_t, value in number) 
        return number is
      insert_pt number;
    begin
      if list_agg is null then
        list_agg := list_t(9999999,9999999,9999999,9999999,9999999,
                           9999999,9999999,9999999,9999999,9999999);
        list_agg(1) := value;
        list_count := 1;
        return ODCIConst.Success;
      end if;

      insert_pt := 11;
      for itemno in 1 .. 10
      loop
        if value < list_agg(itemno) then
          insert_pt := itemno;
          exit;
        end if;
      end loop;

      if insert_pt = 11 then
        return ODCIConst.Success;
      end if;

      if insert_pt = 10 then
        list_agg(10) := value;
        return ODCIConst.Success;
      end if;

     for itemno in reverse insert_pt .. 9
     loop
       list_agg(itemno + 1) := list_agg(itemno);
     end loop;

      list_agg(insert_pt) := value;
      return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self in medagg_t, 
        return_value out number, flags in number) return number is
    begin
      if list_agg is null then
        return_value := 9999;
      else
        return_value := list_agg(5);
      end if;
      return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self in out medagg_t, 
        ctx2 in medagg_t) return number is
    begin
        return ODCIConst.Success;
    end;
end;
/

create or replace function agg_median (input number) return number
    parallel_enable aggregate using medagg_t;
/