Manipulating collection elements

A database column of collection type physically stores the data in the database. As part of data operations, the collection type instance held by a column might get updated. There are two approaches to manipulate the collection instance in the database column:

• The complete instance can be replaced with a new one

• The target element can be solely modified

The first approach is feasible when the structure of the collection and the value of other attributes of the instance are known. But it may not be the case every time. However, it has been demonstrated in earlier sections. Therefore, Oracle recommends manipulating the collection element through a PL/SQL program.

Let us demonstrate the collection element update with the following illustration. Check out the latest data in the table TAB_USE_NT_COL:

SQL> SELECT * FROM TAB_USE_NT_COL;

        ID NUM
---------- --------------------------
         1 NUM_NEST_T(10, 12, 3)
         2 NUM_NEST_T(10, 12, 13)

Now, we...