Oracle PL/SQL Varrays

Oracle PL/SQL Varrays

Varray stands for variable-size array. Varray can be stored in the columns of your tables. When you create varrays you must provide the maximum size for them. They retain their ordering and subscripts when stored in and retrieved from a database table. They are similar to PL/SQL table, and each element in a varray is assigned a subscript/index starting with 1.These are dense and Not sparse, which means there is no way to delete individual elements of a Varray.

Example 1 of PL/SQL Varray

Once the VARRAY is defined, elements can easily be inserted into the VARRAY. We can initialize our PLSQL varrays either in the declaration or separately. If the varray is only partially initialized we have to use the EXTEND method to define extra cells.

SQL> declare
2    type auth_var is varray(10) of
3                      author.author_last_name%type;
4    x_auth auth_var := auth_var();
5  begin
6    x_auth.extend;
7    x_auth(1) := 'Hello';
8    x_auth.extend;
9    x_auth(2) := 'World';
10    dbms_output.put_line(
x_auth(1) ||' & '|| x_auth(2));
11  end; /

Hello & World

In the lines 2 and 3 we define the VARRAY type with a maximum of 10 elements. In line 4 we define the variable x_auth of auth_var type and it also initializes the array. A VARRAY can not be used until it is initialized. In line 4 the auth_var() function actually does the initialization.

Once the array is initialized you can extend it and add elements, which is done in lines 6 through 9. We access each element using the VARRAY variable and the index number. When the PL/SQL block ends (or the array variable goes out of scope), the memory used by the array is recovered automatically by the PL/SQL engine. Unlike a cursor, you do not close a collection.

Example 2 of PL/SQL Varray



Example 3 of PL/SQL Varray



PL/SQL procedure successfully completed.

Let us take a look what the line varray.EXTEND(2, 4);does in the above code. It appends two copies on the fourth element to the collection. As a result, the seventh and eighth elements both contain a value of 4.