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".

---