The scope of the VARRAY which is created as PL/SQL block member is limited to the block in which it is created, that means we cannot use this VARRAY outside its block or even reuse it and that is its biggest drawback. So go ahead & read on to find out how we can overcome this disadvantage of VARRAY.

This drawback can easily be overcome if we can find out a way to create the VARRAY outside the PL/SQL block and store it permanently into the schema. Fortunately we can achieve both the goals by creating the VARRAY as a database object. That is exactly what we are going to learn in this tutorial.

In this tutorial we will learn –

  • How to create VARRAY as database object.
  • How to use that varray.
  • How to insert data into the VARRAY.
  • How to retrieve data into(from) the VARRAY
  • How to update the data of the VARRAY.

Let’s start with the first step.

How to Create VARRAY as Database Object?

	SET SERVEROUTPUT ON;
	CREATE OR REPLACE TYPE dbObj_vry IS VARRAY (5) OF NUMBER;
	/

Above code on successful execution will create a VARRAY with name dbObj_vry which will have the size limit of 5 elements and their datatype will be NUMBER. This VARRAY has wider scope and can be used not only inside the PL/SQL block but also with other schema objects.

How to Use the VARRAY Created as Database Object?

The benefit of defining the VARRAY as database object is that it may be referenced from any program that has the permission to use it. You can use the VARRAY with tables, records or even with PL/SQL blocks.

Let’s do the example:

Example 1. How to define a column of a table using VARRAY?

CREATE TABLE calendar(
    day_name        VARCHAR2(25),
    day_date        dbObj_vry
);
/

In the above code we created a table with the name Calendar which has two columns day_name and day_date. The first column can hold data of VARCHAR2 datatype whereas the second column can hold data of a dbObj_vry type which is a VARRAY.

Info: What does defining a column of a table as VARRAY type means?
Defining a column of a table as VARRAY type means that it can hold ‘n’ number of values into it. Where ‘n’ is equal to the size limit of that varray. In our case the size limit of VARRAY is 5 that means the column ‘Day Date’ of table Calendar can hold 5 values.

How to insert data into the VARRAY?

INSERT INTO calendar ( day_name, day_date ) 
VALUES ( 'Sunday', dbObj_vry (7, 14, 21, 28) );    

This insert DML statement will insert a row into the Calendar table. Inserting data into the first column ‘Day Name’ which is of varchar2 datatype is easy. You just have to write the desired data and enclose it into single quotes. But same is not true with the second column ‘Day Date’ which is of VARRAY type. In order to insert data into the column which is of VARRAY type you first have to write the name of the varray and supply the data.

Also, you have to make sure four things

  1. The data that you are supplying must be enclosed inside the parenthesis.
  2. The Datatype of the data must match with the datatype of the elements of your VARRAY which in our case is NUMBER.
  3. The number of elements you are inserting into the column must either be less than or equal to the size limit of the VARRAY. In our case it is 5 and we are inserting 4 elements into the column which is completely ok. But if suppose I insert 6 elements into the column then there will be an error.
  4. If inserting multiple data into VARRAY column then make sure to separate the elements from each other using semi-colon.

How to retrieve the data from the VARRAY?

Data can be retrieved using SELECT statement. Any correctly written SELECT statement will do the work. For example

SELECT * FROM calendar;

This will retrieve all the data from the table calendar.

how to create varray as database collection object by manish sharma
Result 1: Simple SELECT Statement

In case you want to display the data stored into the column, which is holding data of VARRAY type, in a relational format then you can take the help of TABLE expression. For example

SELECT 
    tab1.day_name, 
    vry.column_value AS "Date"
FROM calendar tab1, TABLE (tab1.day_date) vry;

This SELECT statement will show you the data from both the columns in a relational format. The TABLE expression can open the collection instance and represent the object rows in relational format.

how to create varray as database object by manish sharma
Result 2: TABLE Expression

How to update the data of VARRAY type column?

Updating the values of VARRAY type column is pretty simple. Below example will show you how to update the values of day_date columns.

UPDATE calendar 
SET day_date = dbObj_vry(10,14,21,28) 
WHERE day_name = 'Sunday';

Example 2. How to use VARRAY with PL/SQL block?

In the above example we learnt, how to use the VARRAY which is created as Database object to define the column of a table. Now we will see how to use the same varray inside a PL/SQL block.

DECLARE
    vry_obj dbObj_vry    := dbObj_vry();
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;
/

You have seen this example in the last tutorial. There are no such big changes here except that this time instead of defining the VARRAY inside the block we created it as a standalone database object. I suggest you to take a look at the last tutorial where I explained the above code in detail.

That is the PL/SQL tutorial on how to create VARRAY as Database object in Oracle. Hope you enjoyed reading, if so then do make sure to share this blog on your social with your friends. Thanks & have a great day!