Today’s PL/SQL tutorial is all about Collection Method LIMIT. We have already seen 4 functions in this collection method series that are – Count, Exists, First and Last. Limit is the fifth function which we will be learning today.

What is Collection Method LIMIT?

Collection method LIMIT which is actually a PL/SQL function returns the maximum number of elements that a VARRAY can hold. This means that by using this function you can find out how many elements you can store in a VARRAY.

What does collection method LIMIT return?

Collection method LIMIT return a value of PLS_INTEGER type?

Does this function works with other two collection Nested Tables and Associative Array also?

Collection method LIMIT only works with VARRAY. If it applied to Nested table or associative array then this function will either return a NULL or No value. So the answer is, No, the collection function LIMIT does not work with Nested tables and Associative Arrays.

What is the specification of collection function LIMIT?

The specification of collection function LIMIT is:

FUNCTION LIMIT RETURN pls_integer;

Does the collection function LIMIT raises any exception? If yes, then when?

Yes the function LIMIT raises COLLECTION_IS_NULL exception if it is applied to an uninitialized nested table or a VARRAY.

Can you show us an example of how to use the function LIMIT?

Sure, why not. Here is a very simple example demonstrating, how to properly use collection function LIMIT with VARRAYs.

SET SERVEROUTPUT ON;
DECLARE
    TYPE inBlock_vry IS VARRAY (5) OF NUMBER;
    vry_obj inBlock_vry := inBlock_vry();
BEGIN
 --Let's find out total number of indexes in the above VARRAY
    DBMS_OUTPUT.PUT_LINE ('Total Indexes '||vry_obj.LIMIT);
END;
/

Don’t we have the function COUNT which gives the same information?

The collection function LIMIT returns the total number of indexes of a VARRAY regardless of whether these indexes are empty or holding some data.  It checks the definition of the VARRAY and sees the total number of elements it is designed to store and returns that number.

While the collection function COUNT returns the number of Indexes which are not empty and holding some data.

Take a look at this PL/SQL program. This will help you understanding the difference between collection method COUNT and LIMIT more clearly.

SET SERVEROUTPUT ON;
DECLARE
    --Create VARRAY of 5 element
    TYPE inblock_vry IS
        VARRAY ( 5 ) OF NUMBER;
    vry_obj   inblock_vry := inblock_vry ();
BEGIN
    --Insert into VARRAY
    	vry_obj.extend;
    	vry_obj(1) := 10 * 2; 
dbms_output.put_line('Total Number of Index ' || vry_obj.limit);
dbms_output.put_line('Total Number of Index which are occupied ' || vry_obj.count);
END;
/

In the above code we have a VARRAY which is capable of holding 5 elements of NUMBER datatype. In the execution section we have two DBMS output statements. The first output statement which is showing the result of LIMIT function will return 5 because that is the total strength of our VARRAY while the second output statement will return 1 because among those 5 indexes there is only one index which has some data stored into it.

You said in the video that you will show us how to find out the total number of elements left un-used for us to store data into a varray using collection method LIMIT?

Is that so? Have I said that? I am just kidding.

Finding out the number of vacant index for your use in a VARRAY is very easy. Let me tell you how.

As I said above collection function COUNT returns the number of indexes which have data stored into them and collection function LIMIT returns the total number of indexes a VARRAY can accommodate.

If you subtract the result of count function from the result of the LIMIT function you will get the total number of elements left un-used for you to store data into a varray. For example

DECLARE
    --Create VARRAY of 5 element
    TYPE inblock_vry IS
        VARRAY ( 5 ) OF NUMBER;
    vry_obj   inblock_vry := inblock_vry ();
BEGIN
    --Insert into VARRAY
    vry_obj.extend;
    vry_obj(1) := 10 * 2;
    dbms_output.put_line('Total Number of Index ' || vry_obj.limit);
    dbms_output.put_line('Total Number of Index which are occupied ' || vry_obj.count);
    dbms_output.put_line('Total Number of Vacant index left for use '
                            || (vry_obj.limit - vry_obj.count) );
END;
/

That’s the PL/SQL tutorial answering all the question which you can expect in certification exam as well as in your Interview about collection method LIMIT in Oracle database.

Is there anything which I forgot to cover or mention in this tutorial? If yes, then please let me. Write to me on my Twitter or Facebook.

Thanks have a great day.