create or replace function get_max_version
( value1 in varchar2,
value2 in varchar2
) return varchar2 is
v_num1 int;
v_num2 int;
v_pos int;
v_value1 varchar2(2000) ;
v_value2 varchar2(2000) ;
begin
v_value1 := value1;
v_value2 := value2;
if value1 is null or value1 = '' then
return value2;
elsif value2 is null or value2 = '' then
return value1;
else
while (length(v_value1) > 0 and length(v_value2) > 0) loop
v_pos := instr(v_value1, '.', 1, 1);
if v_pos > 0 then
v_num1 := to_number(substr(v_value1, 0, v_pos-1)) ;
v_value1 := substr(v_value1, v_pos+1);
else
v_num1 := to_number(v_value1);
v_value1 := '';
end if;
v_pos := instr(v_value2, '.', 1, 1);
if v_pos > 0 then
v_num2 := to_number(substr(v_value2, 0, v_pos-1)) ;
v_value2 := substr(v_value2, v_pos+1);
else
v_num2 := to_number(v_value2);
v_value2 := '';
end if;
if v_num1 > v_num2 then
return value1;
elsif v_num1 < v_num2 then
return value2;
end if;
end loop;
if length(v_value1) = 0 then
return value2;
else
return value1;
end if;
end if;
end;
/
create or replace type toversion as object
(
max_version varchar2(200),
static function ODCIAggregateInitialize
( actx in out toversion
) return number,
member function ODCIAggregateIterate
( self in out toversion,
value in varchar2
) return number,
member function ODCIAggregateTerminate
( self in out toversion,
returnValue out varchar2,
flags in varchar2
) return number,
member function ODCIAggregateMerge
( self in out toversion,
ctx2 in toversion
) return number
);
/
create or replace type body toversion as
static function ODCIAggregateInitialize
( actx in out toversion
) return number is
begin
actx := toversion('');
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate
( self in out toversion,
returnValue out varchar2,
flags in varchar2
) return number is
begin
returnValue := self.max_version;
return ODCIConst.Success;
end;
member function ODCIAggregateIterate
( self in out toversion,
value in varchar2
) return number is
begin
if self.max_version is null then
self.max_version := value;
else
self.max_version := get_max_version(self.max_version, value);
end if;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge
( self in out toversion,
ctx2 in toversion
) return number is
begin
self.max_version := get_max_version(self.max_version, ctx2.max_version);
return ODCIConst.Success;
end;
end;
/
create or replace function max_version
( x varchar2) return varchar2
parallel_enable
aggregate using toversion;
/