Home > Database, Oracle > VARRAY on Oracle

VARRAY on Oracle

Creating a Varray Type:

  1. A varray stores an ordered set of elements.
  2. Each element has an index associated with it.
  3. A varray has a maximum size that you can change dynamically.
You create a varray type using the SQL DDL CREATE TYPE statement.
You specify the maximum size and the type of elements stored in the varray when creating the
The basic Oracle syntax for the CREATE TYPE statement for a VARRAY type definition would be:
CREATE OR REPLACE TYPE name-of-type IS VARRAY(nn)of type
Where name-of-type is a valid attribute name, nn is the number of elements (maximum) in the array, and type is the data type of the elements of the array.
You can change the maximum size of a varray using the ALTER TYPE statement.
SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2OF VARCHAR2(50);
  2  /
Type created.SQL>
SQL> desc addressVarray;
 addressVarray VARRAY(2OF VARCHAR2(50)

SQL>

Using VARRAYs

In the Oracle environment, array subscripts start from 1, and not from 0 (as in C and Java).
VARRAYs are of fixed length.
You specify the length of the array when you define it.
Arrays of elements of the same type use sequential numbers as a subscript.
VARRAYS can be used both in PL/SQL and SQL.
You should use VARRAYs when you know the size of your data set and that size is very stable.
declare
  type VarrayType is varray(sizeof ElementType;
create or replace type VarrayType is varray(sizeof ElementType;
The size of a VARRAY must be a positive integer and cannot be null.
You cannot create an array of REF CURSORs.
SQL>
SQL> declare
  2      type month_va is varray(13of VARCHAR2(20);
  3      v_month_va month_va;
  4      v_count_nr number;
  5  begin
  6      v_month_va:=month_va('A','B','C','D','E','F','G');
  7      DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
  8
  9      v_month_va.extend;
 10      v_month_va(v_month_va.last):='Null';
 11      DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
 12
 13      for i in v_month_va.first..v_month_va.last
 14      loop
 15          DBMS_OUTPUT.put_line('v_month_va(i)'||v_month_va(i));
 16      end loop;
 17  end;
 18  /
Length:7
Length:8
v_month_va(i): A
v_month_va(i): B
v_month_va(i): C
v_month_va(i): D
v_month_va(i): E
v_month_va(i): F
v_month_va(i): G
v_month_va(i): Null
PL/SQL procedure successfully completed.
Advertisements
Categories: Database, Oracle
  1. June 1, 2013 at 9:43 am
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: