Quick Search:
 The Oracle ARRAY Data Type      [Return To Index] Jump to:  


An array (also called a collection) is a set of ordered data items that can be thought of as a numbered list, with each item being of the same type. Each item in an array is referred to as an element, and each element has an index which is a number corresponding to the element's position in the array. The index-to-element relationship can be thought of as a key/value pair.

Oracle provides three types of PL/SQL collections: associative arrays, nested tables, and varrays.

  1. Associative arrays (also referred to as index-by tables) let you access elements using numbers or strings for the index values. Associative arrays are useful for small to medium sized lookup tables where the array can be constructed in memory each time a procedure is called. There is no fixed limit on their size and their index values are more flexible- associative array keys can be negative and/or nonsequential, and associative arrays can use string values instead of numbers if appropriate.

  2. Nested tables hold an arbitrary number of elements and use sequential numbers as the index or key to the elements. Nested tables can be stored in database tables and manipulated through SQL. They are appropriate for data relationships that must be stored persistently. Nested tables are flexible in that arbitrary elements can be deleted, rather than just removing an element from the end. Note that the order and subscripts (keys) of nested tables are not preserved as the table is stored and retrieved in the database.

  3. Varrays hold a fixed number of elements, although the number of elements can be changed at runtime. Like nested tables, varrays use sequential numbers as the index or key to the elements. You can define equivalent SQL types, allowing varrays to be stored in database tables. Varrays are a good choice when the number of elements is known in advance, and when the elements are likely to be accessed in sequence.

This is an example associative array named "People" containing 5 elements:

Index    Element
1       john
2       mary
3       frank
4       jane
5       william

In this array "People", the element "frank" would be accessed by referring to it as


All Oracle PL/SQL collections have a variety of built-in methods or functions that can be used to navigate or manipulate them, as detailed in the table below.

Method Action Performed
COUNTReturns number of elements in the array
EXISTSReturns Boolean true if the element at the specified index exists; otherwise returns false
EXTENDIncreases size of array by 1 or by the number specified, ie. EXTEND(n)
Cannot be used with associative arrays
FIRSTNavigates to the first element in the array
NEXTNavigates to the next element in the array
LASTNavigates to the last element in the array
PRIORNavigates to the previous element in the array
TRIMRemoves the last element of the array, or the last n elements if a number is specified, ie. TRIM(n)
Cannot be used with associative arrays
DELETERemoves all elements of an array, or the nth element, if a parameter is specified

A VARRAY is an array of varying size. It has an ordered set of data elements, and all the elements are of the same data type. The number of elements in a VARRAY is the "size" of the VARRAY. You must specify a maximum size (but not a minimum size) when you declare the VARRAY type.

In general, the VARRAY type should be used when the number of items to be stored is small; it is not suitable for large numbers of items or elements. Note that you cannot index or constrain VARRAY values.

Related Links:

Related Code Snippets:
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org