Working with Collections in Oracle Options

codeling Posts: 1085 Points: 4548
Posted: Monday, January 25, 2016 10:02:37 AM

Oracle PL/SQL provides the datatypes Table and VARRAY to allow you declare index-by tables, nested tables and variable-size arrays:

  • Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
  • Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
  • Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

In this topic, you learn how those types let you reference and manipulate collections of data as whole objects. 

codeling Posts: 1085 Points: 4548
Posted: Monday, January 25, 2016 10:04:52 AM

Associative Arrays (Index-By Tables)

Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.

Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique, either by using the primary key from a SQL table, or by concatenating strings together to form a unique value.

For example, here is the declaration of an associative array type, and two arrays of that type, using keys that are strings:

  country_population population_type;
  continent_population population_type;
  howmany NUMBER;
  which VARCHAR2(64)

  country_population('Greenland') := 100000;
  country_population('Iceland') := 750000;
  howmany := country_population('Greenland');

  continent_population('Australia') := 30000000;
  continent_population('Antarctica') := 1000; -- Creates new entry
  continent_population('Antarctica') := 1001; -- Replaces previous value
  which := continent_population.FIRST; -- Returns 'Antarctica'
-- as that comes first alphabetically.
  which := continent_population.LAST; -- Returns 'Australia'
  howmany := continent_population(continent_population.LAST);
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.

Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. It is like a simple version of a SQL table where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.

Because associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body.

codeling Posts: 1085 Points: 4548
Posted: Monday, January 25, 2016 10:17:04 AM

Nested Tables

PL/SQL nested tables are like one-dimensional arrays. Within the database, they can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.

Nested tables differ from arrays in two important ways:

  • Arrays have a fixed upper bound, but nested tables are unbounded. So, the size of a nested table can increase dynamically.


  • Arrays must be dense (have consecutive subscripts). So, you cannot delete individual elements from an array. Initially, nested tables are dense, but they can become sparse (have nonconsecutive subscripts). So, you can delete elements from a nested table using the built-in procedure DELETE. That might leave gaps in the index, but the built-in function NEXT lets you iterate over any series of subscripts.

codeling Posts: 1085 Points: 4548
Posted: Monday, January 25, 2016 10:44:49 AM


Items of type VARRAY are called varrays. A varray has a maximum size, which you must specify in its type definition. To reference an element in a varray, you use standard subscripting syntax. Its index has a fixed lower bound of 1 and an extensible upper bound. For example, Grade(3) references the third element in varray Grades.

In the above example, the current upper bound for varray Grades is 7, but you can extend it to 8, 9, 10, and so on. Thus, a varray can contain a varying number of elements, from zero (when empty) to the maximum specified in its type definition.

Users browsing this topic