Let us learn how to make custom Aggregate Function in Oracle

Introduction

User-defined aggregates are a feature of the Extensibility Framework.It is possbile to make user define Aggregate function because of Oracle's Data Cartridge model which takes advantage of object types and other extensibility features. Inorder to create user-defined aggregate function, we need to implement a set of routines collectively referred to as the ODCIAggregate routines. It is of four types which are

1. ODCIAggregateInitialize
2. ODCIAggregateIterate
3. ODCIAggregateMerge
4. ODCIAggregateTerminate

Below is a brief description about the above routines

It is quite simple to create user define Aggregate function in Oracle.For creating a user define aggreagte function, we need -

1. Object type specification
2. Object type body
3. PL/SQL aggregate function

Straight to Experiment

So armed with all these information, we can now create our own aggregate function that will perform string aggregation.

Let us first see the original source

SQL> Select DeptName,EmpName
     From tblDept
     Join tblEmployee
     Using(DeptId)
     Group By DeptName;

DEPTNAME                                           EMPNAME
-------------------------------------------------- ----------------------
IT                                                 Deepak Kumar Goyal
Accounts                                           Shashi Dayal
IT                                                 Amitav Mallik
Sales                                              Amit Ojha
Finance                                            Sumanta Manik
IT                                                 Deepak Singh
IT                                                 Shashi Bhushan
IT                                                 Amitav Salonki
Accounts                                           Deepak Singh
Accounts                                           Shashi Bhushan
Accounts                                           Amitav Salonki
Accounts                                           Manish Bharat
Accounts                                           Abhijeet Moshambique
Finance                                            Sarapati Babulal Apte
Finance                                            Shinarayan Pande
Finance                                            Fatima  Sarani

16 rows selected.

Well Oracle already has WM_Concat(undocumented function in in 10g) and ListAgg function(Oracle 11g R2) for doing so. Let us see what the built-in ListAgg/WM_Concat function is doing.

SQL> Select DeptName,ListAgg(EmpName,',') Within Group (Order By EmpName) Concat_Employees
     From tblDept
     Join tblEmployee
     Using(DeptId)
     Group By DeptName;

	- OR - 

SQL>  Select DeptName,WM_Concat(EmpName) Concat_Employees
	 From tblDept
	 Join tblEmployee
	 Using(DeptId)
	 Group By DeptName;

DEPTNAME                                           CONCAT_EMPLOYEES
-------------------------------------------------- -------------------------------------------------------
Accounts                                           Shashi Dayal,Deepak Singh,Amitav Salonki,Shashi Bhushan
						   ,Manish Bharat,Abhijeet Moshambique

Finance                                            Sumanta Manik,Sarapati Babulal Apte,Fatima  Sarani
                                                   ,Shinarayan Pande

IT                                                 Deepak Kumar Goyal,Deepak Singh,Amitav Salonki
						   ,Shashi Bhushan,Amitav Mallik

Sales                                              Amit Ojha

Our aim is to make a similar kind of function.

N.B.~If someone ask me since we already have buit-in function for achieving the result then why we need to build our own? Well it is for our demonstration purpose

Now let us look into the practical aspect of doing so

Step 1: First create the Object type specification

SQL> CREATE OR REPLACE TYPE String_Aggregate_Type AS OBJECT(

    str_values VARCHAR2(8000),
    
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT String_Aggregate_Type) 
    RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateIterate(self  IN OUT String_Aggregate_Type					
					 ,value IN VARCHAR2) 
    RETURN NUMBER,

	 MEMBER FUNCTION ODCIAggregateMerge(self IN OUT String_Aggregate_Type 
                                       ,ctx2 IN String_Aggregate_Type) 
    RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateTerminate (self IN String_Aggregate_Type
                                            ,return_value OUT VARCHAR2
                                            ,flags IN NUMBER)               
   RETURN NUMBER
   
);
/
Type created.

To keep things simple, our type needs only the string values(str_values). If we look into the original source, it is EMPNAME values

Step 2: Second, create the Object type body

SQL> CREATE OR REPLACE TYPE BODY String_Aggregate_Type IS 

    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT String_Aggregate_Type) 
    RETURN NUMBER IS 
    BEGIN
        sctx := String_Aggregate_Type(null);
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self  IN OUT String_Aggregate_Type					
					 ,value IN varchar2) 
    RETURN NUMBER IS
    BEGIN
        str_values := str_values || ',' || value;
		str_values := LTRIM(str_values,',');
        RETURN ODCIConst.Success;
    END;    

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT String_Aggregate_Type 
                                       ,ctx2 IN String_Aggregate_Type) 
    RETURN NUMBER
    IS
    BEGIN
        str_values := str_values || ctx2.str_values;
        return ODCIConst.Success;
    END;

	MEMBER FUNCTION ODCIAggregateTerminate(self IN String_Aggregate_Type
                                            ,return_value OUT VARCHAR2
                                            ,flags IN NUMBER)               
   RETURN NUMBER 
   IS
    BEGIN
        return_value := str_values ;
        return ODCIConst.Success;
    END;
END;
/
Type body created.

In the ODCIAggregateInitialize, we are initializing str_values with null values. In ODCIAggregateIterate, the values(in this case EMPNAME) are being iterated, appended with a comma(,) delimeter and are store into the str_values.This is the method that tells Oracle on how to aggregate the individual elements that are passed to it. The ODCIAggregateMerge combines two aggregation contexts and returns a single aggregation context.Finally, ODCIAggregateTerminate returns the aggregated result

Step 3: Create the PL/SQL aggregate function

SQL> CREATE OR REPLACE FUNCTION Custom_String_Aggregation(inputString VARCHAR2) 
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING String_Aggregate_Type;
/
Function created.

So our function is now ready to use and let us use it as under

SQL> Select DeptName,Custom_String_Aggregation(EmpName) Concat_Employees
     From tblDept
     Join tblEmployee
     Using(DeptId)
     Group By DeptName;

DEPTNAME                                           CONCAT_EMPLOYEES
-------------------------------------------------- -------------------------------------------------------
Accounts                                           Shashi Dayal,Deepak Singh,Amitav Salonki,Shashi Bhushan
						   ,Manish Bharat,Abhijeet Moshambique

Finance                                            Sumanta Manik,Sarapati Babulal Apte,Fatima  Sarani
                                                   ,Shinarayan Pande

IT                                                 Deepak Kumar Goyal,Deepak Singh,Amitav Salonki
						   ,Shashi Bhushan,Amitav Mallik

Sales                                              Amit Ojha

Conclusion

So in this article, we have seen how to create own Aggregate Function using Oracle.Hope this will be helpful.Thanks for reading.