Today we are going to do some practical demonstration to learn how to create a VARRAY in Oracle Database so feel free to visit the last tutorial for all the necessary theories on VARRAYs. Like always I will try to keep the example as simple as possible. So, if you are new to programming, don’t worry, I got you!

Previously we discussed in the introduction to PL/SQL VARRAYs that like nested table VARRAYs can be created

• As a member of PL/SQL Block and
• As a database object.

Today we will learn how to create VARRAYs as a Member of PL/SQL block and leave the rest for the future tutorials.

Step 1: Define a Varray inside PL/SQL block

You can define a varray only inside the declaration section of a PL/SQL block.

 	SET SERVEROUTPUT ON;
	DECLARE
	    TYPE inBlock_vry IS VARRAY (5) OF NUMBER;

In the above code we created a VARRAY and named it inBlock_vry. This varray is capable of holding 5 elements of Number datatype.

Step 2: Initialize the Varray

Initialization of a varray can easily be done using the collection variable. To initialize the VARRAY we will first define a collection variable and then use it for initializing.

vry_obj inBlock_vry  :=  inBlock_vry();

In the above code we created a collection variable with name vry_obj and used that to initialize the varray inBlock_vry.

Info:
Some books refer to collection variable as collection object, so please don’t get confused as both are the same.

Step 3: How to insert data into the VARRAY

Inserting data into the varray is very similar to inserting data into the array of other programming language. You can insert data either directly into each cell of the varray using the index number or you can use LOOP for populating the varray.

How to insert data into the VARRAY using index of the cell

As we know that the structure of a cell PL/SQL collection consists of a cell with a subscript called index. We can use this index for inserting the data into the varray.

	BEGIN
		vry_obj.EXTEND(5); 
		vry_obj(1):= 10*2;
		DBMS_OUTPUT.PUT_LINE(vry_obj(1));    
	END;
	/

Execution section -1

In the above code we wrote the execution section of the PL/SQL block. It consists of 3 executable statements. These three statements are –

Line 6: Statement 1

First statement is an EXTEND procedure call. In this statement we are allocating the memory to each cell of VARRAY using the EXTEND procedure.

Line 7: Statement 2

In the second statement we are assigning a numeric value (value derived from arithmetic multiplication expression) into the first cell of the varray (cell with index number 1).

Info:
In PL/SQL collection VARRAY index number of the cell starts with 1 whereas the index number of cells in array starts with 0.

Line 8: Statement 3

Third statement is an output statement where we are displaying the value which we stored into the 1^st cell of the VARRY back to the user.

That is how you can store and display the value stored in individual cell of the varray. This process is good only when you have a short varray. Otherwise it is not an efficient way. Another way of inserting data into the Varray is by using Loop.

Recommended reading: Introduction to PL/SQL Loop

How to insert data into a VARRAY using PL/SQL Loop

The most common way of dealing with data of a collection is by using Loops. Most programmers are used to using Loops to cycle through the data of any kind of array because this is easy, less time consuming and have less line of codes which keep your code cleaner and makes it easy to read. In short it is easy and efficient.

	BEGIN
	FOR i IN 1 .. vry_obj.LIMIT
	LOOP
 		vry_obj.EXTEND;
		vry_obj (i):= 10*i;    
		DBMS_OUTPUT.PUT_LINE (vry_obj (i));    
	END LOOP;
	END;
	/

Execution section -2

I have explained the above code in detail in the Video tutorial on my YouTube channel which you can watch here.

Still to minimize the confusion I will explain to you here the two main functions used in the above execution section (Execution Section – 2) that are – Limit & Extend.

To understand the working of above shown code requires the understanding of PL/SQL for loop. Gladly, I have done a detailed tutorial on For-Loop, which you can read here.

Limit (line 6): Limit is a collection method which returns the maximum number of elements which are allowed in the VARRAY. In our case the maximum number of elements which are allowed in the VARRAY is 5 (line 3) which in turn becomes the upper limit of the For-Loop here.

Extend (Line 8): Extend is a procedure which is used for allocating the memory and appends an element to the VARRAY. If used without argument (Execution Section-2 Line 8) it appends single null element and if used with an argument EXTEND (n) (execution section -1 Line 6) it then appends n numbers to the collection. Where n is the integer you supplied as an argument to the procedure EXTEND.

That is the PL/SQL tutorial on How to create collection VARRAY in Oracle Database as a member of PL/SQL block. Please do make sure to Like this blog as well as share it with your friends and subscribe to my channel as many such Tutorials are yet to come. Thanks & have a great day!