A varray can be updated only as a single column within the main table and not as individual elements.
Similarly, the individual elements of a varray cannot be selected directly; you must select the whole column on the main table.
To update all the elements of a varray, you must write a DML statement that specifies the new values that replace the old values. For example, to update the varray data within the CUSTOMER_OBJ_TABLE table,
you can use the following DML statement:
SET phone_array = (‘1-800-234-5678’, ‘1-888-987-6543’)
WHERE cust_id = 25;
This statement replaces the old values with the new values, which are defined within the DML statement for a particular record within the CUSTOMER_OBJ_TABLE table.
To update the individual elements or to add more elements into a varray, you must use PL/SQL. Let us look at an example of updating a varray by adding one more elements to it.
To add an element, we must extend the existing varray, then add an element within the PL/SQL code.
In the following example, we will add an element to a record within the CUSTOMER_OBJ_TABLE table:
SELECT phone_list into var_phone_array
WHERE cust_id = 26;
var_phone_array (var_phone_array.last) := '1-800-336-6199’';
SET phone_list = var_phone_array
WHERE cust_id = 26;
In the PL/SQL block above, we defined a variable of type PHONE_ARRAY, then selected the data into the variable.
Next, we extended the varray to add one more element to it and to update the CUSTOMER_OBJ_TABLE table with the variable being assigned to the varray.
The following MouseOver explains the syntax of updating data within a varray:
Example Updating Within Varray
Now that you have learned few techniques to update rows within nested tables and varrays, try them with the following tutor-based exercise.
In this exercise, you will be asked to write couple of UPDATE statements, one each for updating a nested table and a varray. The first statement will update the data within the CUSTOMER_OBJ_TABLE table.
The second statement will update the data within the SALE_HEADER table.
In the next lesson, you will learn about deleting rows from nested tables and varrays.