Welcome back once again to the series on Collection Methods in Oracle Database. So far in this series we have covered Collection Function Count ( ) and Collection Function Exist ( ). Hope you enjoyed reading. Today in this PL/SQL tutorial we will learn collection methods First ( ) and Last ( ).

What are collection methods FIRST ( ) and LAST ( )?

We use collection functions First & Last to know the first and last index values defined in a collection.

Can we use these collection methods with any type of collection?

Yes! You can use both these functions with all three types of collections that are Associative Array, Nested table and VARRAYs.

When does collection method FIRST ( ) and LAST ( ) return null?

Both the functions return null when applied to an empty collection or when applied to an initialize collection that has no elements.

Can you list the function specification for both these functions?

Sure! Why not. The specification for collection function FIRST ( ) is:

FUNCTION FIRST RETURN PLS_INTEGER | VARCHAR2

And the function specification for collection function LAST ( ) is:

FUNCTION LAST RETURN PLS_INTEGER | VARCHAR2

Info:
For string indexed associative array, these methods return strings; “lowest” and “highest” are determined by the ordering of the character set in use in that session.

What if there is only 1 element in my VARRAY?

In that case collection function FIRST ( ) is always 1 and collection method LAST ( ) is always equal to COUNT.

What if I applied these functions to an uninitialized collection?

I mean seriously, why would you do so! Anyways if you applied collection function FIRST & LAST to an uninitialized collection then it will raise COLLECTION_IS_NULL exception.

Example: How to use collection function FIRST and LAST with collection?

SET SERVEROUTPUT ON;
DECLARE
    TYPE nt_tab IS TABLE OF NUMBER;
    col_var nt_tab := nt_tab(10, 20, 30, 40, 50);
BEGIN
    DBMS_OUTPUT.PUT_LINE ('First Index of the Nested table is ' || col_var.FIRST);
    DBMS_OUTPUT.PUT_LINE ('Last Index of the Nested table is ' || col_var.LAST);
END;
/

In the above example we have created a nested table with the name NT_TAB and initialized it using collection variable col_var. This nested table has 5 indexes into which we have stored the values. The lowest index in this nested table is 1 which is holding value 10 and maximum index is 5 with value 50. Thus on execution the result from the first DBMS_OUTPUT will be 1 and from the second DBMS_OUTPUT statement will be 5.

Go ahead copy the code and checkout the result yourself.

What if we delete the first element of the nested table? What will then be the output of collection function FIRST?

That is a very good question! If you delete the first element of the collection function then the collection function FIRST will return the subscript which is greater than 1 and is holding some data. Let’s see the example:

SET SERVEROUTPUT ON;
DECLARE
    TYPE nt_tab IS TABLE OF NUMBER;
    col_var nt_tab := nt_tab(10, 20, 30, 40, 50);
BEGIN
col_var.DELETE(1);
    DBMS_OUTPUT.PUT_LINE ('First Index after DELETE is ' || col_var.FIRST);
END;
/

In the above example we deleted the first element of the nested table nt_tab using the collection method DELETE.  After deleting the first element which is 10 on index 1 the new lowest subscript is now 2 which has some data stored into it. Thus on execution the result will be 2.

What if I delete the element from the middle of the collection?

If you delete the data from the middle then the collection function LAST will return a value which is greater than the value returned by COUNT method.

Can we see the data stored into the indexes of the collection using FIRST and LAST collection methods?

When I was explaining this same concept in a class a student raised her hand and asked me.

Sir, so far we see that these functions return the index number of the collection. What if we want to see the data stored into those index. Is there a way to see the data stored into the index using these collection methods FIRST & LAST?

The answer to this question is, yes! Of course. Along with subscript number you can use these functions to see the data stored in lowest and highest index of the collection.

For example

SET SERVEROUTPUT ON;
DECLARE
    TYPE nt_tab IS TABLE OF NUMBER;
    col_var nt_tab := nt_tab(10, 20, 30, 40, 50);
BEGIN
    -- This output statement will return 10 which is the value stored at the first index
    DBMS_OUTPUT.PUT_LINE ('Value stored at First Index is ' || col_var(col_var.FIRST));
    -- This output statement will return 50 which is the value stored at the last index
    DBMS_OUTPUT.PUT_LINE ('Value stored at First Index is ' || col_var(col_var.LAST));
END;
/

In order to see the data stored into the first and last index you just have to place the function calls of these function inside the parenthesis of collection variable which is col_var just like we did in the above example.

Now the question is what if we TRIM the collection? What will then be the output of the collection function LAST? Go ahead and check out the video tutorial where I have explained what will be the output of collection method LAST when you trim the collection using the example.

That’s all about these Collection Methods. Hope you found this blog useful. Thanks & have a great day!

RELATED ARTICLESMORE FROM AUTHOR