Oracle Data Cartridge - Extending the Database javacode 9085791

Oracle Data Cartridge – Extending the Database

Oracle Data Cartridge is a technology component, part of the Oracle RDBMS (ever since the 8i release), that allows developers to extend the functionality of the database. One area of extension lies in the user defined aggregation functions. Another even more important one is the area of Domain Indexes or User Defined Index Types; Oracle Text and Oracle Spatial draw heavily on these user defined index types.

A simple example of the user defined aggregates – User Defined Date Average

The following statement does not work in Oracle SQL:

select avg(hiredate) from emp
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Using the Data Cartridge, we register an Object Type as implementation of a new aggregation function. First we defined the object type; it must implement these functions/methods:

  • ODCIAggregateInitialize – called before the aggregation start
  • ODCIAggregateIterate – called for every record in the aggregation
  • ODCIAggregateTerminate – called to conclude the aggregation and produce the end result
  • ODCIAggregateMerge – called to merge the results from two aggregations; used to parallellize the aggregation process
create type AmisDateAvgImpl as object
(
  count NUMBER, -- the number of dates processed so far
  sumdates NUMBER, -- the sum of all dates; that is: the sum of all (date-sysdate) values
  static function ODCIAggregateInitialize(sctx IN OUT AmisDateAvgImpl)
    return number,
  member function ODCIAggregateIterate(self IN OUT AmisDateAvgImpl,
    value IN date) return number,
  member function ODCIAggregateTerminate(self IN AmisDateAvgImpl,
    returnValue OUT date, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT AmisDateAvgImpl,
    ctx2 IN AmisDateAvgImpl) return number
);
create or replace type body AmisDateAvgImpl is
static function ODCIAggregateInitialize(sctx IN OUT AmisDateAvgImpl)
return number is
begin
  sctx := AmisDateAvgImpl(0, 0);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT AmisDateAvgImpl, value IN date) return number is
begin
  self.count:= self.count+1;
  self.sumdates := self.sumdates + (value - sysdate);
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN AmisDateAvgImpl, returnValue OUT date, flags IN number) return number is
begin
  returnValue := self.sumdates/self.count + sysdate;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT AmisDateAvgImpl, ctx2 IN AmisDateAvgImpl) return number is
begin
  return ODCIConst.Success;
end;
end;
CREATE or replace FUNCTION AmisDateAvg (input Date) RETURN Date
PARALLEL_ENABLE AGGREGATE USING AmisDateAvgImpl;
/

Now we can perform the query using our new aggregation function:

select AmisDateAvg(hiredate) from emp

which in this case is equivalent with:

select sysdate - sum(sysdate - hiredate)/count(hiredate) from emp

For the documentation on Oracle Data Cartridge 10g, see: Data Cartridge Developer’s Guide and Data Cartridge Java API Reference (Javadoc)

2 Comments

  1. Lucas Jellema July 6, 2004
  2. Lucas Jellema July 6, 2004