As Oracle manual says
You can create a user-defined aggregate function by implementing a set of routines collectively known as the ODCIAggregate routines. You can implement these routines as methods within an object type, so the implementation can be in any language that Oracle supports, PL/SQL, C, C++ or Java.
There is no other info how to implement it. I have found example of implementing this in C/C++
Anyone know how to accomplish this using Java? Any info would be great.
Yes a Java implementation of Oracle aggregate function is indeed possible.
The enabling trick is not to use a Java implementation of an ORACLE TYPE
but to define a PL/SQL wrapper package above the Java class and use the
PL/SQL functions in the TYPE implementation.
Very simple implementation of a Hello World Java based aggregate function follows - the aggregate function returns always a
string "Hello World".
The Java class makes nearly nothing only returns in the ODCITerminate function the string "Hello World".
Any practical implementation on a Java based aggregate function will need to set up a context class and pass it to each iterate method.
Java Class
Create or Replace AND RESOLVE Java Source Named "JAggrFun" As
import java.math.BigDecimal;
class JAggrFun {
/*
Supporting Java class for dummy aggregate function
Implemented methods:
initiate - dummy
iterate - dummy
terminate - return "Hello World"
merge (is not required)
*/
final static BigDecimal SUCCESS = new BigDecimal(0);
final static BigDecimal ERROR = new BigDecimal(1);
static public BigDecimal ODCIInitialize(BigDecimal[] sctx) {
return SUCCESS;
}
static public BigDecimal ODCIIterate(BigDecimal ctx, String str) {
return SUCCESS;
}
static public BigDecimal ODCITerminate(BigDecimal ctx, String[] str) {
str[0] = "Hello World";
return SUCCESS;
}
}
/
wrapper package of Java methods
create or replace
PACKAGE JAggrFunPackage authid current_user AS
FUNCTION ODCIInitialize(
ctx OUT NOCOPY NUMBER
) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'JAggrFun.ODCIInitialize(
java.math.BigDecimal[]) return java.math.BigDecimal';
FUNCTION ODCIIterate(
ctx IN NUMBER,
str VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'JAggrFun.ODCIIterate(
java.math.BigDecimal,
java.lang.String) return java.math.BigDecimal';
FUNCTION ODCITerminate(
ctx IN NUMBER,
str OUT VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'JAggrFun.ODCITerminate(
java.math.BigDecimal,
java.lang.String[]) return java.math.BigDecimal';
END JAggrFunPackage;
/
Type
CREATE OR REPLACE
TYPE JAggrFunPackageType authid current_user AS OBJECT
(
jctx NUMBER, -- stored context; not used in dummy implementation
STATIC FUNCTION
ODCIAggregateInitialize(sctx IN OUT NOCOPY JAggrFunPackageType )
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateIterate(self IN OUT NOCOPY JAggrFunPackageType,
VALUE IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateTerminate(self IN JAggrFunPackageType,
returnValue OUT NOCOPY VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateMerge(self IN OUT NOCOPY JAggrFunPackageType,
ctx IN JAggrFunPackageType)
RETURN NUMBER
);
/
Aggregate Function
CREATE OR REPLACE
FUNCTION JAggr(input VARCHAR2 )
RETURN VARCHAR2
AGGREGATE USING JAggrFunPackageType;
/
Type Body
create or replace
type body JAggrFunPackageType is
static function ODCIAggregateInitialize(sctx IN OUT NOCOPY JAggrFunPackageType)
return number
is
begin
sctx := JAggrFunPackageType( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT NOCOPY JAggrFunPackageType,
value IN varchar2 )
return number
is
status NUMBER;
begin
if self.jctx is null then
status := JAggrFunPackage.ODCIInitialize(self.jctx);
if (status <> ODCIConst.Success) then
return status;
end if;
end if;
status := JAggrFunPackage.ODCIIterate(jctx,value);
return status;
end;
member function ODCIAggregateTerminate(self IN JAggrFunPackageType,
returnValue OUT NOCOPY VARCHAR2,
flags IN number)
return number
is
begin
return JAggrFunPackage.ODCITerminate(jctx, returnValue);
end;
member function ODCIAggregateMerge(self IN OUT NOCOPY JAggrFunPackageType,
ctx IN JAggrFunPackageType)
return number
is
begin
return ODCIConst.Success;
end;
end;
/
Test
select JAggr(dummy) from dual;
JAGGR(DUMMY)
-------------
Hello World
