CREATE OR REPLACE  
TYPE hw_type AS OBJECT 
( 
   theString VARCHAR2(4000), 
   STATIC FUNCTION 
        ODCIAggregateInitialize(sctx IN OUT hw_type ) 
        RETURN NUMBER, 
 
   MEMBER FUNCTION 
        ODCIAggregateIterate(self IN OUT hw_type , 
                             VALUE IN VARCHAR2 ) 
        RETURN NUMBER, 
   MEMBER FUNCTION 
        ODCIAggregateTerminate(self IN hw_type, 
                               returnValue OUT  VARCHAR2, 
                               flags IN NUMBER) 
        RETURN NUMBER, 
   MEMBER FUNCTION 
        ODCIAggregateMerge(self IN OUT hw_type, 
                           ctx2 IN hw_type) 
        RETURN NUMBER 
); 

CREATE OR REPLACE  
TYPE BODY hw_type 
 IS  
 STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT hw_type) 
  RETURN NUMBER 
  IS 
 BEGIN 
  sctx := hw_type( NULL ); 
  RETURN ODCIConst.Success; 
 END; 
 MEMBER FUNCTION ODCIAggregateIterate(self IN OUT hw_type, 
                                      VALUE IN VARCHAR2 ) 
  RETURN NUMBER 
  IS 
 BEGIN 
  RETURN ODCIConst.Success; 
 END; 
 MEMBER FUNCTION ODCIAggregateTerminate(self IN hw_type, 
                                        returnValue OUT VARCHAR2, 
                                        flags IN NUMBER) 
  RETURN NUMBER 
  IS 
 BEGIN 
  returnValue := 'Hello Wold'; 
  RETURN ODCIConst.Success; 
 END; 
  
 MEMBER FUNCTION ODCIAggregateMerge(self IN OUT hw_type, 
                                    ctx2 IN hw_type) 
  RETURN NUMBER 
  IS   
 BEGIN 
  RETURN ODCIConst.Success; 
 END; 
END; 

CREATE OR REPLACE  
FUNCTION hello_wold(input VARCHAR2 ) 
RETURN VARCHAR2 
AGGREGATE USING hw_type; 

select x, hello_wold(y) 
from (select 1 x , 1 y from dual union all select 1 x , 2 y from dual union all select 2 x , 20 y from dual  union all select 2 x , 2 y from dual) 
group by x