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".
Note that nny practical implementation of a Java based aggregate function will need to set up a context class and pass it to each iterate method - you can find an example of such context class in the DBPrism example - see follow up above.
Java ClassCreate 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 methodscreate 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;
/
TypeCREATE 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 FunctionCREATE OR REPLACE
FUNCTION JAggr(input VARCHAR2 )
RETURN VARCHAR2
AGGREGATE USING JAggrFunPackageType;
/
Type Bodycreate 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;
/
Testselect JAggr(dummy) from dual;
JAGGR(DUMMY)
-------------
Hello World Thanks very much for the support with solving this problem. A a final note - you were right, I finally realized I do not need a Java implemented aggregate function at all. I can use a PL/SQL implementation for my purpose - see details in
https://community.oracle.com/ideas/20275