How to Write 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.

The VARRAY and NESTED TABLE collections can be defined as both SQL and PL/SQL datatypes. As SQL datatypes, they are single-dimensional arrays of scalar values or object types. They can also define user-specified column datatypes. Both the VARRAY and NESTED TABLE datatypes are structures indexed by sequential integers (using 1-based integers). Sequentially indexed structures disallow gaps in the index values, and are also known as densely populated structures. While the VARRAY has a fixed number of elements when defined, the NESTED TABLE does not. Oracle varray and nested table collections are indexed by 1-based numbering.

The associative array, previously known as a PL/SQL table, is only a PL/SQL datatype. Associative array datatypes can only be referenced in a PL/SQL scope. They are typically defined in package specifications when they are to be used externally from an anonymous or named block program. Associative array datatypes support both numeric and string indexes. Numeric indexes for associative arrays do not need to be sequential and are non-sequential structures. Non-sequential structures can have gaps in index sequences and are known as sparsely populated structures. Associative arrays are dynamically sized and, like the NESTED TABLE datatype, have no fixed size. All three have access to the Oracle Collection API, but each uses a different set of methods. The recent changes to OCI8 enable it to support scalar, arrays of scalar, and reference cursor variables to external languages, like C, C++, C#, Java, and PHP. The VARRAY and NESTED TABLE datatypes require that you use the OCI-Collection class to access them externally from the SQL*Plus environment. OCI8 also has a new function that supports passing by reference a PL/SQL table.

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