FIRST Method in PL/SQL – Usage with Numeric and String Indexes

The FIRST Method in PL/SQL is a built-in function used with collections to retrieve the lowest index currently in use. Whether you’re working with numeric indexes in nested tables or string keys in associative arrays, the FIRST method helps you safely iterate and manage data in your collection.

Let’s explore the syntax, behavior, and examples of using the FIRST Method in PL/SQL.

What is the FIRST Method in PL/SQL?

The FIRST Method returns the first subscript value (lowest index) used in a collection.

For numeric indexes, it returns a PLS_INTEGER.
For associative arrays with string keys, it returns a VARCHAR2.

Syntax of FIRST Method in PL/SQL

collection_name.FIRST

Returns:

The lowest (first) index in use.
NULL if the collection is empty.

Example: FIRST Method with String Indexes

DECLARE
   -- Define an associative array with VARCHAR2 keys
   TYPE number_table IS TABLE OF INTEGER INDEX BY VARCHAR2(9 CHAR);
   number_list NUMBER_TABLE;
BEGIN
   -- Initialize three elements with string keys
   number_list('One') := 1;
   number_list('Two') := 2;
   number_list('Nine') := 9;

   -- Display FIRST and NEXT
   DBMS_OUTPUT.PUT_LINE('FIRST Index [' || number_list.FIRST || ']');
   DBMS_OUTPUT.PUT_LINE('NEXT Index [' || number_list.NEXT(number_list.FIRST) || ']');

   -- Display LAST and PRIOR
   DBMS_OUTPUT.PUT_LINE(CHR(10) || 'LAST Index [' || number_list.LAST || ']');
   DBMS_OUTPUT.PUT_LINE('PRIOR Index [' || number_list.PRIOR(number_list.LAST) || ']');
END;
/

Output:

FIRST Index [Nine]
NEXT  Index [One]

LAST  Index [Two]
PRIOR Index [One]

Explanation:

The collection is indexed by string keys: 'One', 'Two', and 'Nine'.
PL/SQL orders the keys alphabetically (based on the NLS environment).
FIRST returns 'Nine' as it is the alphabetically smallest string.
NEXT(FIRST) returns 'One' – the next in alphabetical order.
LAST returns 'Two'.
PRIOR(LAST) returns 'One'.

Comparison: FIRST with Numeric vs String Indexes

Type	Index Type	FIRST Returns	Ordering Logic
Nested Table	Numeric	PLS_INTEGER	Smallest numeric index
Associative Array	String	VARCHAR2	Alphabetical (NLS-based)

Best Practices for FIRST Method in PL/SQL

Use FIRST in combination with NEXT to loop through elements.
Always check for NULL from FIRST to avoid exceptions in empty collections.
Do not use in a FOR i IN FIRST..LAST loop if the index is non-numeric.
Use it to build safe, sparse-aware collection loops.

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