Introduction to Oracle VARRAY TYPE Data Type

The Oracle VARRAY data type is a composite data type used for storing arrays of variable length. It can be used in PL/SQL and is well-suited for storing structured data with a variable number of data items. Below, we will introduce its syntax, use cases, examples, and conclusion.

Syntax

To create a VARRAY type, you need to use the CREATE TYPE statement, as shown below:

CREATE TYPE type_name AS VARRAY(size_limit) OF element_data_type;

Where type_name is the name you specify for the VARRAY type, size_limit is the size limit of the VARRAY type, and element_data_type is the data type of each element in the VARRAY type.

Use Cases

The VARRAY type is well-suited for storing structured data that requires dynamic addition or removal of elements. For example, you can use the VARRAY type to store a list of projects with a variable number of items, or you can use the VARRAY type to store objects with a different number of attributes.

Examples

Here is an example of using the VARRAY type to store a list of projects:

CREATE TYPE project_list AS VARRAY(10) OF VARCHAR2(50);

DECLARE
  projects project_list := project_list();
BEGIN
  projects.EXTEND(3);
  projects(1) := 'Project A';
  projects(2) := 'Project B';
  projects(3) := 'Project C';

  FOR i IN 1..projects.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(projects(i));
  END LOOP;
END;

The above code creates a VARRAY type named project_list with a size limit of 10 and element data type of VARCHAR2. Then, in a PL/SQL block, a projects variable is declared, which is an instance of the project_list type, and initialized as an empty array. Then, 3 elements are added to the array using the EXTEND method and set to different project names. Finally, the COUNT method is used to get the number of elements in the array, and a FOR loop is used to print out all the project names.

Here is an example of using the VARRAY type to store objects with a different number of attributes:

CREATE TYPE employee AS OBJECT (
  id NUMBER,
  name VARCHAR2(50),
  phone_numbers phone_list
);

CREATE TYPE phone_list AS VARRAY(5) OF VARCHAR2(20);

DECLARE
  employee_obj employee := employee(
    id => 1,
    name => 'John',
    phone_numbers => phone_list('555-1234', '555-5678')
  );
BEGIN
  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_obj.id);
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_obj.name);
  DBMS_OUTPUT.PUT_LINE('Phone Numbers:');

  FOR i IN 1..employee_obj.phone_numbers.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('  ' || employee_obj.phone_numbers(i));
  END LOOP;
END;

The above code defines an object type called employee which contains three attributes: id, name, and phone_numbers. The phone_numbers attribute has a type of phone_list, which is a VARRAY type with a size limit of 5 and element type of VARCHAR2. Then, a employee_obj variable is declared in a PL/SQL block, which is an instance of the employee type, and it is initialized with an object containing two phone numbers. Finally, a FOR loop is used to print out all the attributes and phone numbers of the object.

Conclusion

VARRAY type is a very useful Oracle data type that can be used to store structured data with a variable number of items. It can be used in PL/SQL and is ideal for storing data that requires dynamic addition or deletion of elements. By using VARRAY type, you can write applications that are more scalable and flexible.