Java Programming with Oracle SQLJ

5.5. Accessing Collections Using SQLJ

In this section, you will learn how to retrieve, add, and modify collections using a SQLJ program. The steps for retrieving and adding VARRAYs are the same as those for retrieving and adding nested tables. However, the steps for modifying a VARRAY differ slightly from those for modifying a nested table. This is because the elements in a nested table can be modified individually, unlike the elements stored in a VARRAY.

As I guide you through the steps for retrieving and adding collections, I will use the VARRAY stored in the addresses column in the customers3 table in my SQLJ examples. Because the steps for retrieving and adding collections are the same, the examples shown also apply to the nested table stored in the addresses column of the customers5 table (with the appropriate changes for the custom class name and the table name). When I get to the section on modifying collections, I point out the differences between modifying a VARRAY and modifying a nested table.

5.5.1. Retrieving a Collection

To retrieve a column containing a collection, you must perform the following three steps:

1. Declare a host object. A host object is conceptually the same as a host variable, except that it is a Java object. Because this host object will be used to hold a collection, it must be declared using the appropriate custom class.

2. Retrieve the column containing the collection into the host object using a SELECT INTO statement.

3. Read the contents of the host ...