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;

Updating a VARRAY: You can update individual elements of a VARRAY within a table. For example, if you have a table EMPLOYEES with a VARRAY column PHONE_NUMBERS, you can update an individual element like this:

UPDATE EMPLOYEES
SET PHONE_NUMBERS(1) = '1234567890'
WHERE EMPLOYEE_ID = 100;

This means you don't have to update the entire VARRAY; you can modify elements individually. Selecting from a VARRAY: You can select individual elements of a VARRAY without needing to select the whole column:

SELECT PHONE_NUMBERS(1) FROM EMPLOYEES 
WHERE EMPLOYEE_ID = 100;

This query will return just the first element of the PHONE_NUMBERS VARRAY for the employee with ID 100. However, there are some nuances:

• You cannot directly select or update a VARRAY element outside of its containing column in SQL; you must reference the column first.
• SQL operations like WHERE clauses can compare a specific element of a VARRAY directly, but you can't use SQL to manipulate (like add or remove) elements from a VARRAY stored in a table; for those operations, you'd typically use PL/SQL (procedural language constructs).

Therefore, while there are restrictions on how you interact with VARRAY elements, they are not as limiting as described in your initial statement. You can indeed select and update individual elements within the context of the column they belong to.

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

The following diagram shows an example of updating data within a varray:

• PL/SQL History
  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:
  Past Oracle Limitations: Collections and Records

  Limitations and Frustrations	Fixed
  Cannot store a list of values related to an individual row. Alternative: create a separate attributive table to hold the row’s list. These “temporary” child tables clutter the schema, add processing and storage overhead, and make code more complex.	8.0
  Cannot use a collection in SQL statements. Alternative: create, populate and join to a temporary table.	8.0
  Could not do array-like, or bulk SQL operations on collections. Alternative: None. Limited to one-by-one row processing. Shifting from the PL/SQL to SQL engine as each row was processed was a real drag on performance.	8.1
  Cannot access list values using character-based keys. Alternative: create another kludge table to store the key:value pairs, or roll your own hash tables using a packaged PL/SQL table of record, hash algorithms and functions. Do-able, but not fun.	9.2
  Cannot insert or update a table using a record. Alternative: None. Break out a record into its individual columns for insert/update. This effectively eliminated the benefits of abstraction and de-coupling that records were meant to provide.	9.2
  Cannot use awesome BULK ops to get data directly into collections of record. Alternative: break every column down into its own collection of scalar. That was really unpleasant working with any more than a three-column table.	9.2

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