Let Us Learn Oracle - Part 29 of N [ Collect function of Oracle 10g ]

Introduce in Oracle 10g,this function comes under the category of Collection function. It helps to aggregate data into a collection. This function takes a column of any type as it's input argument and creates a nested table from that input on a row basis. It is very useful in row aggregation.

Let us take a simple example

SQL> Select DeptId, Collect(EmpName) CollectExample
  2  From tblemployee
  3  Group By DeptId;

    DEPTID		COLLECTEXAMPLE
------------	--------------------------------------------------------------------
        1		SYSTPWPBWoKKIQdW+EOftsPxbQA==('Deepak Kumar Goyal', 'Amitav Mallik')

        2		SYSTPWPBWoKKIQdW+EOftsPxbQA==('Shashi Dayal')

        3		SYSTPWPBWoKKIQdW+EOftsPxbQA==('Sumanta Manik')

	    5		SYSTPWPBWoKKIQdW+EOftsPxbQA==('Amit Ojha')

We can find out that some weired strings (e.g. 'SYSTPWPBWoKKIQdW+EOftsPxbQA== ) has come into picture. However, if we ignore them, we can infer that the COLLECT function has aggregated the Employee Names grouped by their Departments.

We just saw that Collect function has created some weired strings. Those are system generated collection types (found in sys schema) as shown under

SQL> SELECT 
  2       Owner
  3    ,Type_Name
  4    ,TypeCode 
  5  FROM   All_Types
  6  WHERE  Type_Name = 'SYSTPWPBWoKKIQdW+EOftsPxbQA==';

OWNER   	TYPE_NAME							TYPECODE				
------- 	------------------------------		-----------
SCOTT   	SYSTPWPBWoKKIQdW+EOftsPxbQA==		COLLECTION

So we can infer that veery time it parses a Collect Function SQL statement, it generates a supporting collection type

Having said all these,we rather need to get the result.For this we need to create our own collection types. Then by using teh Cast function, we can convert the results of the COLLECT into our desired type

So let us first create our own type

SQL> Create Or Replace Type CustomType As Table Of Varchar2(2000);
  2  /

Type created.

Now if we use this in our previous sql statement, we will get the below result

SQL> Select DeptId, Cast(Collect(EmpName) As CustomType) CollectExample
  2  From tblemployee
  3  Group By DeptId;

DEPTID			COLLECTEXAMPLE
------------	--------------------------------------------------------------------
         1		CustomType('Deepak Kumar Goyal', 'Amitav Mallik')

         2		CustomType('Shashi Dayal')

         3		CustomType('Sumanta Manik')

	     5		CustomType('Amit Ojha')

Much better. We got rid of the system collection. But still we have not reached to our goal. We need to get the result without any Collection type appended. For that, let us write a function that will accept a Collection and will return a string aggregation.

Create or Replace Function Collection2String
(
	collectionTbl in CustomType, 
	delimiter in Varchar2 Default ','
)Return Varchar2 Is

-- Variable declarations
resultString varchar2(32767);
startIdx PLS_INTEGER;
endIdx PLS_INTEGER;

Begin	

	startIdx := collectionTbl.First;
	endIdx := collectionTbl.Last;

 For i in startIdx .. endIdx Loop

	  If i != startIdx Then
		resultString  := Concat(resultString,delimiter);
	  End if;		
	  resultString := Concat(resultString,collectionTbl(i));
 
 End Loop;
 Return resultString;
 End  Collection2String;
 

And finally we can use it as under

SQL> Select DeptId, Collection2String(Cast(Collect(EmpName) As CustomType)) CollectExample
  2      From tblemployee
  3      Group By DeptId;

DEPTID			COLLECTEXAMPLE
------------	---------------------------------
         1		Deepak Kumar Goyal, Amitav Mallik

         2		Shashi Dayal

         3		Sumanta Manik

		 5		Amit Ojha

We can even pass the delimiter of our choice in to bring the result of our format

SQL> Select DeptId, Collection2String(Cast(Collect(EmpName) As CustomType),'|') CollectExample
  2      From tblemployee
  3      Group By DeptId;

DEPTID			COLLECTEXAMPLE
------------	---------------------------------
         1		Deepak Kumar Goyal | Amitav Mallik

         2		Shashi Dayal

         3		Sumanta Manik

	     5		Amit Ojha

Hope you like this. Thanks for reading