Let us explore Collect function of Oracle

Niladri.Biswas
Posted by in Oracle category on for Beginner level | Points: 250 | Views : 2260 red flag

In this article, we will know about Oracle's Collect function

Introduction

Introduced in Oracle 10g,the Collect 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.

Straight to Experiment

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 every 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 the 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

Conclusion

So in this article, we have seen the importance of Oracle's Collect function.Hope this will be helpful.Thanks for reading.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)