12.8 Manipulating Collections

If you want to modify a collection's contents, you have two choices: replace the entire collection or modify individual elements of the collection. If the collection is a varray, you have no choice but to replace the entire collection. You can accomplish this by retrieving the contents of the varray, modifying the data, and then updating the table with the new varray. The following statement changes the restock dates for part number "GX5-2786-A2." Note that the varray is entirely recreated:

UPDATE part_c

SET restocks = resupply_dates(TO_DATE('03-SEP-1999','DD-MON-YYYY'),

    TO_DATE('25-APR-2000','DD-MON-YYYY'),

    TO_DATE('21-MAR-2001','DD-MON-YYYY'))

WHERE part_nbr = 'GX5-2786-A2';

If you are using nested tables, however, you can perform DML against individual elements of a collection. For example, the following statement adds an additional line item to the nested cust_order_c table for order number 1000:

INSERT INTO TABLE(SELECT order_items FROM cust_order_c

  WHERE order_nbr = 1000)

VALUES (line_item_obj('T25-ASM', 1));

To update data in the same nested table, use the TABLE expression to create a data set consisting of part numbers from order number 1000, and then modify the element with a specified part number:

UPDATE TABLE(SELECT order_items FROM cust_order_c

  WHERE order_nbr = 1000) oi

SET oi.quantity = 2

WHERE oi.part_nbr = 'T25-ASM';

Similarly, you can use the same data set to remove elements from the collection:

DELETE FROM TABLE(SELECT order_items FROM cust_order_c

  WHERE order_nbr = 1000) oi

WHERE oi.part_nbr = 'T25-ASM';