As we saw in the Introduction to PL/SQL Collection Methods that there are 7 collection functions in Oracle Database. Among these seven functions we have already learnt the first five methods. Prior and Next are the last two PL/SQL collection methods/functions which are still left to explore.

Thus in today’s PL/SQL blog we will be exploring these two untouched collection methods in detail. 

What are Prior and Next Collection Functions?

It is always good to have some functions under your sleeve which can help you in navigating through the content of your collection. The function Prior & Next could be those functions.

Both these functions take an index of the collection as input and return the result.

For example PL/SQL collection method PRIOR takes an index as input and returns the value stored into the previous lowest index. Whereas the collection method NEXT returns the value from the next higher index.

Are Prior and Next collection Functions or Procedures?

Both Prior and Next are functions.

Can we use both these functions with all three types of collections?

Yes, both Prior and Next collection functions can be used with all the three types of collections. 

When will PL/SQL Collection Methods Prior and Next return null?

Collection Method Prior returns null when there are no lower subscript values available and Collection method Next returns null when there are no higher subscript values available to return.

In simple words we can say that both these collection functions return Null if they are used with the First and Last indexes of a collection respectively.

What will be the output of Collection method Next and Prior if we use them with associative array?

If collection method PRIOR and NEXT are used with associative arrays then they will return an output of VARCHAR2 or LONG datatype.

I’ve heard that, these methods raise some type of exception. Is it true?

Yes, it is true. If either of these functions are applied to an uninitialized Nested Table or a Varray then they raise the COLLECTION_IS_NULL exception.

Can you demonstrate us how to use these functions in our code?

Sure, why not! I will show you the application of both Prior and Next collection functions with the help of a very easy code.

Example of Collection Method Prior.

In this method we will learn how to use collection method Prior with Nested Table.

SET SERVEROUTPUT ON;
DECLARE
    TYPE my_nested_table IS
        TABLE OF NUMBER;
    var_nt   my_nested_table := my_nested_table(9,18,27,36,45,54,63,72,81,90);
BEGIN
        dbms_output.put_line('Index prior to index 3 is '||var_nt.PRIOR(3)); 
        dbms_output.put_line('Value before 3rd Index is '||var_nt(var_nt.PRIOR(3))); 
END;
/

In the above anonymous PL/SQL Block, we have two output statements with ‘Prior Function Call’. The first output statement will return the index number prior to the Index number 3 which has some value stored into it. In our case the index prior to index number 3 is 2. Thus 2 will be the output of first output statement.

In the second output statement we called the Prior function and supplied it as an input to the collection object.

var_nt(var_nt.PRIOR(3)) 

Oracle engine will execute the Collection function Prior first (let’s call it part 1) and using the result from part 1 it will then execute the collection object (let’s call it part 2) and produce the final result which will be 18 in our case. Go ahead, copy the code and try executing yourself.

What will happen if we delete the Previous Lowest Index from the nested table?

So now the question is, what will happen if you delete the Previous Lowest Index which is 2 in our code. In that case, result will definitely not be the same. Prior function returns the previous lowest index. But that Index must contain some value.

Try it yourself. Here is the code.

DECLARE
    TYPE my_nested_table IS
        TABLE OF NUMBER;
    var_nt   my_nested_table := my_nested_table(9,18,27,36,45,54,63,72,81,90);
BEGIN
        var_nt.DELETE(2);
        dbms_output.put_line('Index prior to index 3 is '||var_nt.PRIOR(3)); 
        dbms_output.put_line('Value before 3rd Index is '||var_nt(var_nt.PRIOR(3))); 
END;
/

Example of Collection Method Next.

You can use PL/SQL Collection method Next same as you used Prior function in the above code. Collection method NEXT returns the value from the next higher index. Here is the example

DECLARE
    TYPE my_nested_table IS
        TABLE OF NUMBER;
    var_nt   my_nested_table := my_nested_table(9,18,27,36,45,54,63,72,81,90);
BEGIN
        dbms_output.put_line('Next Higher Index to index 3 is '||var_nt.NEXT(3)); 
        dbms_output.put_line('Value after 3rd Index is '||var_nt(var_nt.NEXT(3))); 
END;
/

Both the examples are almost the same except the collection function call. In execution section of this PL/SQL block we again have two output statements. The first output statement will return the next non-empty index number while the second one will return the data stored into that index. The working of both these statements will be the same as we discussed above. Go ahead and copy the code and see the output.

That was the detailed tutorial on PL/SQL Collection method Next and Prior. Hope you enjoyed reading and learnt something new. Do make sure to Share this blog on your Social Media. To share this blog on your Facebook Click Here and for twitter Click Here.

Thanks & have a great day!