How to use array in oracle database...

Definition:
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.
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.

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.

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 

People[3]


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
COUNT	Returns number of elements in the array
EXISTS	Returns Boolean true if the element at the specified index exists; otherwise returns false
EXTEND	Increases size of array by 1 or by the number specified, ie. EXTEND(n)
Cannot be used with associative arrays
FIRST	Navigates to the first element in the array
NEXT	Navigates to the next element in the array
LAST	Navigates to the last element in the array
PRIOR	Navigates to the previous element in the array
TRIM	Removes 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
DELETE	Removes 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.

// create command and set properties

OracleCommand cmd = 
con.CreateCommand();
      
// the sql text used to insert the 
// rows in the arrays and this 
// necessarily uses bind variables

cmd.CommandText =  "insert into 
  jobs (job_id, " +
        "job_title, " +
        "min_salary, " +
        "max_salary) " +
        "values (:1, :2, :3, :4)";

// set the number of elements 
// in the arrays and all three 
// arrays are the same size

cmd.ArrayBindCount = job_id_vals.Length;
// add parameters to collection

cmd.Parameters.Add(p_job_id);
cmd.Parameters.Add(p_job_title);
cmd.Parameters.Add(p_min_salary);
cmd.Parameters.Add(p_max_salary);

// perform the array insert in 
// a single call

cmd.ExecuteNonQuery();