CODEWITHSUNDEEP
oracle-databasepackageaggregate-functions
user1021531
user1021531

Reputation: 477

use User-Defined Aggregate Functions in package

my oracle version is 10.2. I have a user_defined aggregate functions which using a object like this:

create type strcat_type as object ( 
cat_string varchar2(4000), 
static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number, 
member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return           
number, 
member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type)       
return number, 
member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out   
varchar2,flags in number) return number 
)

but when I try to put this object in a package,

create or replace package common is
type strcat_type as object ( 
cat_string varchar2(4000), 
static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number, 
member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return           
number, 
member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type)       
return number, 
member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out   
varchar2,flags in number) return number 
)
end common;

It causes pls-00540,pls-00707,how can I create a package with using this object?And how to put my type body and all those static and member functions in package body?

my type body like this:

create type body strcat_type is 
static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number 
is 
begin 
  cs_ctx := strcat_type( null ); 
  return ODCIConst.Success; 
end; 

member function ODCIAggregateIterate(self IN OUT strcat_type, 
                                   value IN varchar2 ) 
return number 
is 
begin 
   self.cat_string := self.cat_string || ','|| value; 
   return ODCIConst.Success; 
end; 

member function ODCIAggregateTerminate(self IN Out strcat_type, 
                                     returnValue OUT varchar2, 
                                     flags IN number) 
return number 
is 
begin 
  returnValue := ltrim(rtrim(self.cat_string,','),','); 
  return ODCIConst.Success; 
end; 

member function ODCIAggregateMerge(self IN OUT strcat_type, 
                                 ctx2 IN Out strcat_type) 
return number 
is 
begin 
  self.cat_string := self.cat_string || ',' || ctx2.cat_string; 
  return ODCIConst.Success; 
end;
end;

my function is like this:

CREATE or replace 
FUNCTION strcat(input varchar2 ) 
RETURN varchar2 
PARALLEL_ENABLE AGGREGATE USING strcat_type;

how to pack all these into a package and when I need aggregate,I just put 'common.strcat(colName)'?

Upvotes: 0

Views: 1226

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

OBJECT types are SQL objects (like tables, index...), they can't be defined in a package (just like you wouldn't define an index or a view inside a package).

See related SO question: Possible to create Oracle Database object types inside of PL/SQL?

Upvotes: 1

Related Questions