VARRAY (Cont.)

We can NOT insert or update individual elements of VARRAYs as we did with nested tables.
  1. Question: Change the last names of all dependents of employee 101 to uppercase.

    The UPPER function returns char, with all letters uppercase.

  2. Question: Insert all the rows from the table people_tbl into John Smith’s dependents table. 
SQL> UPDATE  TABLE ( SELECT e.dependents  FROM  employees e 
  2      WHERE  e.eid = 101 ) d 
  3    SET  d.pname.lname = UPPER( d.pname.lname ); 

2 rows updated.

SQL> SELECT  *  FROM  employees e  WHERE  e.eid = 101; 

       EID
----------
EPERSON(SSNO, PNAME(LNAME, FNAME, MI), AGE)
----------------------------------------------------------------
DEPENDENTS(SSNO, PNAME(LNAME, FNAME, MI), AGE)
----------------------------------------------------------------
       101
PERSON_T(123897766, NAME_T('Smith', 'John', 'P'), 45)
DEPENDENTS_T(
  PERSON_T(32245776, NAME_T('SMITH', 'Michael', 'J'), 8), 
  PERSON_T(123822332, NAME_T('SMITH', 'Susan', 'R'), 12))


SQL> INSERT INTO TABLE( 
  2      SELECT  e.dependents  FROM  employees e 
  3        WHERE  e.eperson.ssno = 123897766 ) 
  4    SELECT  *  FROM  people_tbl; 

4 rows created.

SQL> SELECT  e.dependents  FROM  employees e 
  2    WHERE  e.eperson.ssno = 123897766; 

DEPENDENTS(SSNO, PNAME(LNAME, FNAME, MI), AGE)
-----------------------------------------------------------------
DEPENDENTS_T(
  PERSON_T(322456776, NAME_T('SMITH', 'Michael', 'J'), 8), 
  PERSON_T(123822332, NAME_T('SMITH', 'Susan', 'R'), 12), 
  PERSON_T(345678901, NAME_T('Bond', 'James', 'C'), 21), 
  PERSON_T(456789012, NAME_T('Ball', 'Dragon', 'Z'), 32), 
  PERSON_T(890123456, NAME_T('Einstein', 'Albert', 'E'), 136),
  PERSON_T(901234567, NULL, NULL))