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.

ODCIAggregateInitialize

Syntax

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

Purpose

The ODCIAggregateInitialize function is 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.

Table 19-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

Syntax

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

Purpose

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.

Table 19-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

Syntax

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

Purpose

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.

Table 19-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

Syntax

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

Purpose

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.

Table 19-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 that therefore any external context should not be freed. (See "Reusing the Aggregation Context for Analytic Functions" for information on using this flag.)

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

Syntax

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

Purpose

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.

Table 19-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

Syntax

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

Purpose

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.