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 :
-
A varray stores an ordered set of elements.
-
Each element has an index associated with it.
-
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.