Associative Array Methods FAQS

1. What is an Associative Array in Oracle?

An Associative Array (formerly called PL/SQL table or index-by table) is a key-value collection in PL/SQL.

• Indexed by PL/SQL scalar type: NUMBER, VARCHAR2, or BINARY_INTEGER
• Sparse collection – elements do not need contiguous indices
• Grows dynamically as elements are added
• Often used for lookup tables or in-memory caching

2. How do you declare an Associative Array?

DECLARE

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

   emp_names emp_table_type;

BEGIN

   NULL;

END;

• INDEX BY specifies the type of key (numeric or string).

3. How do you assign values?

BEGIN

   emp_names(101) := 'Alice';

   emp_names(102) := 'Bob';

END;

• Keys do not need to be sequential.
• Can overwrite an existing key’s value.

4. How do you access elements?

DBMS_OUTPUT.PUT_LINE(emp_names(101)); -- Alice

• Attempting to access a nonexistent key raises NO_DATA_FOUND error.

5. How do you delete elements?

emp_names.DELETE(101);   -- Delete specific element

emp_names.DELETE;        -- Delete all elements

• DELETE(key) removes a single element
• DELETE without key removes all elements

6. Common methods/properties of Associative Arrays

Method/Property	Description
COUNT	Number of elements in the array
EXISTS(key)	Returns TRUE if key exists
DELETE(key)	Deletes element by key
DELETE	Deletes all elements
FIRST	Returns the lowest key
LAST	Returns the highest key
NEXT(key)	Returns the next key after given key
PRIOR(key)	Returns the previous key before given key

7. Example using methods

DECLARE

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

   emp_names emp_table_type;

   k PLS_INTEGER;

BEGIN

   emp_names(101) := 'Alice';

   emp_names(103) := 'Bob';

   emp_names(105) := 'Charlie';

 

   DBMS_OUTPUT.PUT_LINE('Total elements: ' || emp_names.COUNT);

 

   k := emp_names.FIRST;

   WHILE k IS NOT NULL LOOP

      DBMS_OUTPUT.PUT_LINE('Key ' || k || ': ' || emp_names(k));

      k := emp_names.NEXT(k);

   END LOOP;

 

   emp_names.DELETE(103); -- Delete Bob

END;

• Demonstrates COUNT, FIRST, NEXT, DELETE.
• Supports sparse keys (101, 103, 105).

8. Advantages of Associative Arrays

• Dynamic and sparse – no fixed size
• Fast key-based lookups in memory
• Simple syntax for adding, deleting, and iterating
• Can use string or number keys

9. Limitations

• Cannot be stored directly in database tables (PL/SQL only)
• Cannot use SQL queries directly on associative arrays
• Only exists in PL/SQL memory scope

10. Best practices

• Use for in-memory lookup tables or temporary caching
• Use EXISTS before accessing an element to avoid NO_DATA_FOUND
• Use FIRST, NEXT, PRIOR for ordered iteration
• Delete elements to free memory if collection is large