Recall that our example last week tracked engine repairs and used a
varray to track the condition of the spark plugs that were pulled
from the engine. The structure we had created looked like the
following:

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)
/

So how do we manipulate this table? Fortunately, it's very similar
to the SQL that we all know and love. We simply do an insert
statement like this:

Insert into engine_condition values (
        Sysdate,
        3,
        spark_plug_va(?GOOD?,?BAD?,?FAIR?,?FAIR?));

So where did this spark_plug_va function come from? Oracle
automatically created a constructor function for us when we
created the varray. Notice we only specified four values, even
though there is room for up to 12 values. You can specify any
number of values up to the maximum. If you go over the declared
maximum, then you will get the error:

        ORA-22909: exceeded maximum VARRAY limit

So remember, if there's ever a chance that you'll need more than
the defined amount, either make it bigger or use nested or
separate tables. Next week we'll discuss how to get your data out
of the varray.