PL/SQL   «Prev  Next»
Lesson 6Updating rows in a Varray
Objective Write an update command for a varray

Updating Rows in a varray

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;

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:

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:
  1. The UPDATE statement with the table name
  2. The SET clause to update the value within a varray
  3. 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:
  1. The UPDATE statement with the TABLE keyword
  2. The SET clause to update the value within a varray
  3. The WHERE clause to select a particular row of the object table
UPDATE CUSTOMER_OBJ_TABLE
SET phone_array =
('1-800-333-5978', '1-888-654-6133')
WHERE cust_id =26;

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.

Updating Rows in Varray - Exercise

Click the Exercise link below to build the UPDATE statements to update a nested table and a varray.
Updating Rows in Varray - Exercise