Varrays

Oracle PL/SQL Varrays are variable-size arrays that can hold a fixed number of elements. Oracle PL/SQL Varrays are a great way to store data in a fixed-size array. They are easy to create and use, and offer a number of functions for adding, retrieving, and removing data.

Syntax

The syntax for creating a Varray is:

TYPE type_name IS VARRAY(size) 
   OF element_type [NOT NULL];

Creating a PL/SQL Varray is simple and only requires two steps. First, you need to create the TYPE. This is done by using the TYPE keyword, followed by the name of the Varray, and then the keyword IS VARRAY. Following this is the keyword OF, which specifies the data type of the elements that will be stored in the Varray.

Example

Course table

Varrays (variable-size arrays)

DECLARE
	TYPE name IS VARRAY(250) OF VARCHAR2(25);
	course_name name;   
BEGIN
	course_name := name('SQL 1', 'SQL 2', 'HTML5');
	FOR i IN 1 .. course_name.COUNT LOOP
		FOR x IN (SELECT * FROM course WHERE name=course_name(i))
		LOOP
			dbms_output.put_line('ID: '||x.course_id||' - Name: '||x.name);
		END LOOP; 
	END LOOP;
END;
/

Output