Oracle Data Cartridge – Extending the Database

Lucas Jellema 3
0 0
Read Time:2 Minute, 3 Second

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)

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

3 thoughts on “Oracle Data Cartridge – Extending the Database

  1. […]  Furthermore, it is not ideal we need this rather specialized function in the first place. We could use a user defined aggregation function – see our blog article Oracle Data Cartridge – Extending the Database. In an Oracle 10g database, we should be able to leverage the COLLECT aggregator (see for example Oracle-Base on String Aggregation techniques). The COLLECT can be used to aggregate multiple records into a single Collection type. For example to aggregate Strings we would use an Oracle Type, defined like this:CREATE OR REPLACE TYPE STRING_TABLE AS TABLE OF VARCHAR2(4000)/ We can the rewrite the query, using COLLECT and this new type definition: The output is not yet ideally formatted: the Entity Description is returned as a STRING_TABLE. We would now like to turn the STRING_TABLE into a concattenated string. For this, we need another PL/SQL Function. So what have we gained you might ask? Well, it is totally different function: a very generic one that does not do any database access and can be reused in many situations. This function looks like: And the over all query is now: […]

  2. To get rid of the user defined aggregate function:
    1) drop type
    2) drop function

    for example:

    drop type AmisDateAvgImpl;
    drop function AmisDateAvg;

  3. Another example of using the Oracle Data Cartridge:

    Select the sum of a VARCHAR2 column, resulting in a comma-separated list of the individual values in this column.


    select Amisvarchar2Sum(ename) from emp

    AMISVARCHAR2SUM(ENAME)
    --------------------------------------------------------------------------------
    SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARkson,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,
    MILLER,you


    select d.dname
    , count(d.dname)
    , Amisvarchar2Sum(e.ename)
    from dept d , emp e
    where e.deptno = d.deptno
    group by d.dname

    DNAME COUNT(D.DNAME)
    -------------- --------------
    AMISVARCHAR2SUM(E.ENAME)
    --------------------------------------------------
    ACCOUNTING 2
    CLARkson,MILLER

    RESEARCH 5
    SMITH,ADAMS,JONES,FORD,SCOTT

    SALES 6
    ALLEN,TURNER,BLAKE,WARD,MARTIN,JAMES

    This aggregate was implemented as follows:

    create type AmisVarchar2SumImpl as object
    (
    sumvarchar2s varchar2(32000),
    static function ODCIAggregateInitialize(sctx IN OUT AmisVarchar2SumImpl)
    return number,
    member function ODCIAggregateIterate(self IN OUT AmisVarchar2SumImpl,
    value IN varchar2) return number,
    member function ODCIAggregateTerminate(self IN AmisVarchar2SumImpl,
    returnValue OUT varchar2, flags IN number) return number,
    member function ODCIAggregateMerge(self IN OUT AmisVarchar2SumImpl,
    ctx2 IN AmisVarchar2SumImpl) return number
    );

    create or replace type body AmisVarchar2SumImpl is
    static function ODCIAggregateInitialize(sctx IN OUT AmisVarchar2SumImpl)
    return number is
    begin
    sctx := AmisVarchar2SumImpl(”);
    return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self IN OUT AmisVarchar2SumImpl, value IN varchar2) return number is
    begin

    self.sumvarchar2s := self.sumvarchar2s||’,’||value;
    return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self IN AmisVarchar2SumImpl, returnValue OUT varchar2, flags IN number) return number is
    begin
    returnValue := substr(self.sumvarchar2s,2);
    return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self IN OUT AmisVarchar2SumImpl, ctx2 IN AmisVarchar2SumImpl) return number is
    begin
    self.sumvarchar2s := self.sumvarchar2s||ctx2.sumvarchar2s;
    return ODCIConst.Success;
    end;
    end;
    /

    CREATE or replace FUNCTION Amisvarchar2SUm (input varchar2) RETURN varchar2
    PARALLEL_ENABLE AGGREGATE USING AmisVarchar2SumImpl;
    /

Comments are closed.

Next Post

Configure sendmail to forward all mails

Go to /etc/mail/ and look in the sendmail.mc file. Uncomment and change the entry define(`SMART_HOST',`forward.host.domain'). Additional info: The sendmail.cf is generally considered to be too difficult to be changed by hand. Therefore a set of macros have been defined to ease this task. In order to be able to use […]
%d bloggers like this: