Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## Overview of Collection (Array) Functionality

You can obtain collection data in an array instance through a result set or callable statement and pass it back as a bind variable in a prepared statement or callable statement.

The `oracle.sql.ARRAY` class, which implements the standard `java.sql.Array` interface (`oracle.jdbc2.Array` interface under JDK 1.1.x), provides the necessary functionality to access and update the data of an Oracle collection (either a VARRAY or nested table).

This section discusses the following:

- statement and result set getter and setter methods for passing collections to and from the database as Java arrays

- `ARRAY` descriptors and `ARRAY` class methods

Remember that you can use custom collection classes instead of the `ARRAY` class. See "Custom Collection Classes with JPublisher".

### Array Getter and Setter Methods

Use the following result set, callable statement, and prepared statement methods to retrieve and pass collections as Java arrays. Code examples are provided later in the chapter.

##### Result Set and Callable Statement Getter Methods

The `OracleResultSet` and `OracleCallableStatement` classes support `getARRAY()` and `getArray()` methods to retrieve `ARRAY` objects as output parameters--either as `oracle.sql.ARRAY` instances or `java.sql.Array` instances (`oracle.jdbc2.Array` under JDK 1.1.x). You can also use the `getObject()` method. These methods take as input a `String` column name or `int` column index.

##### Prepared and Callable Statement Setter Methods

The `OraclePreparedStatement` and `OracleCallableStatement` classes support `setARRAY()` and `setArray()` methods to take updated `ARRAY` objects as bind variables and pass them to the database. You can also use the `setObject()` method. These methods take as input a `String` parameter name or `int` parameter index as well as, respectively, an `oracle.sql.ARRAY` instance or a `java.sql.Array` instance (`oracle.jdbc2.Array` under JDK 1.1.x).

### ARRAY Descriptors and ARRAY Class Functionality

The section introduces `ARRAY` descriptors and lists methods of the `ARRAY` class to provide an overview of its functionality.

#### ARRAY Descriptors

Creating and using an `ARRAY` object requires the existence of a descriptor--an instance of the `oracle.sql.ArrayDescriptor` class--to exist for the SQL type of the collection being materialized in the array. You need only one `ArrayDescriptor` object for any number of `ARRAY` objects that correspond to the same SQL type.

`ARRAY` descriptors are further discussed in "Creating ARRAY Objects and Descriptors".

#### ARRAY Class Methods

The `oracle.sql.ARRAY` class includes the following methods:

- `getDescriptor()`: Returns the `ArrayDescriptor` object that describes the array type.

- `getArray()`: Retrieves the contents of the array in "default" JDBC types. If it retrieves an array of objects, then `getArray()` uses the default type map of the database connection object to determine the types.

- `getOracleArray()`: Identical to `getArray()`, but retrieves the elements in `oracle.sql.*` format.

- `getBaseType()`: Returns the SQL typecode for the array elements (see "Class oracle.jdbc.driver.OracleTypes" for information about typecodes).

- `getBaseTypeName()`: Returns the SQL type name of the elements of this array.

- `getSQLTypeName()` (Oracle extension): Returns the fully qualified SQL type name of the array as a whole.

- `getResultSet()`: Materializes the array elements as a result set.

- `getConnection()`: Returns the connection instance associated with this array.

- `length()`: Returns the number of elements in the array.

  Note: As an example of the difference between `getBaseTypeName()` and `getSQLTypeName()`, if you define `ARRAY_OF_PERSON` as the array type for an array of `PERSON` objects in the `SCOTT` schema, then `getBaseTypeName()` would return "SCOTT.PERSON" and `getSQLTypeName()` would return "SCOTT.ARRAY_OF_PERSON".

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index