Nested Table FAQS

1. What is a Nested Table in Oracle?

A Nested Table is a collection type in PL/SQL that allows storing multiple elements of the same type in a single variable.

• Unlike associative arrays, nested tables can be stored in database columns.
• They are dense by default but can become sparse after deletions.
• Can hold scalars, records, or objects.
• Supports SQL operations like TABLE() for querying.

2. How do you declare a Nested Table?

PL/SQL declaration:

DECLARE

   TYPE num_table_type IS TABLE OF NUMBER;

   nums num_table_type;

BEGIN

   NULL;

END;

SQL declaration for table column:

CREATE TYPE num_table_type AS TABLE OF NUMBER;

/

CREATE TABLE dept_table (

   dept_id NUMBER,

   salaries num_table_type

) NESTED TABLE salaries STORE AS salaries_nt;

3. How do you assign values to a Nested Table?

DECLARE

   TYPE num_table_type IS TABLE OF NUMBER;

   nums num_table_type := num_table_type(10, 20, 30);

BEGIN

   DBMS_OUTPUT.PUT_LINE(nums(1)); -- 10

END;

• Elements are 1-based indexed.

4. How do you add or remove elements?

DECLARE

   TYPE num_table_type IS TABLE OF NUMBER;

   nums num_table_type := num_table_type();

BEGIN

   nums.EXTEND;        -- Adds one element

   nums(1) := 100;

   nums.EXTEND(2);     -- Adds 2 more elements

   nums(2) := 200;

   nums(3) := 300;

   nums.DELETE(2);     -- Deletes element at index 2

   DBMS_OUTPUT.PUT_LINE(nums.COUNT); -- 2 elements remain

END;

• Use EXTEND to grow, DELETE to remove, TRIM to remove from end.

5. How do you loop through a Nested Table?

DECLARE

   TYPE num_table_type IS TABLE OF NUMBER;

   nums num_table_type := num_table_type(10, 20, 30);

   i PLS_INTEGER;

BEGIN

   FOR i IN 1 .. nums.COUNT LOOP

      DBMS_OUTPUT.PUT_LINE(nums(i));

   END LOOP;

END;

• COUNT gives the number of elements.
• Be careful: deleted elements create gaps, may need FIRST/NEXT iteration.

6. Can Nested Tables be used in SQL queries?

Yes, with the TABLE() operator:

DECLARE

   TYPE num_table_type IS TABLE OF NUMBER;

   nums num_table_type := num_table_type(10, 20, 30);

BEGIN

   FOR rec IN (SELECT * FROM TABLE(nums)) LOOP

      DBMS_OUTPUT.PUT_LINE(rec.COLUMN_VALUE);

   END LOOP;

END;

• COLUMN_VALUE is the default name for elements in SQL context.

7. What are common methods/properties?

Method/Property	Description
EXTEND(n)	Add n empty elements at end
DELETE(n)	Delete element at index n
DELETE	Delete all elements
TRIM(n)	Remove last n elements
COUNT	Number of elements in table
FIRST / LAST	Returns first/last index
NEXT / PRIOR	Returns next/prior index

8. Can Nested Tables be sparse?

Yes, after using DELETE on specific elements, indices may have gaps.

nums.DELETE(2);

-- Index 2 is now empty

• Iteration using 1..COUNT may need adjustment. Use FIRST / NEXT for safe traversal.

9. Advantages of Nested Tables

• Can store multiple values in a single column or variable
• Can be queried in SQL using TABLE()
• Dense or sparse storage supported
• Useful for bulk operations and PL/SQL collections
• Can store scalars, records, or objects

10. Common mistakes

• Assuming indices remain contiguous after deletions
• Forgetting to EXTEND before assigning values dynamically
• Confusing COUNT with total allocated elements (COUNT ignores deleted ones)
• Trying to use them in SQL without TABLE()

11. Best practices

• Use EXTEND and DELETE carefully
• Use BULK COLLECT for efficient fetching into nested tables
• For SQL querying, always use TABLE() operator
• Use FIRST/NEXT iteration for sparse nested tables