22 User-Defined Aggregates Interface

This chapter describes the routines that need to be implemented to define a user-defined aggregate function. The routines are implemented as methods in an object type. Then the CREATE FUNCTION statement is used to actually create the aggregate function.

This chapter contains the following topic:

Aggregate Routines

ODCIAggregateInitialize

Invoked by Oracle as the first step of aggregation. This function typically initializes the aggregation context (an instance of the implementation object type) and returns it (as an OUT parameter) to Oracle.

Syntax

STATIC FUNCTION ODCIAggregateInitialize(
   actx IN OUT <impltype>) 
RETURN NUMBER

Table 22-1 ODCIAggregateInitialize Parameters

Parameter	Meaning
actx (IN OUT)	The aggregation context that is initialized by the routine. Its value will be null for regular aggregation cases. In aggregation over windows, `actx` is the context of the previous window. This object instance is passed in as a parameter to the next aggregation routine.

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

Usage Notes

Implement this routine as a static method.

ODCIAggregateIterate

The ODCIAggregateIterate function is invoked by Oracle to process the next input row. The routine is invoked by passing in the aggregation context and the value of the next input to be aggregated. This routine processes the input value, updates the aggregation context accordingly, and returns the context back to Oracle. This routine is invoked by Oracle for every value in the underlying group, including NULL values.

Syntax

MEMBER FUNCTION ODCIAggregateIterate(
   self IN OUT <impltype>, 
   val <inputdatatype>) 
RETURN NUMBER

Table 22-2 ODCIAggregateIterate Parameters

Parameter	Meaning
self (IN)	The value of the current aggregation context
self (OUT)	The updated aggregation context returned to Oracle
val (IN)	The input value to be aggregated

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

Usage Notes

This is a mandatory routine and is implemented as a member method.

ODCIAggregateMerge

The ODCIAggregateMerge function is invoked by Oracle to merge two aggregation contexts into a single object instance. Two aggregation contexts may need to be merged during either serial or parallel evaluation of the user-defined aggregate. This function takes the two aggregation contexts as input, merges them, and returns the single, merged instance of the aggregation context.

Syntax

MEMBER FUNCTION ODCIAggregateMerge(
   self IN OUT <impltype>, 
   ctx2 IN <impltype>)
RETURN NUMBER

Table 22-3 ODCIAggregateMerge Parameters

Parameter	Meaning
self (IN)	The value of one aggregation context
ctx2 (IN)	The value of the other aggregation context
self (OUT)	The single, merged aggregation context returned to Oracle

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

Usage Notes

This is a mandatory routine and is implemented as a member method.

ODCIAggregateTerminate

The ODCIAggregateTerminate function is invoked by Oracle as the final step of aggregation. This routine takes the aggregation context as input and returns the resultant aggregate value to Oracle. This routine also typically performs any necessary cleanup operations such as freeing memory, and so on.

Syntax

MEMBER FUNCTION ODCIAggregateTerminate(
   self IN <impltype>, 
   ReturnValue OUT <return_type>, 
   flags IN number) 
RETURN NUMBER

Table 22-4 ODCIAggregateTerminate Parameters

Parameter	Meaning
self (IN)	The value of the aggregation context
ReturnValue (OUT)	The resultant aggregate value
flags (IN)	A bit vector that indicates various options. A set bit of `ODCI_AGGREGATE_REUSE_CTX` indicates that the context will be reused and any external context should not be freed. (See "Reusing the Aggregation Context for Analytic Functions".)

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

Usage Notes

This is a mandatory routine and is implemented as a member method.

ODCIAggregateDelete

The ODCIAggregateDelete function is invoked by Oracle to remove an input value from the current group. The routine is invoked by passing in the aggregation context and the value of the input to be removed. The routine processes the input value, updates the aggregation context accordingly, and returns the context to Oracle. This routine is invoked by Oracle during computation of user-defined aggregates with analytic (windowing) functions.

Syntax

MEMBER FUNCTION ODCIAggregateDelete(
   self IN OUT <impltype>, 
   val <inputdatatype>) 
RETURN NUMBER

Purpose

Table 22-5 ODCIAggregateDelete Parameters

Parameter	Meaning
self (IN)	The value of the current aggregation context
self (OUT)	The updated aggregation context returned to Oracle
val (IN)	The input value to be removed from the current group

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

Usage Notes

This is an optional routine and is implemented as a member method.

ODCIAggregateWrapContext

The ODCIAggregateWrapContext function is invoked by Oracle if the user-defined aggregate has been declared to have external context and is transmitting partial aggregates from slave processes. This routine must integrate all external pieces of the current aggregation context to make the context self-contained.

Syntax

MEMBER FUNCTION ODCIAggregateWrapContext(
   self IN OUT <impltype>) 
RETURN NUMBER

Table 22-6 ODCIAggregateWrapContext Parameters

Parameter	Meaning
self (IN)	The value of the current aggregation context
self (OUT)	The updated and self-contained aggregation context returned to Oracle

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

Usage Notes

This is an optional routine and is implemented as a member method.

See Also:

"Handling Large Aggregation Contexts" for more information on using this function