10.2 Characteristics of PL/SQL Tables

A definition worth repeating: A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers.

Let's examine each of these characteristics in detail:

One-dimensional

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array. You cannot define a PL/SQL table so that it can be referenced as follows:

my_table (10, 44)

This is a two-dimensional structure and not currently supported.

Unbounded or Unconstrained

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.

Sparse

In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.

In contrast, an array is a dense data structure. When you declare an array, all cells in the array are allocated in memory and are ready to use.

Homogeneous elements

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.

Indexed by integers

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -2 ³¹ -1 to 2 ³¹ -1, so you have an awful lot of rows with which to work.[ 1 ]

[1] Notice that the index of a row in the table can even be negative. In the PL/SQL table, a negative row number is no different from a positive one; it's just another integer. However, there are ways that you, the programmer, can take advantage of differences between positive and negative row numbers in your application. For example, the ps_global package, included on the companion disk, uses rows with positive numbers to store "globals-by-number" and uses negative row values to store "globals-by-name".

Because the row number does not have to be used sequentially and has such enormous range, you can use this integer index in interesting ways. For example, the row number for a PL/SQL table could be the primary key of your company table, so that:

company_name (14055)

contains the name of the company whose company_id = 14055.