VARRAY in Oracle with examples

VARRAY in Oracle with examples, VARRAY in object relational databases, How to create and manipulate VARRAY data in Oracle, Examples for VARRAY

VARRAY (Varrying Array)

• VARRAY is one of the collection data types in Oracle to store column(s) that contain multiple values. That is, an attribute of type VARRAY is a multi-valued attribute.

• For example, assume a customer with multiple phone numbers. To store all phone numbers as part of single record, we would use VARRAY.

• Unlike nested tables, VARRAY has a limit. In other words, we use VARRAY attributes to store limited number of entries. It has a limit like other data types NUMBER, CHAR etc.

• We cannot access the individual VARRAY elements using array_name[1] like format. But it can be accessed in PL/SQL using array_name[] format.

• The values that are stored under a VARRAY attribute is stored in-line, that is in the same tablespace as the other data in its row of the same table.

• VARRAY can be created as either

• an array of particular data type like NUMBER, CHAR, VARCHAR, etc.

• or an array of user defined data types.

Example 1 shows the first one and example 2 shows the example of user defined type as varray.

Example 1:

The following statement will create a user defined type Phone_Arr as VARRAY to store/accept at most 20 phone numbers of type NUMBER;

CREATE OR REPLACE TYPE Phone_Arr AS VARRAY(10) OF NUMBER(10); /

The following relational table includes an attribute Phone of VARRAY type that we declared above;

CREATE TABLE Customer ( Cust_ID CHAR(10), Name VARCHAR(30), Phone Phone_Arr )

We can insert the records into the Customer table using the type constructor function as follows [a constructor function is created when you create a type automatically];

INSERT INTO Customer VALUES ( ‘C1001’, ‘Raghu’, Phone_Arr(9878676500, 9809076750) ); INSERT INTO Customer VALUES ( ‘C1002’, ‘Vels’, Phone_Arr(9978679520) );

Here, the first insert statement inserts two phone numbers of customer ‘C1001’, while the second inserts one phone number.

Example 2:

Let us first create a type for Address as follows;

CREATE TYPE Address_Typ AS OBJECT ( Street VARCHAR(20), City VARCHAR(20), Pincode NUMBER(6) ); /

The following statement will create a user defined type Address_Arr as VARRAY to store/accept at most 3 addresses of type Address_Type (as defined above);

CREATE OR REPLACE TYPE Address_Arr AS VARRAY(3) OF Address_Typ; /

The following relational table includes an attribute Address of VARRAY type that we created above;

CREATE TABLE Customer ( Cust_ID CHAR(10), Name VARCHAR(30), Address Address_Arr )

We can insert the records into the Customer table using the type constructor function as follows [a constructor function is created when you create a type automatically];

INSERT INTO Customer VALUES ( ‘C1001’, ‘Raghu’, Address_Arr(Address_Typ(‘str1’, ‘chennai’, 602345), Address_Typ(‘Street’, ‘Madurai’, 634589)) ); INSERT INTO Customer VALUES ( ‘C1002’, ‘Vels’, Address_Arr(Address_Typ(‘MG st’, ‘Bangalore’, 502345)) );

Here, the first insert statement inserts two addresses of customer ‘C1001’, while the second inserts one address.

****************

 

Go to Collection Types page

Go to Object database systems page