PL/SQL Collection Methods

By Manish Sharma

EXISTS Function

Collection Method EXISTS in Oracle Database by Manish Sharma

As you know that in the previous tutorial we learnt the concepts of first collection function – COUNT. So now it’s time to move on to the second function of the collection method series. Thus here I present to you the PL/SQL Collection Method EXISTS. In this blog we will explore the collection method EXISTS ( ) in detail.

What is Collection Method EXISTS ( )?

Collection Method EXISTS ( ) checks the existence of an element at a specific index in a collection. If it finds the specified element then it returns TRUE otherwise it returns FALSE.

 You can use EXISTS ( ) function to check the existence of a specific row within the collection.

Syntax of Collection Method EXISTS ( )

The syntax of EXISTS ( ) function is –

 EXISTS (index number);
EXISTS function takes the subscript/index number of a cell of a collection as an Input and searches it in the collection. If it finds any element corresponding to the index number then it returns TRUE otherwise it returns FALSE.

Does the collection method EXISTS returns NULL?

No EXISTS function does not return null. It either returns True or False.

What if I remove an already existing row using TRIM or DELETE function?

If you remove a row using Trim or Delete function then collection method EXISTS ( ) will return FALSE for the index of that row.

Does the collection method EXISTS raises any exception?

No, collection method EXISTS does not raise any exception. In fact, this is the only function which does not raise any exception, even if it used with an uninitialized collection.

If it does not raise an exception then what will happen if I applied this function to an uninitialized collection?

Collection method EXISTS ( ) returns false, if it is applied either to an uninitialized collection or to an initialize collection with no elements.

Example of Collection Method EXISTS ( )

This simple example will show you how to use this function in your application.


  --Declare a local Nested Table
  TYPE my_nested_table IS TABLE OF VARCHAR2 (20);

  --Declare collection variable and initialize the collection.
  col_var_1 my_nested_table := my_nested_table('Super Man','Iron Man','Bat Man');

  IF col_var_1.EXISTS (1) THEN
   DBMS_OUTPUT.PUT_LINE ('Hey we found '||col_var_1 (1));
   DBMS_OUTPUT.PUT_LINE ('Sorry, no data at this INDEX');
In the above program we are checking if there is any element at index 1 of the collection ‘my_nested_table’ or not. If there is an element at the specified index then the IF part of the IF-ELSE statement will execute otherwise the ELSE part will come into the action.

What will you do…?

Suppose you want to insert a data into a specific index but you are not sure whether that index is already holding some data or not. In case if it is holding any then you don’t want to overwrite it. What will you do in this case?

To know how you can use the collection method EXISTS to find the solution to this problem go ahead and watch the PL/SQL tutorial on my YouTube channel.

This is a very simple demonstration. I am sure you can come up with some crazier examples. So what are you waiting for, go ahead and write your code and see in what other possible ways you can use this method.

If you want me to review your code then you can share it with me on my Facebook page or on my Twitter as well.

 How do you like this blog? Is there anything you want us to improve? Tell us, what you feel on our Facebook page and on our Twitter.

Thanks and have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

How do you like this blog? Is there anything you want us to improve? Tell us, what you feel on our Facebook page and on our Twitter .

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn

         View Manish Sharma's profile on LinkedIn