Associative array is formerly known as PL/SQL tables in PL/SQL 2 (PL/SQL version which came with Oracle 7) and Index-by-Table in Oracle 8 Database. After Nested Table and VARRAYs, Associative Array is the third type of collection which is widely used by developers.

Let’s find out the answers of a few questions about associative array which would help you in understanding them better. In this section you will also find out some of the core differences & similarities between Associative Array and other collections such as VARRAY & Nested Tables.

Are Associative arrays bounded or Unbounded?

Similar to Nested tables, Associative arrays are unbounded form of collection. This means there is no upper bound on the number of elements that it can hold. Same is not true for VARRAYs as Variable arrays are bounded in nature.

Are Associative arrays persistent or non-persistent?

Unlike Nested Table & VARRAYs, Associative arrays are non-persistent form of collection. This means neither the array nor the data can be stored in the database but they are available in PL/SQL blocks only.

Are Associative arrays sparse or dense?

Whereas VARRAYs are densely populated arrays, Nested tables and Associative Arrays are sparsely populated arrays which mean that subscript numbering must be unique but not necessarily sequential.

Can we create Associative array as database object?

Because of their non-persistent nature Associative arrays cannot be stored into the schema. They can only be created in PL/SQL blocks but not at schema level as database object.

Can we reuse associative array?

As mentioned above Associative array is a non-persistent collection which cannot be created at schema level thus it cannot be stored into the schema hence it cannot be reused.

Is index numbering/Subscript numbering in Associative array implicit or explicit?

Unlike Nested Tables and VARRAYs, indexing in Associative array is Explicit. Where Oracle Engine assigns subscript/Index number to the elements of the Nested table and VARRAY collections implicitly in the background, in associative array users have to specify the index number explicitly while populating the collection.

How does Data gets stored into the Associative Array?

Associative array stores data in Key-Value pairs where index number serves as the key and data stored into the cell serves as the value.

These are a few core questions which you can expect in your exam or interview. Read along to find out the technical differences between Associative arrays and other collections.

Define PL/SQL Collection – Associative Array?
Using the information derived from above questions we can define Associative Arrays as one-dimensional, homogenous collection which stores data into key-value pair. It is sparse, unbounded and non-persistent in nature.

What is the Syntax of PL/SQL Associative Array?

TYPE aArray_name IS TABLE OF element_datatype [Not Null]
INDEX BY index_elements_datatype;

As said above Associative array is non-persistent type of collection thus it cannot be created as standalone database object hence cannot be reused like the rest of the other collections. It can only be available in PL/SQL block. Always make sure you create your associative array in DELCARATION section of your PL/SQL Block. [Read here to know how many sections are there in PL/SQL block?] Let’s see the syntax in detail:

Type: Keyword marks the beginning of the statement.

aArray_name: Name of the associative array. It is completely user-defined and complies with Oracle Database naming norms.

IS TABLE OF: Oracle Database reserved phrase using which user tells the compiler what type of elements the array is going to hold?

Element_Datatype: Datatype of the elements the array is going to hold. In Oracle Database all the collections are homogenous in nature, which means every element of the collection must be of the same datatype.

Not_null: An optional clause, which if used makes sure that every index has a value corresponding to it rather than a NULL.

INDEX BY: Clause using which user specifies the datatype of array’s subscript.

Index_elements_dataype: Datatype of the array’s subscript elements.

 

Example: How to Create Associative Array in Oracle Database?

Associative array can only be created inside a PL/SQL block thus its scope is limited to the block in which it is created which means it cannot be used outside that block. Let’s see how to create an Associative Array in Oracle Database?

Step 1: Create Associative Array

	SET SERVEROUTPUT ON;
	DECLARE
	    TYPE books IS TABLE OF NUMBER
	        INDEX BY VARCHAR2 (20);

In the above code we created an Associative array with the name ‘Books’ which can hold elements of NUMBER datatypes and subscript of VARCHAR2 datatype.

Step 2: Create Associative Array Variable

Isbn Books;

You need an Associative array variable for referencing the array in the program. Array variable can be created very easily. You just have to write the name of the variable (which is ‘isbn’ in our case) which is user defined followed by the name of the associative array.

Step 3: Insert Data into the Associative Array

As mentioned above Associative array holds data into key-value pairs. Thus unlike rest of the other collections the users have to insert both the subscript of the array (the key) and the data.

BEGIN
-- How to insert data into the associative array 
isbn('Oracle Database') := 1234;
isbn('MySQL') := 9876;

Like Nested table and VARRAYs we insert data into the Associative array in the execution section of PL/SQL block. If you noticed here unlike other collections we didn’t use the INSERT DML statement for inserting the data rather we inserted it using the Array variable ‘isbn’ . Below you can see the syntax of insert statement for associative array using array variable.

Array_variable (subscript/key) := data;

As you can see in order to insert the data into the associative array you first have to write the name of array variable followed by the array’s subscript and then the data for your array.

Step 4: How to update the data of collection – Associative array?

Updating values of Associative array is as easy as inserting them. If you want to change any value write the same statement which is used for insertion with the modified values. For example say you want to change the value against the key MySQL from 9876 to 1010 then you just write

	-- How to update data of associative array.	
	 	isbn('MySQL') := 1010;

Again you don’t need to write the UPDATE DML for updating the values. You simply use the array variable.

Step 5: How to retrieve data from the Collection- Associative array?

Just like we don’t need Insert DML statement for inserting values or Update DML for updating values similarly we don’t need Select DML for retrieving values.

Suppose you want to see the value stored against the key ‘Oracle Database’. For that you just need to write…

-- how to retrieve data using key from associative array.  	
	  DBMS_OUTPUT.PUT_LINE ('Value '||isbn ('Oracle Database'));

Let’s combine all these chunks of code into a single program.

SET SERVEROUTPUT ON;
DECLARE
    TYPE books IS TABLE OF NUMBER
        INDEX BY VARCHAR2(20);
    isbn Books;
BEGIN
    	-- How to insert data into the associative array 
    	isbn('Oracle Database') := 1234;
    	isbn('MySQL') := 9876;
    	DBMS_OUTPUT.PUT_LINE('Value Before Updation '||isbn('MySQL'));

    	-- How to update data of associative array.
    	isbn('MySQL') := 1010;
    
-- how to retrieve data using key from associative array.  
    	DBMS_OUTPUT.PUT_LINE('Value After Updation '||isbn('MySQL'));
END;
/

Here is the program with some minute modifications. The above PL/SQL program shows how to retrieve one specific value using the key. You can watch the Video Tutorial to learn how to retrieve all the values from Associative Array using Loops. There I have explained it in great detail.

Before winding up this tutorial, there are few pointers which I think you should know. These pointers are –

  • PL/SQL Associative Array support BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL, SIGNTYPE or VARCHAR2 as index datatype.
  • RAW, NUMBER, LONG-ROW, ROWID and CHAR are unsupported index datatypes.

In case of Element Datatype, PL/SQL collection Associative Array Supports –

  • PL/SQL scalar data type: DATE, BLOB, CLOB, BOOLEAN or NUMBER & VARCHAR2 with their subtypes.
  • Inferred data: Term used for such data types that are inherited from a table column, cursor expression or predefined package variable
  • User-defined type: An object type or collection type which is user defined.

That is a detailed tutorial on PL/SQL Collection – Associative Array. This tutorial covers all the topics which you can expect in Oracle Database Certification Exam as well as in Interview. Hope you enjoyed reading.

You can help others in learning something new as well as help us in reaching out to more people by sharing this blog on your Social networks. Don’t forget to tag us because we love giving loud Shout-outs to all our supporters, viewers and subscribers. Thanks & have a great day!

RELATED ARTICLESMORE FROM AUTHOR