Using Oracle Data Cartridge Interface for User Defined Aggragation Functions

create or replace type tq84_text_range_t as object (

    min_val varchar2(4000),
    max_val varchar2(4000),

    static function ODCIAggregateInitialize(sctx  in out tq84_text_range_t) 
                    return number,

    member function ODCIAggregateIterate   (self  in out tq84_text_range_t, 
                                            value in varchar2 ) 
                    return number,

    member function ODCIAggregateTerminate (self         in     tq84_text_range_t   , 
                                            return_value    out varchar2, 
                                            flags        in number      )               
                    return number,

    member function ODCIAggregateMerge(self in out tq84_text_range_t, 
                                       ctx2 in tq84_text_range_t    ) 
                    return number
);
/

create or replace type body tq84_text_range_t is 


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


    member function ODCIAggregateIterate(
        self in out tq84_text_range_t,
        value in varchar2
    ) 
    return number is
    begin

--      if value is null then
--         return ODCIConst.Success;
--      end if;

        if min_val is null then min_val := value; end if;
        if max_val is null then max_val := value; end if;
          
        if value < min_val then min_val := value; end if;
        if value > max_val then max_val := value; end if;

        return ODCIConst.Success;
    end ODCIAggregateIterate;


    member function ODCIAggregateTerminate(
        self         in  tq84_text_range_t, 
        return_value out varchar2,
        flags        in  number
    )
    return number is
    begin
        return_value := min_val || ' .. ' || max_val;
        return ODCIConst.Success;
    end ODCIAggregateTerminate;


    member function ODCIAggregateMerge(
        self in out tq84_text_range_t, 
        ctx2 in     tq84_text_range_t
    ) return number is
    begin

        if ctx2.min_val < self.min_val then self.min_val := ctx2.min_val; end if;
        if ctx2.max_val > self.max_val then self.max_val := ctx2.max_val; end if;

        return ODCIConst.Success;

    end ODCIAggregateMerge;

end;
/

show errors

create or replace function tq84_text_range(
    value varchar2  -- The value to be aggregated per group
) return varchar2
    parallel_enable
    aggregate
using
  tq84_text_range_t;
/

create table tq84_agg_test (
   a number,
   b varchar2(20)
);

insert into tq84_agg_test values (1, 'colporteur');
insert into tq84_agg_test values (2, 'suedehead');
insert into tq84_agg_test values (1, 'skimmington');
insert into tq84_agg_test values (3, 'xenology');
insert into tq84_agg_test values (1, 'deglutition');
insert into tq84_agg_test values (2, 'omophagy');
insert into tq84_agg_test values (3, 'apoptosis');
insert into tq84_agg_test values (1, 'incunabula');
insert into tq84_agg_test values (2, 'rubricate');
insert into tq84_agg_test values (2, 'bergschrund');

column b_conc format a30

select
   a,
   tq84_text_range(b) b_conc
from
   tq84_agg_test 
group by
   a;
--
--         A B_CONC
------------ ------------------------------
--         1 colporteur .. skimmington
--         2 bergschrund .. suedehead
--         3 apoptosis .. xenology

drop table    tq84_agg_test;
drop function tq84_text_range;
drop type     tq84_text_range_t;