Oracle VARRAY (Variable Array)

Oracle VARRAY (Variable Array) is a SQL data type in the Oracle Specific category. Oracle: VARRAY is a bounded array type with a fixed maximum size. Like nested tables but with size limit. The syntax is CREATE TYPE type_name AS VARRAY(n) OF datatype;. It returns variable array. A typical example: CREATE TYPE colors_varray AS VARRAY(5) OF VARCHAR2(20); / CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR2(100), available_colors colors_varray ); INSERT INTO products VALUES ( 1, 'Widget', colors_varray('Red', 'Blue', 'Green') ); -- Access elements: SELECT p.name, p.available_colors(1) AS first_color FROM products p; -- Widget | Red -- Unlike nested tables: -- VARRAY preserves order -- VARRAY has max size (n) -- VARRAY cannot be indexed -- Both can use… A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c).