PL/SQL - VARRAY

About

VARRAY (variable-size arrays) is a collection, a variable-size arrays (varrays for short)

Articles Related

Constructor

Type

-- Database 
CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);
-- PLSQL
TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

PLSQL

DECLARE
   TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);
   hobbies Hobbies;
BEGIN
   hobbies := Hobbies('golf', 'quilting', 'rock climbing');
END;
/

Example

Table

CREATE TABLE tabHobbies (hobbs Hobbies);
INSERT INTO tabHobbies(hobbs) values (Hobbies('golf', 'quilting', 'rock climbing'));

Fetch

Loop

DECLARE
    TYPE RefCurTyp IS REF CURSOR;
    cv RefCurTyp;
    h  Hobbies;
BEGIN
   OPEN cv FOR 'SELECT hobbs FROM TabHobbies';
   LOOP
      FETCH cv INTO h;
      EXIT WHEN cv%NOTFOUND;
      -- print attributes of 'h' with the collection methods
   END LOOP;
   CLOSE cv;
END;

where:

collection methods

Bulk Collect

PL/SQL - Bulk Collect - Fetch collection of (records|Collection)