VARRAY (Varying-Length Array)

An array is an ordered set of elements, each of which has the same datatype. VARRAYs are named arrays of varying length.
  1. VARRAY data is held directly in the containing row of the table.

    Question: Set up a simple phone book in a table: Each person has a VARRAY of up to four integer phone extensions.

  2. Simple inserts and updates use collection constructors to compose new rows or new column values for rows.

    Question: Insert rows in phonebook for John Smith and David Andrews. 
SQL> CREATE TYPE  extensions_t  AS VARRAY( 4 ) OF INTEGER; 

Type created.


SQL> CREATE TABLE  phonebook ( 
  2    phperson    person_t, 
  3    extensions  extensions_t ); 

Table created.

SQL> INSERT INTO  phonebook  VALUES ( 
  2    person_t( 123897766, name_t( 'Smith', 'John', 'P' ), 45 ), 
  3    extensions_t( 345, 989 ) ); 

1 row created.

SQL> INSERT INTO  phonebook  VALUES ( 
  2    person_t( 432112233, name_t( 'Andrews', 'David', 'S' ), 32 ), 
  3    extensions_t( 123 ) ); 

1 row created.

Question: Retrieve the first name and VARRAY of extensions for the person with social security number 123897766. 

SQL> SELECT  pb.phperson.pname.fname, pb.extensions  FROM  phonebook pb
  2    WHERE  pb.phperson.ssno = 123897766;

PHPERSON.PNAME.FNAME             EXTENSIONS
-----------------------------    --------------------------
John                             EXTENSIONS_T(345, 989)




      Jerry tries to teach Tuffy how to    
      bell the cat (push his luck) in a last effort.