1. Overview A collection is a group of elements arranged in a defined order, where each one of them can be accessed through its index. The most common forms of collections are arrays, maps or lists. In current version of programming languages, almost all of them support the use of collections. 2. Introduction Oracle supports Collections in three forms, namely, Associative Arrays, Nested Tables and Varrays. The article explains the use of Associative Arrays in PL/SQL. Associative arrays alias Index By tables are non persistent form of collections in PL/SQL, where their life is a single database session. Note than non persistent objects are not physically stored in the database, therefore they can only be used within PL/SQL. It can be defined as a one dimensional array variable fragmented in the “Key-Value” format, which can hold more than one element (value) each acquiring a memory location. The key or element index can belong to number family or can be a string also in Oracle versions 9iR2 and above. For numeric index keys, the range is -2147483647 to 2147483647. Data in the elements does not have to be stored in consecutive rows. It can exist in sparse. 3. Syntax Code (Text): TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)]; Prior to Oracle 9iR2, only BINARY_INTEGER was the admissible index type. In later versions of Oracle, index key can be of BINARY_INTEGER, PLS_INTEGER, VARCHAR2 or their subtype’s type. Restrictions: RAW, LONG RAW, ROWID, and CHAR are not allowed as key types for an associative array. Example 1: Index-by tables (Associative arrays) Associative arrays can be initialized by assigning values corresponding to an index. In the below PL/SQL block, elements corresponding to cell index 1,20,12of associative array DTAB are initialized with numeric values. Code (SQL): DECLARE TYPE CLUB_TAB IS TABLE OF NUMBER INDEX BY PLS_INTEGER; DTAB CLUB_TAB; RESULT NUMBER; BEGIN DTAB(1) := 5 ; DTAB(20) := 10 ; DTAB(12) := 15 ; RESULT := DTAB(1) + DTAB(20) + DTAB(12); DBMS_OUTPUT.PUT_LINE(RESULT); END; / 30 PL/SQL PROCEDURE successfully completed. Example 2: The PL/SQL block below uses string as the index and assigns their quarter sales as element value. Code (SQL): DECLARE TYPE CLUB_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(100); DTAB CLUB_TAB; L_COMP VARCHAR2(100) := 'TCS'; BEGIN DTAB('INFOSYS') := 5000; DTAB('TCS') := 84020 ; DTAB('CTS') := 18300; DTAB('ORACLE') := 90010; DTAB('SUN') := 43000 ; DTAB('ICICI') := 2000; DBMS_OUTPUT.PUT_LINE(DTAB(L_COMP)); END; / 84020 PL/SQL PROCEDURE successfully completed. 4. Associative Array Methods Below table shows the methods which can be used with the associative arrays to access index and values. [TABLE]Method Description EXISTS(n) Returns TRUE if the nth element in a PL/SQL table exists COUNT Returns the number of elements that a PL/SQL table currently contains FIRST | LAST Returns the first and last (smallest and largest) index number in a PL/SQL table. Returns NULL , if it is empty PRIOR (n) Returns the index number that precedes index in a PL/SQL table NEXT (n) Returns the index number that succeeds index in a PL/SQL table DELETE DELETE removes all elements from the table. DELETE (n) removes n elements from the table. DELETE (m,n) removes all elements in range from m..n from the table[/TABLE] Syntax for using a collection method: Code (Text): collection_name.method_name[(parameters)] Restrictions 1. Collection methods are not callable from SQL statements. 2. No method can be used with a NULL collection except EXISTS. Oracle raises COLLECTION_IS_NULL exception if any method is used with NULL collection 4.1. Examples The PL/SQL block below shows the use of FIRST and LAST collection methods. Code (SQL): DECLARE TYPE CLUB_NEST IS TABLE OF NUMBER; TYPE CLUB_TAB IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(100); DTAB1 CLUB_NEST := CLUB_NEST(1986,2010); DTAB2 CLUB_TAB; BEGIN DTAB2('Club') := 5000; DTAB2('Oracle') := 1000; DBMS_OUTPUT.PUT_LINE( 'Index for First element in CLUB_TAB: '||DTAB2.FIRST) ; DBMS_OUTPUT.PUT_LINE( 'Index for Last element in CLUB_TAB: '||DTAB2.LAST) ; FOR I IN DTAB1.FIRST..DTAB1.LAST LOOP DBMS_OUTPUT.PUT_LINE('Index for element '||to_char(i)||' is:'|| to_char(DTAB1(I)) ) ; END LOOP; END; / INDEX FOR FIRST element IN CLUB_TAB: Club INDEX FOR LAST element IN CLUB_TAB: Oracle INDEX FOR element 1 IS:1986 INDEX FOR element 2 IS:2010 PL/SQL PROCEDURE successfully completed.