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

ScenarioUse
Reverse traversalLooping backwards in collections
Sparse collection managementSkipping deleted/null values
Safe navigationAvoiding 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.

Previous
Next

๐Ÿ“˜ AmantPoint Exclusive Learning Series
ยฉ 2025 AmantPoint. All rights reserved. Educational content for developers and learners.

Facebooktwitterredditpinterestlinkedinmailby feather
Scroll to Top