1. What is an Associative Array in Oracle?
An Associative Array (also called Index-by Table) is a PL/SQL collection type that stores elements as key-value pairs.
- Each element has a unique index (key).
- Keys can be INTEGER or VARCHAR2.
- The collection can grow dynamically; no fixed size is required.
2. How is it different from VARRAYs or Nested Tables?
|
Feature |
Associative Array |
VARRAY |
Nested Table |
|
Size |
Unlimited |
Fixed max size |
Can grow dynamically |
|
Index type |
INTEGER or VARCHAR2 |
Integer (1..N) |
Integer (1..N) |
|
Storage |
PL/SQL only |
Inline in table or stored separately |
Stored out-of-line |
|
Order |
Not guaranteed |
Maintains order |
Order maintained |
|
SQL Usage |
PL/SQL only |
Can be stored in table |
Can be stored in table |
Use Associative Arrays for fast lookups and dynamic data in PL/SQL.
3. How do you declare an Associative Array?
Example with INTEGER keys:
DECLARE
TYPE emp_tab_type IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
employees emp_tab_type;
BEGIN
employees(1) := 'Alice';
employees(2) := 'Bob';
DBMS_OUTPUT.PUT_LINE(employees(1)); -- Output: Alice
END;
/
Example with VARCHAR2 keys:
DECLARE
TYPE emp_tab_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
salaries emp_tab_type;
BEGIN
salaries('Alice') := 5000;
salaries('Bob') := 4500;
DBMS_OUTPUT.PUT_LINE(salaries('Bob')); -- Output: 4500
END;
/
4. How do you loop through an Associative Array?
For INTEGER keys:
FOR i IN employees.FIRST .. employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || employees(i));
END LOOP;
For non-sequential keys:
DECLARE
idx VARCHAR2(10);
BEGIN
idx := salaries.FIRST;
WHILE idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(idx || ': ' || salaries(idx));
idx := salaries.NEXT(idx);
END LOOP;
END;
/
Use FIRST, LAST, NEXT, PRIOR to navigate associative arrays.
5. Can Associative Arrays store different data types?
No, all elements in a single associative array must have the same data type.
- You can use OBJECT types if you need multiple fields per element.
Example with objects:
CREATE TYPE emp_obj AS OBJECT (
name VARCHAR2(50),
salary NUMBER
);
DECLARE
TYPE emp_tab_type IS TABLE OF emp_obj INDEX BY PLS_INTEGER;
employees emp_tab_type;
BEGIN
employees(1) := emp_obj('Alice', 5000);
DBMS_OUTPUT.PUT_LINE(employees(1).name || ': ' || employees(1).salary);
END;
/
6. Can Associative Arrays be stored in database tables?
No, Associative Arrays exist only in PL/SQL.
- Use VARRAYs or Nested Tables if you need to store collections in database tables.
7. How do you delete elements from an Associative Array?
- Delete a single element: collection.DELETE(key);
- Delete all elements: collection.DELETE;
Example:
employees.DELETE(1); -- Deletes element with key 1
employees.DELETE; -- Deletes all elements
8. Advantages of Associative Arrays
- Fast lookups using keys.
- Dynamic size; grows as needed.
- Supports non-sequential keys (like a dictionary).
- Useful for temporary data processing in PL/SQL.
9. Limitations of Associative Arrays
- Cannot be used directly in SQL.
- Not stored in tables (PL/SQL only).
- Only one data type per array; use OBJECT types for multiple fields.
10. When should I use Associative Arrays?
- Temporary storage of key-value pairs in PL/SQL.
- Fast in-memory lookups or caching.
- Storing dynamic data whose size is unknown.