Varrays | Oracle Database 10g SQL (Osborne ORACLE Press Series)

You use a varray to store an ordered set of elements, with each element having an index associated with it that corresponds to its position in the array. A varray has a maximum size that you can change dynamically.

Note

I ve provided a SQL*Plus script named collection_schema.sql in the SQL directory where you unzipped the files for this book. This script may be run against an Oracle8 or higher database. This script creates a user named collection_user with a password of collection_password and creates the collection types and tables used in the first part of this chapter. This script also populates the tables with sample data. I ve also provided scripts named collection_schema_9i.sql and collection_schema_10g.sql that are referred to later in this chapter (don t run these additional scripts yet).

Creating a Varray Type

You create a varray type using the SQL DDL CREATE TYPE statement, and you specify the maximum size and the type of elements stored in the varray when creating the type. You can change the maximum size of a varray using the ALTER TYPE statement. The following CREATE TYPE statement creates a varray type named varray_address_typ that can store up to two VARCHAR2 strings:

 CREATE TYPE varray_address_typ AS VARRAY(2) OF VARCHAR2(50); /

Each VARCHAR2 can be used to represent a different address for a customer of our imaginary store. One address could be the customer s shipping address where they receive products, and the other their billing address where they receive the bill for the product (both addresses could, of course, be the same). You can also store object types in a collection, and you ll see an example of that when I show you how to create a nested table type later.

Using a Varray Type to Define a Column in a Table

Once you define your varray type, you can use it to define a column in a table. For example, the following table named customers_with_varray uses varray_address_typ to define a column named addresses :

 CREATE TABLE customers_with_varray (id INTEGER PRIMARY KEY,  first_name VARCHAR2(10),  last_name VARCHAR2(10),  addresses varray_address_typ);

Notice that customers_with_varray also contains columns named id , first_name , and last_name in addition to the addresses column.

Getting Information on Varrays

You can use the DESCRIBE command to get information on your varray types. The following example describes the varray type varray_address_typ :

  DESCRIBE varray_address_typ  varray_address_typ VARRAY(2) OF VARCHAR2(50)

The next example describes the table customers_with_varray whose addresses column is of type varray_address_typ :

  DESCRIBE customers_with_varray  Name Null? Type  ------------------------------- -------- ------------------  ID NOT NULL NUMBER(38)  FIRST_NAME VARCHAR2(10)  LAST_NAME VARCHAR2(10)  ADDRESSES VARRAY_ADDRESS_TYP

You can also get information on your varrays from the user_varrays view. Table 13-1 describes some of the columns in user_varrays . I ve only mentioned some of the more useful columns in user_varrays in the table; you can get information on all the columns in user_varrays from the Oracle SQL reference manuals.

Table 13-1: Some Columns in user_varrays
Column	Type	Description
parent_table_name	VARCHAR2(30)	Name of the table that contains the varray.
parent_table_column	VARCHAR2(4000)	Name of the column in the parent table containing the varray.
type_owner	VARCHAR2(30)	User who owns the varray type.
type_name	VARCHAR2(30)	Name of the varray type.
lob_name	VARCHAR2(30)	Name of the large object (LOB) if the varray is stored in a LOB. You'll learn about LOBs in the next chapter.
storage_spec	VARCHAR2(30)	Storage specification for the varray.
return_type	VARCHAR2(20)	Return type of the column.

Note	You can get information on all the tables you have access to using all_varrays .

The following example retrieves the columns from user_varrays ( assuming you re connected to the database as collection_user ):

  SELECT *   FROM user_varrays;  PARENT_TABLE_NAME ------------------------------ PARENT_TABLE_COLUMN ------------------------------------------------------------- TYPE_OWNER TYPE_NAME ------------------------------ ------------------------------ LOB_NAME STORAGE_SPEC ------------------------------ ------------------------------ RETURN_TYPE -------------------- CUSTOMERS_WITH_VARRAY ADDRESSES COLLECTION_USER VARRAY_ADDRESS_TYP  DEFAULT  VALUE

Populating a Varray with Elements

You initially populate the elements in a varray using an INSERT statement. The following INSERT statement adds a row to the customers_with_varray table. Notice the use of the varray_address_typ constructor to specify two strings for the addresses varray column:

 INSERT INTO customers_with_varray VALUES (1, 'Steve', 'Brown',  varray_address_typ('2 State Street, Beantown, MA, 12345',  '4 Hill Street, Lost Town, CA, 54321'));

Selecting Varray Elements

You select the elements in a varray using a SELECT statement. The following SELECT statement selects the row from the customers_with_varray table:

  SELECT *   FROM customers_with_varray;  ID FIRST_NAME LAST_NAME ---------- ---------- ---------- ADDRESSES ---------------------------------------------------------  1 Steve Brown VARRAY_ADDRESS_TYP('2 State Street, Beantown, MA, 12345',  '4 Hill Street, Lost Town, CA, 54321')

Modifying Varray Elements

As I mentioned earlier, the elements in a varray can only be modified as a whole. This means that even if you only want to modify one element, you must supply all the elements for the varray. The following UPDATE statement modifies the first address of customer #1 in the customers_with_varray table. Notice that the second address is also supplied even though it hasn t changed:

 UPDATE customers_with_varray  SET addresses = varray_address_typ('3 New Street, Middle Town, CA, 123435',  '4 Hill Street, Lost Town, CA, 54321') WHERE id = 1;

The following query shows the change:

  SELECT *   FROM customers_with_varray;  ID FIRST_NAME LAST_NAME ---------- ---------- ---------- ADDRESSES --------------------------------------------------------------------------------  1 Steve Brown VARRAY_ADDRESS_TYP('3 New Street, Middle Town, CA, 123435',  '4 Hill Street, Lost Town, CA, 54321')