While learning about Nested Tables, I came across a statement that even if you delete a value from a Nested Table, the index still exists but is assigned with NULL. Is this true?

This below code does not give exact output when run on SQL Developer. Instead of giving this output : Index 3 exists but no value assigned. it gives me : Index 3 does not exist. I am not convinced - how come it is not matching with above statement which it says.

DECLARE
   TYPE nt_type IS TABLE OF NUMBER;
   nt nt_type := nt_type(10, 20, 30, 40, 50);
BEGIN
   DBMS_OUTPUT.PUT_LINE('Before DELETE: Count = ' || nt.COUNT); -- Output: 5

   nt.DELETE(3);  -- Deletes the 3rd element (value 30)

   DBMS_OUTPUT.PUT_LINE('After DELETE: Count = ' || nt.COUNT); -- Output: 4

   IF nt.EXISTS(3) THEN
      DBMS_OUTPUT.PUT_LINE('Index 3 exists but no value assigned.');
   ELSE  
      DBMS_OUTPUT.PUT_LINE('Index 3 does not exist.');
   END IF;
   
   -- Display all elements including sparse indexes
   FOR i IN 1..nt.LAST LOOP
      IF nt.EXISTS(i) THEN
         DBMS_OUTPUT.PUT_LINE('Element at index ' || i || ' = ' || COALESCE(TO_CHAR(nt(i)), 'NULL'));
      END IF;
   END LOOP;
END;
/

Expected Output :

Before DELETE: Count = 5  
After DELETE: Count = 4  
Index 3 exists but no value assigned.  
Element at index 1 = 10  
Element at index 2 = 20  
Element at index 3 = NULL  
Element at index 4 = 40  
Element at index 5 = 50  

My SQL Developer output

Before DELETE: Count = 5
After DELETE: Count = 4
Index 3 does not exist.
Element at index 1 = 10
Element at index 2 = 20
Element at index 4 = 40
Element at index 5 = 50

While learning about Nested Tables, I came across a statement that even if you delete a value from a Nested Table, the index still exists but is assigned with NULL. Is this true?

This below code does not give exact output when run on SQL Developer. Instead of giving this output : Index 3 exists but no value assigned. it gives me : Index 3 does not exist. I am not convinced - how come it is not matching with above statement which it says.

DECLARE
   TYPE nt_type IS TABLE OF NUMBER;
   nt nt_type := nt_type(10, 20, 30, 40, 50);
BEGIN
   DBMS_OUTPUT.PUT_LINE('Before DELETE: Count = ' || nt.COUNT); -- Output: 5

   nt.DELETE(3);  -- Deletes the 3rd element (value 30)

   DBMS_OUTPUT.PUT_LINE('After DELETE: Count = ' || nt.COUNT); -- Output: 4

   IF nt.EXISTS(3) THEN
      DBMS_OUTPUT.PUT_LINE('Index 3 exists but no value assigned.');
   ELSE  
      DBMS_OUTPUT.PUT_LINE('Index 3 does not exist.');
   END IF;
   
   -- Display all elements including sparse indexes
   FOR i IN 1..nt.LAST LOOP
      IF nt.EXISTS(i) THEN
         DBMS_OUTPUT.PUT_LINE('Element at index ' || i || ' = ' || COALESCE(TO_CHAR(nt(i)), 'NULL'));
      END IF;
   END LOOP;
END;
/

Expected Output :

Before DELETE: Count = 5  
After DELETE: Count = 4  
Index 3 exists but no value assigned.  
Element at index 1 = 10  
Element at index 2 = 20  
Element at index 3 = NULL  
Element at index 4 = 40  
Element at index 5 = 50  

My SQL Developer output

Before DELETE: Count = 5
After DELETE: Count = 4
Index 3 does not exist.
Element at index 1 = 10
Element at index 2 = 20
Element at index 4 = 40
Element at index 5 = 50

• oracle-database
• plsql
• plsqldeveloper

• Your testing shows what happens, and that matches what the documentation says. You haven't said where you found the statement so we can't say why that disagreed or if you just misinterpreted it. – Alex Poole Commented Feb 1 at 19:54

2 Answers 2

When you delete an element from a nested table collection, it marks that position as "non-existent" (which is not the same as existing but holding a NULL value), but does not internally deallocate the memory. Thus, you can reassign a value to that same position without first calling the .extend method, assuming you deleted off the end of the collection. But once you get to a position that had never been allocated at all, you cannot assign to it without first .extending the collection, as that's what assigns the memory.

Note that these are all different:

DECLARE
   TYPE nt_type IS TABLE OF NUMBER;
   nt nt_type := nt_type(10, NULL, 30);
BEGIN
   nt.delete(3); -- delete the 3rd element
   
   dbms_output.put_line(nt(1)); -- 10
   dbms_output.put_line(nt(2)); -- NULL
   
   begin
     dbms_output.put_line(nt(3)); -- raises ORA-01403: no data found
   exception
     when others then
       dbms_output.put_line(sqlerrm);
   end;

   begin
     dbms_output.put_line(nt(4)); -- raises ORA-06533: Subscript beyond count
   exception
     when others then
       dbms_output.put_line(sqlerrm);
   end;

   
   nt.extend(1); -- now add a 4th element. this does not replace the deleted 3 but creates a new 4th
   
   dbms_output.put_line(nt(4)); -- NULL
END;
/

The example above shows that deleting doesn't result in a valid element with a NULL value, but the ORA-01403 exception. Adding another element will increase the collection size without reusing the deleted element's space.

While learning about Nested Tables, I came across a statement that even if you delete a value from a Nested Table, the index still exists but is assigned with NULL. Is this true?

No, the index does not exist.

If you are going to iterate through a sparse array then do not use a FOR loop. Instead use FIRST, NEXT and LAST (as then: you do not need to know what the minimum index is; nor do you have to check whether indexes exist; and it will work on associative arrays where the indexes are not numeric):

DECLARE
  TYPE nt_type IS TABLE OF NUMBER;
  nt  nt_type := nt_type();
  idx PLS_INTEGER;
BEGIN
  nt.EXTEND(5);

  DBMS_OUTPUT.PUT_LINE('Before DELETE: Count = ' || nt.COUNT);

  nt(1) := 10;
  nt(2) := 20;
  nt.DELETE(3);  -- Delete the 3rd element.
  -- Leave the 4th element as its default value.
  nt(5) := 50;

  DBMS_OUTPUT.PUT_LINE('After DELETE: Count = ' || nt.COUNT);

  IF nt.EXISTS(3) THEN
    DBMS_OUTPUT.PUT_LINE('Index 3 exists.');
  ELSE  
    DBMS_OUTPUT.PUT_LINE('Index 3 does not exist.');
  END IF;

  IF nt.EXISTS(4) THEN
    DBMS_OUTPUT.PUT_LINE('Index 4 exists.');
  ELSE  
    DBMS_OUTPUT.PUT_LINE('Index 4 does not exist.');
  END IF;

  -- Display all elements
  idx := nt.FIRST;
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Element at index ' || idx || ' = ' || COALESCE(TO_CHAR(nt(idx)), 'NULL')
    );
    idx := nt.NEXT(idx);
  END LOOP;
END;
/

Which outputs:

Before DELETE: Count = 5
After DELETE: Count = 4
Index 3 does not exist.
Index 4 exists.
Element at index 1 = 10
Element at index 2 = 20
Element at index 4 = NULL
Element at index 5 = 50

fiddle