|Oracle8i JDBC Developer's Guide and Reference
Release 3 (8.1.7)
Part Number A83724-01
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:
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".
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.)
oracle.sql.ARRAY class implements the standard
java.sql.Array interface (
oracle.jdbc2.Array under JDK 1.1.x).
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.)
For more information about custom collection classes, see "Custom Collection Classes with JPublisher".
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:
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:
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:
This statement identifies
myNumList as a SQL type name that defines the table type used for the nested tables of the type