Oracle FIRST and LAST methods

In Oracle PL/SQL the FIRST and LAST methods are functions. If there is at least one element in the collection, FIRST and LAST return the indexes of the first and last element respectively (ignoring the deleted elements, even if DELETE stores fillers for them).

If the collection has only one element, FIRST and LAST return the same index. If the collection is empty, FIRST and LAST return NULL.

Syntax of FIRST and LAST collection methods in Oracle PL/SQL

collection_name.FIRST;
collection_name.LAST;

Method parameters or arguments

• collection_name – types of collection.
• FIRST – index of the first element of the collection.
• LAST – index of the last element of the collection.

Consider some examples to understand how to use FIRST and LAST collection methods in Oracle PL/SQL.

For Associative Arrays (associative array) indexed with integers, the first and last elements are those with the smallest and largest indexes respectively.

Let’s look at an example that shows the FIRST and LAST values for Associative Arrays indexed with integers, removes the first and last element and shows FIRST and LAST values again.

DECLARE
TYPE aa_type_int IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
aa_int aa_type_int;

PROCEDURE print_first_and_last IS
BEGIN
DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);
END print_first_and_last;

BEGIN
aa_int(1) := 3;
aa_int(2) := 6;
aa_int(3) := 9;
aa_int(4) := 12;

DBMS_OUTPUT.PUT_LINE('Before deletions:');
print_first_and_last;

aa_int.DELETE(1);
aa_int.DELETE(4);

DBMS_OUTPUT.PUT_LINE('After deletions:');
print_first_and_last;
END;

Result:
Before deletions:
FIRST = 1
LAST = 4
After deletions:
FIRST = 2
LAST = 3

For Indexed Arrays, the first and last elements are those with the lowest and highest key values respectively. The values of the key are in sorted order.

The following example shows the FIRST and LAST values for Associative Arrays indexed by string, removes the first and last element and shows the FIRST and LAST values again.

DECLARE
TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
aa_str aa_type_str;

PROCEDURE print_first_and_last IS
BEGIN
DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_str.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_str.LAST);
END print_first_and_last;

BEGIN
aa_str('Z') := 26;
aa_str('A') := 1;
aa_str('K') := 11;
aa_str('R') := 18;

DBMS_OUTPUT.PUT_LINE('Before deletions:');
print_first_and_last;

aa_str.DELETE('A');
aa_str.DELETE('Z');

DBMS_OUTPUT.PUT_LINE('After deletions:');
print_first_and_last;
END;

Result:
Before deletions:
FIRST = A
LAST = Z
After deletions:
FIRST = K
LAST = R

Examples of FIRST and LAST methods for Varray

For not empty Varray, FIRST always returns 1.
For each varray LAST is always COUNT. For example.

DECLARE
TYPE NumList IS VARRAY(10) OF INTEGER;
n NumList := NumList(1,3,5,7);

PROCEDURE print_count_and_last IS
BEGIN
DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
END print_count_and_last;

BEGIN
print_count_and_last;

n.EXTEND(3);
print_count_and_last;

n.TRIM(5);
print_count_and_last;
END;

Result:
n.COUNT = 4, n.LAST = 4
n.COUNT = 7, n.LAST = 7
n.COUNT = 2, n.LAST = 2

The following example prints the Varray team using the FOR LOOP cycle with the team.FIRST and team.LAST limits. Since Varray is always limited, the team(i) inside the loop always exists.

DECLARE
TYPE team_type IS VARRAY(4) OF VARCHAR2(15);
team team_type;

PROCEDURE print_team (heading VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);

IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST. team.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i));
END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('---');
END;

BEGIN
print_team('Team Status:');

team := team_type(); -- The command is created, but there is nobody in it.
print_team('Team Status:');

team := team_type('John', 'Mary'); -- 2 members added to the team.
print_team('Initial Team:');

team := team_type('Arun', 'Amitha', 'Allan', 'Mae'); -- Team replacement.
print_team('New Team:');
END;
Result:
Team Status:
Does not exist
---
Team Status:
Has no members
---
Initial Team:
1. John
2. Mary
---
New Team:
1. Arun
2. Amitha
3. Allan
4. Mae
---

Examples of FIRST and LAST methods for Nested Tables

For Nested Tables, LAST is COUNT unless you remove items from the middle of the Nested Tables, in which case LAST is larger than COUNT.

Let’s look at an example which is printed by the Nested Tables team using the FOR LOOP loop in the team.FIRST and team.LAST ranges. Since Nested Tables can be unlimited, the FOR LOOP operator prints the team(i) only if team.EXISTS(i) has the value TRUE.

DECLARE
TYPE team_type IS TABLE OF VARCHAR2(15);
team team_type;

PROCEDURE print_team (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);

IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST. team.LAST LOOP
DBMS_OUTPUT.PUT (i || '. ');
IF team.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(team(i));
ELSE
DBMS_OUTPUT.PUT_LINE('(to be hired)');
END IF;
END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('---');
END;

BEGIN
print_team('Team Status:');

team := team_type(); -- The command is created, but there is nobody in it.
print_team('Team Status:');

team := team_type('Arun', 'Amitha', 'Allan', 'Mae'); -- Adds members.
print_team('Initial Team:');

team.DELETE(2,3); -- Removes the 2nd and 3rd members.
print_team('Current Team:');
END;
Result:
Team Status:
Does not exist
---
Team Status:
Has no members
---
Initial Team:
1. Arun
2. Amitha
3. Allan
4. Mae
---
Current Team:
1. Arun
2. (to be hired)
3. (to be hired)
4. Mae
---

PL/SQL tutorial: Collection Method FIRST & LAST in Oracle Database