Varray in oracle :

In my previous article, I have explained about complex types of PL SQL as well as different scalar datatypes with examples. In this article I will try to explain about the Varray in oracle.Varrays are nothing but variable size arrays, which will hold the fixed number of elements from database.Varray in oracle is also known as varying array type. In oracle the array subscripts starts from 1 not from 0 like C. User can able to specify length of array when you define it.

User can not create array of REF Cursor.

Varray in oracle Examples:

Varray in oracle can be used at SQL as well as PL SQL level. The varrays are useful when user knows the size of dataset and mostly when size is fixed.

Syntax :

declare  type V_array_name  is varray(size) of Variable_name (size);

create or replace type V_array_name is varray(size) of ElementType;

 Or

User can create Varray independently in the database :

CREATE OR REPLACE TYPE <Type_name>

IS

[VARRAY | VARYING ARRAY] (<Bound_limit>) OF <Scalar_datatype | Composite_datatype>;

Using Type keyword compiler will able to know that user needs to define the type at database level. Before us going to take example of varray in oracle let us check some built in methods in collections. There are set of collection methods for use of Varrays.

The methods cannot be used with DML statements. These methods are useful in PL SQL blocks.

1.LIMIT :

LIMIT is used with varrays to determine the maximum number of values allowed.

2.FIRST :

The First Keyword returns the smallest index number used in varray. As I mentioned that Varrays starts from value as 1 the First method always returns the value ‘1’ .Naturally FIRST function will return null in case of empty collection.

3.LAST:

LAST method will return largest index number used in varray. LAST returns the last filled instance of Varray. For Varray Count is always LAST.4.COUNT:Count method is used to fetch the current count of varray elements, which will not contain the null values. For varrays, count is always equal to LAST method.

5.PRIOR and NEXT :

Return the prior or next value based on the input value for the collection index. PRIOR and NEXT ignore deleted instances in a collection.

6. EXTEND (n) :

Appends instances to a collection. EXTEND has three forms, EXTEND, which adds one null instance, EXTEND(n) which adds “n” null instances and EXTEND(n,m) which appends N copies of instance “m” to the collection. For not null specified collections forms one and two cannot be used.

7.TRIM :

TRIM is used to remove the instance from the collection.Trim(n) removes the n instances from collection.

8.DELETE :

DELETE removes specified items from a nested table or all of a VARRAY. DELETE specified with no arguments removes all instances of a collection.

Exceptions needs to use while using collections :

• COLLECTION_IS_NULL — Caused when the collection referenced is atomically null.
• NO_DATA_FOUND — Subscript points to a null instance of the collection.
• SUBSCRIPT_BEYOND_COUNT — The specified subscript is beyond the number of instances in the collection.
• SUBSCRIPT_OUTSIDE_LIMIT — The specified subscript is outside the legal range (usually received from VARRAY references)
• VALUE_ERROR — Subscript is null or is not an integer.

Example 1 :

 SET serveroutput ON;

DECLARE  TYPE t_varray IS VARRAY(5) OF NUMBER(10);

V_Number  t_varray;      —declaration of variable of varray type  f_val   NUMBER;

BEGIN  V_Number := t_varray(1, 2);                 —-Initialization of varray with 2 values

FOR i IN 3 .. 5 LOOP

V_Number.extend;

V_Number (V_Number.last) := i;

END LOOP;

f_val := V_Number.FIRST;                            —Traversing the collection starts here

WHILE f_val IS NOT NULL LOOP

DBMS_OUTPUT.PUT_LINE(‘The Varray’ || V_Number(f_val));    f_val := V_Number.NEXT(f_val);

END LOOP display_loop;

END;

Output :

anonymous block completed

The Varray1

The Varray2

The Varray3

The Varray4

The Varray5

The VARRAYs are mainly used in an environment where the number of elements to be stored/processed are already known and its size is very stable. The user can create varray in database independently .User can reuse this type of varray collection type in different database objects.

Example :

CREATE Or Replace TYPE New_varray AS VARRAY(2) OF VARCHAR2(50);

Output :

 Type created.

You create a varray type using the SQL DDL CREATE TYPE statement.

Things to remember :

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.

Useful examples of Varray in oracle :

Example 1 : Creation of Varray type at Schama level

  CREATE OR REPLACE TYPE T_new_type_t  IS    VARRAY(10) OF VARCHAR2 (100);

Type Created

Giving grants to type created : 

GRANT EXECUTE ON T_new_type TO PUBLIC;

Example 2 : Varray simple example  (Use already created varray type )

DECLARE

Name  T_new_type:= T_new_type ();

BEGIN    Name.EXTEND (4);

Name (1) := ‘Sreenu’;

Name (2) := ‘Amit’;

Name (3) := ‘Sonali’;

Name (4) := ‘Sukruta’;

FOR v_name IN 1 .. Name.COUNT

LOOP

DBMS_OUTPUT.put_line (Name (v_name));

END LOOP;

END;

Example 3 : Varray example with use of cursor

DECLARE    CURSOR c_Employee is    SELECT  name FROM Employees;

type e_list is varray (6) of Employees.name%type;

Employee_list e_list := e_list();

counter integer :=0;

BEGIN    FOR n IN c_Employee

LOOP       counter := counter + 1;

Employee_list.extend;

Employee_list (counter)  := n.name;

dbms_output.put_line(‘Employees: (‘||counter ||’):’|| Employee_list (counter));

END LOOP;

END;

Output :

Employees(1):  Amit

Employees (2): Sreenu

Employees (3): Divya

Employees (4): Sonali

Employees (5): Kalpesh

Employees (6): Sukruta  

Example 4 : Varray in Oracle with Delete statement:

 Varrays always have consecutive subscripts, so you cannot delete individual elements except from the end by using the TRIM method. You can use DELETE without parameters to delete all elements. So this block fails.

DECLARE

Name  T_new_type:= T_new_type ();

BEGIN

Name.EXTEND (4);

Name (1) := ‘Sreenu’;

Name (2) := ‘Amit’;

Name (3) := ‘Sonali’;

Name (4) := ‘Sukruta’;

–Name.delete (2);                      —Error will come

Name.delete();                          —Deleted all records

END;

END;

Example 5 : Varray in Oracle with table operator

You can apply SQL query logic to the contents a varray using the TABLE operator.

DECLARE

Name  T_new_type:= T_new_type ();

BEGIN

Name.EXTEND (4);

Name (1) := ‘Sreenu’;

Name (2) := ‘Amit’;

Name (3) := ‘Sonali’;

Name (4) := ‘Sukruta’;

/* Use TABLE operator to apply SQL operations to a PL/SQL nested table */

FOR rec IN ( SELECT COLUMN_VALUE employee_name                    FROM TABLE (Name)                ORDER BY employee_name)

LOOP

DBMS_OUTPUT.put_line (rec.employee_name);

END LOOP;

END;

 Example 6 :  Varray in Oracle with Insertion statement

Create varray :

CREATE OR REPLACE TYPE manager_names_t IS VARRAY (2) OF VARCHAR2 (100);

CREATE OR REPLACE TYPE Employee_names_t IS VARRAY (1) OF VARCHAR2 (100);

CREATE TABLE Employee ( surname  VARCHAR2 (1000) ,Manager_names manager_names_t , Employee_names   manager_names_t);

—Use of varray type in table

DECLARE

Managers    manager_names_t:= manager_names_t ();

Employee   Employee_names_t:= Employee_names_t ();

BEGIN

DBMS_OUTPUT.put_line (Managers.LIMIT);          —-Use of Limit keyword

Managers.EXTEND (2);

Managers(1) := ‘Kartik’;

Managers(2) := ‘Rohit’;    —    Employee.EXTEND;

Employee (1) := ‘Amit’;     —

INSERT INTO family (surname, Manager_names, Employee_names)

VALUES (‘Mali’, Managers, Employee);

COMMIT;

END;

These are some important real life examples of varray in oracle.Hope everyone like this article.Dont miss to share it with everyone.