Oracle PL/SQL - FIRST and LAST Methods for Varray

Introduction

For a varray that is not empty, FIRST always returns 1. For every varray, LAST always equals COUNT.

The following code prints the varray team using a FOR LOOP statement with the bounds team.FIRST and team.LAST.

Because a varray is always dense, team(i) inside the loop always exists.

Demo

SQL>
SQL>-- w  ww .  j  av a2  s .  co  m
SQL> DECLARE
  2    TYPE team_type IS VARRAY(4) OF VARCHAR2(15);
  3    team team_type;
  4
  5    PROCEDURE print_team (heading VARCHAR2)
  6    IS
  7    BEGIN
  8      DBMS_OUTPUT.PUT_LINE(heading);
  9
 10      IF team IS NULL THEN
 11        DBMS_OUTPUT.PUT_LINE('Does not exist');
 12      ELSIF team.FIRST IS NULL THEN
 13        DBMS_OUTPUT.PUT_LINE('Has no members');
 14      ELSE
 15        FOR i IN team.FIRST..team.LAST LOOP
 16          DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i));
 17        END LOOP;
 18      END IF;
 19    END;
 20
 21  BEGIN
 22    print_team('Team Status:');
 23
 24    team := team_type();  -- Team is funded, but nobody is on it.
 25    print_team('Team Status:');
 26
 27    team := team_type('A', 'B');  -- Put 2 members on team.
 28    print_team('Initial Team:');
 29    team := team_type('A', 'B', 'C', 'D');  -- Change team.
 30    print_team('New Team:');
 31  END;
 32  /
Team Status:
Does not exist
Team Status:
Has no members
Initial Team:
1. A
2. B
New Team:
1. A
2. B
3. C
4. D

PL/SQL procedure successfully completed.

SQL>