Oracle Extensions for Collections (Arrays)

An Oracle collection--either a variable array (VARRAY) or a nested table in the database--maps to an array in Java. JDBC 2.0 arrays are used to materialize Oracle collections in Java. The terms "collection" and "array" are sometimes used interchangeably, although "collection" is more appropriate on the database side, and "array" is more appropriate on the JDBC application side.

Oracle supports only named collections, where you specify a SQL type name to describe a type of collection.

JDBC lets you use arrays as any of the following:

• columns in a SELECT-list

• IN or OUT bind variables

• attributes in an Oracle object

The rest of this section discusses creating and materializing collections.

The remainder of the chapter describes how to access and update collection data through Java arrays. For a complete code example of creating a table with a collection column and then manipulating and printing the contents, see "Weakly Typed Arrays--ArrayExample.java".

Choices in Materializing Collections

In your application, you have the choice of materializing a collection as an instance of the oracle.sql.ARRAY class, which is weakly typed, or materializing it as an instance of a custom Java class that you have created in advance, which is strongly typed. Custom Java classes used for collections are referred to as custom collection classes in this manual. A custom collection class must implement the Oracle oracle.sql.CustomDatum interface. In addition, the custom class or a companion class must implement oracle.sql.CustomDatumFactory. (The standard java.sql.SQLData interface is for mapping SQL object types only.)

The oracle.sql.ARRAY class implements the standard java.sql.Array interface (oracle.jdbc2.Array under JDK 1.1.x).

The ARRAY class includes functionality to retrieve the array as a whole, retrieve a subset of the array elements, and retrieve the SQL base type name of the array elements. You cannot write to the array, however, as there are no setter methods.

Custom collection classes, as with the ARRAY class, allow you to retrieve all or part of the array and get the SQL base type name. They also have the advantage of being strongly typed, which can help you find coding errors during compilation that might not otherwise be discovered until runtime.

Furthermore, custom collection classes produced by JPublisher offer the feature of being writable, with individually accessible elements. (This is also something you could implement in a custom collection class yourself.)

Note: There is no difference in your code between accessing VARRAYs and accessing nested tables. ARRAY class methods can determine if they are being applied to a VARRAY or nested table, and respond by taking the appropriate actions.

For more information about custom collection classes, see "Custom Collection Classes with JPublisher".

Creating Collections

This section presents background information about creating Oracle collections.

Because Oracle supports only named collections, you must declare a particular VARRAY type name or nested table type name. "VARRAY" and "nested table" are not types themselves, but categories of types.

A SQL type name is assigned to a collection when you create it, as in the following SQL syntax:

CREATE TYPE <sql_type_name> AS <datatype>;

A VARRAY is an array of varying size. It has an ordered set of data elements, and all the elements are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the VARRAY. The number of elements in a VARRAY is the "size" of the VARRAY. You must specify a maximum size when you declare the VARRAY type. For example:

CREATE TYPE myNumType AS VARRAY(10) OF NUMBER;

This statement defines myNumType as a SQL type name that describes a VARRAY of NUMBER values that can contain no more than 10-elements.

A nested table is an unordered set of data elements, all of the same datatype. The database stores a nested table in a separate table which has a single column, and the type of that column is a built-in type or an object type. If the table is an object type, it can also be viewed as a multi-column table, with a column for each attribute of the object type. Create a nested table with this SQL syntax:

CREATE TYPE myNumList AS TABLE OF integer;

This statement identifies myNumList as a SQL type name that defines the table type used for the nested tables of the type INTEGER.

---