Oracle PL/SQL Programming: A Developer's Workbook

Chapter 8. Variable Arrays

Beginner

Q:
8-1.	A variable array (VARRAY) is a collection type introduced in Oracle8. Variable arrays are similar to nested tables in that they are homogenous and indexed, but they’re very different in that they are never sparse and always maintain their order.
Q:
8-2.	You can use VARRAYs as variables in PL/SQL and as columns in database tables.
Q:
8-3.	You define the structure of a VARRRAY using the CREATE TYPE command introduced in Oracle8. For example: SQL> CREATE TYPE a_varray AS VARRAY(10) OF NUMBER; 2 / Type created.
Q:
8-4.	Variable arrays can store only a finite number of rows, as defined in the declaration. This finite number is called their limit and is specified when they are created. In the previous example, a_varray is a bounded array having a limit of 10 elements.
Q:
8-5.	It’s actually quite simple; you specify the column datatype as the variable array, just as you would specify any other data type. For example, assuming that a_varray has been previously declared as a VARRAY type in the database, you can create a VARRAY column using this command: SQL> CREATE TABLE a_table 2 (col1 NUMBER, 3 col2 a_varray ); Table created.
Q:
8-6.	The syntax to define a VARRAY is similar to that used to define an index-by table, with the exception that the INDEX BY clause is omitted: DECLARE TYPE x_t IS VARRAY(10) OF NUMBER; x x_t; BEGIN ... END;
Q:
8-7.	Most of the standard Oracle datatypes are available: SQL> CREATE TYPE a_number_array AS VARRAY(10) OF NUMBER(22); 2 / Type created. ...