VARRAYs were launched in Oracle 8i back in 1998 as a modified version of Nested table type collection which we have discussed in the previous blogs. 

Suggested Reading: How to Create Nested Table –

  • As PL/SQL Block element
  • As Database Object
  • Using User-Define Datatype

Also now you can test your knowledge on the subject by taking a test here.

VARRAY is an important topic because it is seen that generally there is always a question on it in certification exam. In order to minimize any confusion we will first take a brief look at the intro of VARRAYs collection.

VARRAYs which is an acronym of Variable Sized Arrays were introduced in Oracle 8i back in 1998 as a modified format of nested tables. The major modifications can be seen in storage orientation. There are no noticeable changes in the implementation but their storage orientation is completely different compared to the nested tables.

Unlike nested table which requires an external table for its storage, VARRAYs are stored in-line with their parent record as raw value in the parent table. It means no more need for STORE AS clause. Oh, what a relief, no unnecessary IOs and on top of that increased performance.

Can we save and reuse VARRAYs?

Similar to Nested Tables VARRAYs are Persistent type of Collection which means they can be created as database object that can be saved for later use. VARRAYs can also be created as member of PL/SQL Blocks. The scope of the VARRAY which is declared inside a PL/SQL block is limited to the block in which it is created.

Are VARRAYs bounded or Unbounded?

Unlike Nested table VARRAYs are bounded form of collection. By bounded I mean, you have to decide how many elements you want to store in your collection while declaring it. Whereas in nested table which is unbounded type of collection there is no upper cap on number of elements.

VARRAYs Storage Mechanism

The storage mechanism of VARRAYs is the biggest difference which makes them a superior choice than Nested tables. Unlike nested tables which requires an external table for its storage, VARRAYs are stored in-line with their parent record as raw value in the parent table. This means there is no requirement of STORE AS clause or separate storage table.

The in-line storage of VARRAYs help in reducing disk Inputs/Outputs (I/O) which makes VARRAYs more performance efficient than nested table. But when VARRAYs exceed 4K data then Oracle follows out-of-line storage mechanism and stores VARRAYs as an LOB.

Syntax for creating PL/SQL VARRAYs

In this section we will see the syntax for creating VARRAYs as

  • Database Object and
  • Member of PL/SQL Block.

You can head over to the Video on the same topic on my YouTube channel where I explained both these syntax in detail.

VARRAY as Database Object

CREATE [OR REPLACE] TYPE type_name
IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type;

VARRAY as a member of PL/SQL Block

DECLARE
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF
element_type;

Both the above syntaxes are same as of nested table except here we have an additional clause which is Size_Limit. Size limit is a numeric integer which will indicate the maximum number of elements your VARRAY can hold.

Always remember similar to nested table we can declare VARRAY only in the declaration section of PL/SQL block.

How to modify the size limit of VARRAYs type collection?

Size limit of a VARRAY can be altered using ALTER TYPE DDL statement.

ALTER TYPE type_name MODIFY LIMIT new-size-limit [INVALIDATE | CASCADE]

Where:

ALTER TYPE is a reserved phrase that indicates to the compiler which DDL action you want to perform.

TYPE NAME is the name of type which you want to alter.

MODIFY LIMIT is a clause which informs the compiler that user wants to modify the size limit.

NEW-SIZE-LIMIT is an integer which will be the new size limit of your VARRAY.

INVALIDATE clause is an optional clause which will Invalidate all dependent objects without any checking mechanism.

CASCADE clause again is an optional clause which will propagate changes to dependent types and table.

 

How to drop a VARRAY type collection?

To drop a VARRAY type you can take help from DROP DDL statement.

DROP TYPE type_name [FORCE];

Where:

Drop Type

Is a DDL statement using which you can drop any type created on your database.

Type name

Type name is the name of an already created type which you want to drop.

Force

Specify FORCE to drop the type even if it has dependent database objects. Oracle Database marks UNUSED all columns dependent on the type to be dropped, and those columns become inaccessible. Remember this operation is not recoverable and could cause the data in the dependent tables or columns to become inaccessible. 

Hope you learnt something new. Now you can test your knowledge on the subject by taking a test here. Please help us in growing by sharing this blog on your social. In this way you can also help your friends in learning. Thanks & have a great day!