Associative Array FAQS

1. What is an Associative Array in Oracle?

An Associative Array (formerly called Index-By Table) is a PL/SQL collection type that stores key-value pairs.

• Keys can be PL/SQL integer or string (VARCHAR2).
• Values can be any PL/SQL type (scalar, record, object).
• It is sparse, meaning indexes do not need to be sequential.
• Exists only in PL/SQL memory; cannot be stored in the database.

2. How do you declare an Associative Array?

Syntax:

DECLARE

   TYPE emp_tab_type IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;

   emp_tab emp_tab_type;

BEGIN

   NULL;

END;

• PLS_INTEGER or VARCHAR2 can be used as index type.
• emp_tab is the variable of the collection.

3. How do you assign values to an Associative Array?

DECLARE

   TYPE emp_tab_type IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;

   emp_tab emp_tab_type;

BEGIN

   emp_tab(1) := 'Alice';

   emp_tab(2) := 'Bob';

   DBMS_OUTPUT.PUT_LINE(emp_tab(1)); -- Outputs 'Alice'

END;

• Keys do not need to be sequential; you can have emp_tab(100) := 'Eve';

4. How do you loop through an Associative Array?

Using the indices:

DECLARE

   TYPE emp_tab_type IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;

   emp_tab emp_tab_type;

   idx PLS_INTEGER;

BEGIN

   emp_tab(1) := 'Alice';

   emp_tab(3) := 'Bob';

  

   idx := emp_tab.FIRST;

   WHILE idx IS NOT NULL LOOP

      DBMS_OUTPUT.PUT_LINE(emp_tab(idx));

      idx := emp_tab.NEXT(idx);

   END LOOP;

END;

• Use FIRST, NEXT, LAST, PRIOR for iteration.
• Sparse arrays are supported.

5. Can Associative Arrays be indexed by strings?

Yes. Example:

DECLARE

   TYPE emp_tab_type IS TABLE OF NUMBER INDEX BY VARCHAR2(20);

   emp_tab emp_tab_type;

BEGIN

   emp_tab('Alice') := 5000;

   emp_tab('Bob') := 6000;

 

   DBMS_OUTPUT.PUT_LINE(emp_tab('Alice')); -- 5000

END;

• Useful for key-value lookups by name or code.

6. Can Associative Arrays be used with cursors?

Yes. You can bulk collect query results into an associative array:

DECLARE

   TYPE emp_tab_type IS TABLE OF employees.salary%TYPE INDEX BY PLS_INTEGER;

   emp_salaries emp_tab_type;

BEGIN

   SELECT salary

   BULK COLLECT INTO emp_salaries

   FROM employees

   WHERE department_id = 10;

 

   FOR i IN emp_salaries.FIRST .. emp_salaries.LAST LOOP

      DBMS_OUTPUT.PUT_LINE(emp_salaries(i));

   END LOOP;

END;

• Efficient for row-by-row processing in memory.

7. What are common methods/properties of an Associative Array?

Method/Property	Description
FIRST	Returns the first index
LAST	Returns the last index
NEXT(n)	Returns the next index after n
PRIOR(n)	Returns the previous index before n
EXISTS(n)	Checks if key n exists
DELETE(n)	Deletes element at key n
DELETE	Deletes all elements

8. What are the advantages of Associative Arrays?

• Fast in-memory lookup using keys
• Sparse arrays supported
• Flexible indexing: integers or strings
• Works well with bulk processing (BULK COLLECT)
• No need to predefine size

9. Common mistakes when using Associative Arrays

• Trying to store them directly in database columns (not allowed)
• Using sequential loops assuming contiguous indexes (sparse arrays may skip indexes)
• Forgetting to check EXISTS before accessing a key
• Using COUNT incorrectly (does not guarantee contiguous keys)

10. Best practices

• Use integer indices for numeric loops, strings for key-value lookups
• Use BULK COLLECT for performance with queries
• Use EXISTS before accessing a key
• Prefer associative arrays for temporary in-memory storage and lookups