PRIOR Method in PL/SQL – Access Previous Collection Index

The PRIOR Method in PL/SQL is a built-in collection function used to retrieve the previous subscript value in a collection. It’s particularly useful when navigating through sparse or string-indexed collections, such as associative arrays.

By using the PRIOR Method in PL/SQL, you can implement reverse navigation in collections safely, without assuming that subscripts are sequential.

What is the PRIOR Method in PL/SQL?

The PRIOR method returns the immediately lower index (subscript) in a collection relative to the one provided.

Returns PLS_INTEGER for numeric indexes
Returns VARCHAR2 for associative arrays with string keys
Returns NULL if no smaller index exists

Syntax of PRIOR Method

collection_name.PRIOR(index_value)

Returns:
- The previous valid subscript
- NULL if the input is the first subscript

Example: Using PRIOR Method with String Indexes

DECLARE
   TYPE assoc_array IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
   fruits assoc_array;
BEGIN
   fruits('Apple') := 100;
   fruits('Banana') := 200;
   fruits('Mango') := 300;

   DBMS_OUTPUT.PUT_LINE('LAST: ' || fruits.LAST);
   DBMS_OUTPUT.PUT_LINE('PRIOR of LAST: ' || fruits.PRIOR(fruits.LAST));

   DBMS_OUTPUT.PUT_LINE('FIRST: ' || fruits.FIRST);
   DBMS_OUTPUT.PUT_LINE('PRIOR of FIRST: ' || TO_CHAR(fruits.PRIOR(fruits.FIRST)));
END;
/

Output:

LAST: Mango
PRIOR of LAST: Banana
FIRST: Apple
PRIOR of FIRST:

Explanation of PRIOR Method in PL/SQL

In associative arrays, PL/SQL uses alphabetical order (based on NLS_SORT) for subscripts. In the above example:

PRIOR('Mango') → 'Banana'
PRIOR('Apple') → NULL

This demonstrates safe backward traversal through a collection.

Use Cases of PRIOR Method in PL/SQL

Scenario	Use
Reverse traversal	Looping backwards in collections
Sparse collection management	Skipping deleted/null values
Safe navigation	Avoiding exceptions on missing indexes

Best Practices

Use EXISTS to verify the collection is not null.
Combine FIRST, LAST, PRIOR, and NEXT to safely iterate collections.
Don’t assume string indexes are numerically ordered.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.