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:
UPDATE CUSTOMER_OBJ_TABLE
SET phone_array = (‘1-800-234-5678’, ‘1-888-987-6543’)
WHERE cust_id = 25;
Replaces Old values with New values
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:
varray Example
DECLARE
var_phone_array phone_array;
BEGIN
SELECT phone_list into var_phone_array
FROM CUSTOMER_OBJ_TABLE
WHERE cust_id = 26;
var_phone_array.extend;
var_phone_array (var_phone_array.last) := '1-800-336-6199’';
UPDATE CUSTOMER_OBJ_TABLE
SET phone_list = var_phone_array
WHERE cust_id = 26;
COMMIT;
END;
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:
The UPDATE statement with the table name
The SET clause to update the value within a varray
The WHERE clause to select a particular row of the object table
Update <table name>
SET <varray_name>=<variable>
WHERE <clause>;
Syntax To Update Varray
The following MouseOver shows an example of updating data within a varray:
UPDATE CUSTOMER_OBJ_TABLE
SET phone_array =
('1-800-333-5978', '1-888-654-6133')
WHERE cust_id =26;
The UPDATE statement with the TABLE keyword
The SET clause to update the value within a varray
The WHERE clause to select a particular row of the object table
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.