Oracle User definied aggregate function for varray of varchar
- by baju
I am trying to write some aggregate function for the varray and I get this error code when I'm trying to use it with data from the DB:
ORA-00600 internal error code, arguments: [kodpunp1], [], [], [], [], [], [], [], [], [], [], []
[koxsihread1], [0], [3989], [45778], [], [], [], [], [], [], [], []
Code of the function is really simple(in fact it does nothing ):
create or replace
TYPE "TEST_VECTOR" as varray(10) of varchar(20)
ALTER TYPE "TEST_VECTOR" MODIFY LIMIT 4000 CASCADE
create or replace
type Test as object(
lastVector TEST_VECTOR,
STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number,
MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in TEST_VECTOR) return number,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number,
MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT TEST_VECTOR, flags IN number) return number
);
create or replace
type body Test is
STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number is
begin
sctx := Test(TEST_VECTOR());
return ODCIConst.Success;
end;
MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in TEST_VECTOR) return number is
begin
self.lastVector := value;
return ODCIConst.Success;
end;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number is
begin
return ODCIConst.Success;
end;
MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT TEST_VECTOR, flags IN number) return number is
begin
returnValue := self.lastVector;
return ODCIConst.Success;
end;
end;
create or replace
FUNCTION test_fn (input TEST_VECTOR) RETURN TEST_VECTOR
PARALLEL_ENABLE AGGREGATE USING Test;
Next I create some test data:
create table t1_test_table(
t1_id number not null,
t1_value TEST_VECTOR not null,
Constraint PRIMARY_KEY_1 PRIMARY KEY (t1_id)
)
Next step is to put some data to the table
insert into t1_test_table (t1_id,t1_value) values (1,TEST_VECTOR('x','y','z'))
Now everything is prepared to perform queries:
Select test_fn(TEST_VECTOR('y','x')) from dual
Query above work well
Select test_fn(t1_value) from t1_test_table where t1_id = 1
Version of Oracle DBMS I use: 11.2.0.3.0
Does anyone tried do such a thing?
What can be the reason that it does not work?
How to solve it? Thanks in advance for help.
