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'));

In the example above, we inserted two elements into the varray and also inserted data into the other columns of the object table.
Using the second technique, it is also possible to have a blank record for a varray while inserting data into the main table. For example, we will use the INSERT statement mentioned above to insert a blank record into a varray:

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

1. With SQL*Plus, connect to the database by using PETSTORE as the username, GREATPETS as the password, and MYDB as the host string.
2. You are now connected to the database. Next, build an INSERT statement to insert a record into the varray within the CUSTOMER_OBJ_TABLE table. The values for the record are as follows: cust_id is 56, name of the person is Norma Grey residing at 100 South East Main Burbank CA 65443 having the following phone numbers: 1-800-876-3243 and 1-714-785-8763. Joan last updated the record on 12/22/99.
3. SQL*Plus now displays the result of the DML statement.

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.

• Associative array:
  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
In the next lesson, you will learn more about updating data in nested tables.