Updating Rows in 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 data explains the syntax for updating data within a varray:

With version 7, Oracle finally introduced the PL/SQL Record and PL/SQL Table. With records, the contents of a programmer-defined structure or table row could be loaded into a variable that could be manipulated and used as a parameter to PL/SQL routines2. With PL/SQL Tables, lists of scalar values, and even lists of records, could be created and manipulated without the overhead of doing everything with tables and SQL statements. This greatly simplified programming in PL/SQL and enabled robust PL/SQL routines. Oracle programmers continued to run into limitations in their quest to simplify and speed up their routines. The significant limitations and when they were finally resolved are:

Now that you have learned few techniques to update rows within nested tables and varrays, try them with the following 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.

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