Nested Table Methods FAQS

1. What is a Nested Table in Oracle?

A Nested Table is a collection type in PL/SQL that allows you to store multiple values of the same type in a single variable.

• Can grow dynamically
• Can be stored in database columns
• Unlike associative arrays, indices are always sequential
• Supports set operations (MULTISET operators)

2. How do you declare a Nested Table?

DECLARE

   TYPE emp_table_type IS TABLE OF VARCHAR2(50);

   emp_names emp_table_type := emp_table_type(); -- initialize

BEGIN

   NULL;

END;

• Must initialize using () before adding elements.

3. How do you assign values?

BEGIN

   emp_names.EXTEND;

   emp_names(1) := 'Alice';

   emp_names.EXTEND(2);

   emp_names(2) := 'Bob';

END;

• EXTEND increases the size of the nested table.
• Indices are numeric and contiguous.

4. How do you access elements?

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

DBMS_OUTPUT.PUT_LINE(emp_names(2)); -- Bob

• Attempting to access nonexistent index raises SUBSCRIPT_BEYOND_COUNT.

5. How do you delete elements?

emp_names.DELETE(1);   -- Deletes element at index 1

emp_names.DELETE;      -- Deletes all elements

• DELETE(index) removes a single element
• DELETE without index removes all elements
• Remaining elements shift indices automatically for sequential access

6. Common methods/properties of Nested Tables

Method/Property	Description
EXTEND(n)	Increase the size by n (default 1)
TRIM(n)	Removes n elements from the end (default 1)
DELETE(n)	Delete specific element(s)
DELETE	Delete all elements
COUNT	Returns number of elements
FIRST	Returns first index
LAST	Returns last index
PRIOR(n)	Returns previous index before n
NEXT(n)	Returns next index after n

7. Example using methods

DECLARE

   TYPE emp_table_type IS TABLE OF VARCHAR2(50);

   emp_names emp_table_type := emp_table_type();

   i PLS_INTEGER;

BEGIN

   emp_names.EXTEND(3);

   emp_names(1) := 'Alice';

   emp_names(2) := 'Bob';

   emp_names(3) := 'Charlie';

 

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

 

   i := emp_names.FIRST;

   WHILE i IS NOT NULL LOOP

      DBMS_OUTPUT.PUT_LINE('Index ' || i || ': ' || emp_names(i));

      i := emp_names.NEXT(i);

   END LOOP;

 

   emp_names.DELETE(2); -- Deletes Bob

   DBMS_OUTPUT.PUT_LINE('After deletion, count: ' || emp_names.COUNT);

END;

• Demonstrates EXTEND, COUNT, FIRST, NEXT, DELETE.
• Indices are contiguous and shift after deletion.

8. Advantages of Nested Tables

• Dynamic size
• Can be stored in database tables
• Supports set operations (MULTISET UNION, INTERSECT, EXCEPT)
• Can be manipulated in PL/SQL efficiently

9. Limitations

• Cannot use sparse indices – must use contiguous numeric indices
• Must initialize before adding elements
• Performance can degrade if table grows very large in memory

10. Best practices

• Always initialize nested tables before use
• Use EXTEND and TRIM for dynamic resizing
• Use COUNT before accessing indices to avoid errors
• Use BULK COLLECT and FORALL for efficient multi-row operations
• Use DELETE carefully to maintain contiguous indices