insert Command for varray

Unlike nested tables, varrays are ordered and bound collections. That is, the number of elements within a varray is limited to the number specified when you create this type. In this lesson, we will look at the two different ways of inserting data into a varray:

1. A single DML statement to insert the data into the main table and into the varray
2. A single DML statement to insert the data into the main table and empty data into the varray

Using the first technique, inserting the data into the main table and the varray, you must specify the data for the main table and the name of the varray and, within parentheses, specify the data for the varray. For example, let us insert data into the CUSTOMER_OBJ_TABLE table, which has PHONE_LIST as a varray:

INSERT INTO CUSTOMER_OBJ_TABLE VALUES
(CUSTOMER_TYPE (1 ,'Amy' ,'Black' ,
ADDRESS_TYPE('100 West Main' ,NULL, 'Omaha' ,'NE','98765'),
PHONE_ARRAY('1-808-876-6543','1-088-209-0987'),
'22-DEC-99','JANET'));

INSERT INTO CUSTOMER_OBJ_TABLE VALUES 
(CUSTOMER_TYPE (1 ,'Amy' ,'Black' ,
ADDRESS_TYPE('100 West Main' ,NULL, 'Omaha' ,'NE','98765'),
PHONE_ARRAY(), '22-DEC-99','JANET'));

In the example above, we inserted an empty record instead of a NULL value into a varray. The data inserted into the varray can be inserted later via an UPDATE statement. We will learn more about updating varrays later in this module.
Although it is possible to insert individual elements into nested tables, it is not possible to insert individual elements into a varray.
Inserting Row into Varray
In the next lesson, you will learn more about updating data in nested tables.

Now that you have learned how to insert rows into nested table and varray, try it with the following tutor-based exercise. Click the exercise link below to write an INSERT statement to insert a row into the nested table within the SALE_HEADER table.
Inserting Rows into Varray - Exercise