Modifying Varray Elements : VARRAY Column « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. VARRAY Column

The elements in a varray can only be modified as a whole.

SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
  2  /

Type created.

SQL> CREATE TABLE employee (
  2    id         INTEGER PRIMARY KEY,
  3    first_name VARCHAR2(10),
  4    last_name  VARCHAR2(10),
  5    addresses  addressVarray
  6  );

Table created.

SQL>
SQL> DESCRIBE employee;
 Name          Null?    Type

 ID            NOT NULL NUMBER(38)
 FIRST_NAME             VARCHAR2(10)
 LAST_NAME              VARCHAR2(10)
 ADDRESSES              ADDRESSVARRAY

SQL>
SQL> INSERT INTO employee VALUES (
  2    1, 'Steve', 'Brown',
  3    addressVarray(
  4      'AAAAAAAAAAAAAAAA',
  5      'BBBBBBBBBBBBBBBB'
  6    )
  7  );

1 row created.

SQL>
SQL> SELECT *
  2  FROM employee;

ID FIRST_NAME LAST_NAME        ADDRESSES
------------------------------------------------------------------------------------
1 Steve      Brown            ADDRESSVARRAY('AAAAAAAAAAAAAAAA', 'BBBBBBBBBBBBBBBB')


SQL>
SQL>
SQL> UPDATE employee
  2    SET addresses = addressVarray(
  3      'CCCCCCCCCCCCCCC',
  4      'BBBBBBBBBBBBBBB'
  5    )
  6  WHERE id = 1;

1 row updated.

SQL>
SQL> SELECT *
  2  FROM employee;

ID FIRST_NAME LAST_NAME     ADDRESSES
------------------------------------------------------------------------
1 Steve      Brown         ADDRESSVARRAY('CCCCCCCCCCCCCCC', 'BBBBBBBBBBBBBBB')


SQL>
SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL>