Varrays is a type of collector that was introduced with Oracle8.
It’s useful if you have a fixed number of repeating columns that
you want to represent without using a child table. If the number
of elements isn’t known or is variable, then a nested or separate
table would be a better solution.
For example, let’s say you’re tracking engine repairs and want to
record the condition of the spark plugs that have been changed.
You could create the following data structure:
Create or replace type spark_plug_va as varray(12) of varchar2(12)
/
Create table engine_condition(
Check_date date,
Cust_id number,
Spark_plugs spark_plug_va)
/
You now have created a structure to hold spark plug information for
up to a 12-cylinder engine. Note that the varray type we created
uses the Oracle-defined datatype of number. We could have also used
a complex user-defined datatype that had previously been defined.
For example:
Create or replace type spark_plug_tracker_type as object (
spark_plug_position number,
spark_plug_brand varchar2(25),
spark_plug_comment varchar2(50))
/
Create or replace type spark_plug_va2 as varray(12) of
spark_plug_tracker_type
/
We now have created a varray collection of spark_plug_tracker_type
objects.