«Back to Home

Oracle Jump Start
Topics

How To Use VARRAY Or VARYING Arrays In Oracle

Description

VARRAY is a datatype similar to an array in c or pascal. Elements are inserted into a VARRAY, starting at index 1 upto the maximum length declared in VARRAY type.

A VARRAY has two types, which are-
  • Count
  • Limit
COUNT

Current Number of elements.
 
LIMIT

It shows the maximum number of elements, the VARRAY can contain. The limit is user defined.

1

In this diagram a student record contains a VARRAY column, Book_issued which has three elements in it.

VARRAY data is stored within the table, if the VARRAY size is less than 4 KB.

Each elements of the array has the position indicated by an index which can range from one to the count value.

A VARRAY can be stored in a database column. A VARRAY can only be manipulated as a whole individual elements of stored VARRAYS cannot be modified.

Creating VARRAYs

Syntax
  1. Create or replace type type_name  
  2. is [VARRAY|VARYING Array]  
  3. < (Maximum_size) > of element_type [Not Null];  
Example
  1. create type phone_type as object  
  2. (  
  3. country_code number(10),  
  4. area_code number(10),  
  5. phone_num number(20)  
  6. );  
  1. create type phone_varray_type as varray(5) of phone_type;  
2

  1. create type customer_type as object  
  2. (  
  3. customer_name varchar2(20),  
  4. phone phone_varray_type  
  5. );  
  1. create table customer_table of customer_type;  
3

Inserting values
  1. insert into customer_table values(customer_type  
  2. ('smith', Phone_varray_type(phone_type(12, 78, 1111),phone_type(16, 89, 98976))));  
  3. insert into customer_table values(customer_type  
  4. ('smith', Phone_varray_type(phone_type(10, 68, 001111),phone_type(11, 90, 11976))));  

To show your data, use the command, given below-
  1. select c.customer_name, p.country_code, p.area_code, p.phone_num  
  2. from customer_table c, table(c.phone)p;  
4

Updates
  1. Update customer_table c set c.phone = phone_varray_type  
  2. (phone_type(17, 13, 233322),  
  3. phone_type(18, 14, 443344))  
  4. where c.customer_name = ‘smith’;  
5

Summary

Thus we learnt, VARRAY has two types such as count and limit. VARRAY is a data type, similar to an array in C or Pascal. We learnt, how to use VARRAY or VARYING array in Oracle with the examples.