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

## Creating and Using Arrays

This section discusses how to create array objects and how to retrieve and pass collections as array objects, including the following topics.

- Creating ARRAY Objects and Descriptors

- Retrieving an Array and Its Elements

- Passing Arrays to Statement Objects

### Creating ARRAY Objects and Descriptors

This section describes how to create `ARRAY` objects and descriptors and lists useful methods of the `ArrayDescriptor` class.

#### Steps in Creating ArrayDescriptor and ARRAY Objects

This section describes how to construct an `oracle.sql.ARRAY` object. To do this, you must:

1. Create an `ArrayDescriptor` object (if one does not already exist) for the array.

2. Use the `ArrayDescriptor` object to construct the `oracle.sql.ARRAY` object for the array you want to pass.

An `ArrayDescriptor` is an object of the `oracle.sql.ArrayDescriptor` class and describes the SQL type of an array. Only one array descriptor is necessary for any one SQL type. The driver caches `ArrayDescriptor` objects to avoid recreating them if the SQL type has already been encountered. You can reuse the same descriptor object to create multiple instances of an `oracle.sql.ARRAY` object for the same array type.

Collections are strongly typed. Oracle supports only named collections, that is, a collection given a SQL type name. For example, when you create a collection with the `CREATE TYPE` statement:

    CREATE TYPE num_varray AS varray(22) OF NUMBER(5,2);

Where `NUM_VARRAY` is the SQL type name for the collection type.

Note: The name of the collection type is not the same as the type name of the elements. For example:
    CREATE TYPE person AS object
                (c1 NUMBER(5), c2 VARCHAR2(30));
    CREATE TYPE array_of_persons AS varray(10)
                OF person;
In the preceding statements, the SQL name of the collection type is `ARRAY_OF_PERSON`. The SQL name of the collection elements is `PERSON`.

Before you can construct an `Array` object, an `ArrayDescriptor` must first exist for the given SQL type of the array. If an `ArrayDescriptor` does not exist, then you must construct one by ``passing the SQL type name of the collection type and your `Connection` object (which JDBC uses to go to the database to gather meta data) to the constructor.

    ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor
                                               (sql_type_name, connection);

Where `sql_type_name` is the type name of the array and `connection` is your `Connection` object.

Once you have your `ArrayDescriptor` object for the SQL type of the array, you can construct the `ARRAY` object. To do this, pass in the array descriptor, your connection object, and a Java object containing the individual elements you want the array to contain.

    ARRAY array = new ARRAY(arraydesc, connection, elements);

Where `arraydesc` is the array descriptor created previously, `connection` is your connection object, and `elements` is a Java array. The two possibilities for the contents of `elements` are:

- an array of Java primitives--for example, `int[]`

- an array of Java objects, such as `xxx``[]` where `xxx` is the name of a Java class--for example, `Integer[]`

  Note: The `setARRAY()`, `setArray()`, and `setObject()` methods of the `OraclePreparedStatement` class take an object of the type `oracle.sql.ARRAY` as an argument, not an array of objects.

#### Using ArrayDescriptor Methods

An `ARRAY` descriptor can be referred to as a type object. It has information about the SQL name of the underlying collection, the typecode of the array's elements, and, if it is an array of structured objects, the SQL name of the elements. The descriptor also contains the information on about to convert to and from the given type. You need only one descriptor object for any one type, then you can use that descriptor to create as many arrays of that type as you want.

The `ArrayDescriptor` class has the following methods for retrieving an element's typecode and type name:

- `createDescriptor()`: This is a factory for `ArrayDescriptor` instances; looks up the name in the database and determine the characteristics of the array.

- `getBaseType()`: Returns the integer typecode associated with this `ARRAY` descriptor (according to integer constants defined in the `OracleTypes` class, which "Package oracle.jdbc.driver" describes).

- `getBaseName()`: Returns a string with the type name associated with this array element if it is a `STRUCT` or `REF`.

- `getArrayType()`: Returns an integer indicating whether the array is a VARRAY or nested table. `ArrayDescriptor.TYPE_VARRAY` and `ArrayDescriptor.TYPE_NESTED_TABLE` are the possible return values.

- `getMaxLength()`: Returns the maximum number of elements for this array type.

- `getConnection()`: Returns the connection instance that was used in creating the `ARRAY` descriptor (a new descriptor must be created for each connection instance).

  Note: You cannot use a collection within a collection. You can, however, use a structured object with a collection attribute, or a collection with structured object elements.

#### Serializable ARRAY Descriptors

As "Steps in Creating ArrayDescriptor and ARRAY Objects" discusses, when you create an `ARRAY` object, you first must create an `ArrayDescriptor` object. Create the `ArrayDescriptor` object by calling the `ArrayDescriptor.createDescriptor()` method. In Oracle8i release 8.1.7, the `oracle.sql.ArrayDescriptor` class is serializable, meaning that you can write the state of an `ArrayDescriptor` object to an output stream for later use. Recreate the `ArrayDescriptor` object by reading its serialized state from an input stream. This is referred to as deserializing. With the `ArrayDescriptor` object serialized, you do not need to call the `createDescriptor()` method--simply deserialize the `ArrayDescriptor` object.

It is advisable to serialize an `ArrayDescriptor` object when the object type is complex but not changed often.

If you create an `ArrayDescriptor` object through deserialization, you must provide the appropriate database connection instance for the `ArrayDescriptor` object using the `setConnection()` method.

The following code furnishes the connection instance for an `ArrayDescriptor` object:

    public void setConnection (Connection conn) throws SQLException

Note: The JDBC driver does not verify that the connection object from the `setConnection()` method connects to the same database from which the type descriptor was initially derived.

### Retrieving an Array and Its Elements

This section first discusses how to retrieve an `ARRAY` instance as a whole from a result set, and then how to retrieve the elements from the `ARRAY` instance.

#### Retrieving the Array

You can retrieve a SQL array from a result set by casting the result set to an `OracleResultSet` object and using the `getARRAY()` method, which returns an `oracle.sql.ARRAY` object. If you want to avoid casting the result set, then you can get the data with the standard `getObject()` method specified by the `java.sql.ResultSet` interface, and cast the output to an `oracle.sql.ARRAY` object.

#### Data Retrieval Methods

Once you have the array in an `ARRAY` object, you can retrieve the data using one of these three overloaded methods of the `oracle.sql.ARRAY` class:

- `getArray()`

- `getOracleArray()`

- `getResultSet()`

Oracle also provides methods that enable you to retrieve all the elements of an array, or a subset.

Note: In case you are working with an array of structured objects, Oracle provides versions of these three methods that enable you to specify a type map so that you can choose how to map the objects to Java.

##### getOracleArray()

The `getOracleArray()` method is an Oracle-specific extension that is not specified in the standard `Array` interface (`java.sql.Array` under JDK 1.2.x or `oracle.jdbc2.Array` under JDK 1.1.x). The `getOracleArray()` method retrieves the element values of the array into a `Datum[]` array. The elements are of the `oracle.sql.*` datatype corresponding to the SQL type of the data in the original array.

For an array of structured objects, this method will use `oracle.sql.STRUCT` instances for the elements.

Oracle also provides a `getOracleArray(``index``,``count``)` `method` to get a subset of the array elements.

##### getResultSet()

The `getResultSet()` method returns a result set that contains elements of the array designated by the `ARRAY` object. The result set contains one row for each array element, with two columns in each row. The first column stores the index into the array for that element, and the second column stores the element value. In the case of VARRAYs, the index represents the position of the element in the array. In the case of nested tables, which are by definition unordered, the index reflects only the return order of the elements in the particular query.

Oracle recommends using `getResultSet()` when getting data from nested tables. Nested tables can have an unlimited number of elements. The `ResultSet` object returned by the method initially points at the first row of data. You get the contents of the nested table by using the `next()` method and the appropriate `getXXX()` method. In contrast, `getArray()` returns the entire contents of the nested table at one time.

The `getResultSet()` method uses the connection's default type map to determine the mapping between the SQL type of the Oracle object and its corresponding Java datatype. If you do not want to use the connection's default type map, another version of the method, `getResultSet(``map``)`, enables you to specify an alternate type map.

Oracle also provides the `getResultSet(``index``,``count``)` and `getResultSet(``index``,``count``,``map``)` methods to retrieve a subset of the array elements.

##### getArray()

The `getArray()` method is a standard JDBC method that returns the array elements into a `java.lang.Object` instance that you can cast as appropriate (see "Comparing the Data Retrieval Methods"). The elements are converted to the Java types corresponding to the SQL type of the data in the original array.

Oracle also provides a `getArray(``index``,``count``)` method to retrieve a subset of the array elements.

#### Comparing the Data Retrieval Methods

If you use `getOracleArray()` to return the array elements, the use by that method of `oracle.sql.Datum` instances avoids the expense of data conversion from SQL to Java. The data inside a `Datum` (or subclass) instance remains in raw SQL format.

If you use `getResultSet()` to return an array of primitive datatypes, then the JDBC driver returns a `ResultSet` object that contains, for each element, the index into the array for the element and the element value. For example:

    ResultSet rset = intArray.getResultSet();

In this case, the result set contains one row for each array element, with two columns in each row. The first column stores the index into the array; the second column stores the element value.

If you use `getArray()` to retrieve an array of primitive datatypes, then a `java.lang.Object` that contains the element values is returned. The elements of this array are of the Java type corresponding to the SQL type of the elements. For example:

    BigDecimal[] values = (BigDecimal[]) intArray.getArray();

Where `intArray` is an `oracle.sql.ARRAY`, corresponding to a VARRAY of type `NUMBER`. The `values` array contains an array of elements of type `java.math.BigDecimal`, because the SQL `NUMBER` datatype maps to Java `BigDecimal` by default, according to the Oracle JDBC drivers.

Note: Using `BigDecimal` is a resource-intensive operation in Java. Because Oracle JDBC maps numeric SQL data to `BigDecimal` by default, using `getArray()` may impact performance, and is not recommended for numeric collections.

#### Retrieving Elements of a Structured Object Array According to a Type Map

By default, if you are working with an array whose elements are structured objects, and you use `getArray()` or `getResultSet()`, then the Oracle objects in the array will be mapped to their corresponding Java datatypes according to the default mapping. This is because these methods use the connection's default type map to determine the mapping.

However, if you do not want default behavior, then you can use the `getArray(``map``)` or `getResultSet(``map``)` method to specify a type map that contains alternate mappings. If there are entries in the type map corresponding to the Oracle objects in the array, then each object in the array is mapped to the corresponding Java type specified in the type map. For example:

    Object[] object = (Object[])objArray.getArray(map);

Where `objArray` is an `oracle.sql.ARRAY` object and `map` is a `java.util.Map` object.

If the type map does not contain an entry for a particular Oracle object, then the element is returned as an `oracle.sql.STRUCT` object.

The `getResultSet(``map``)` method behaves similarly to the `getArray(``map``)` method.

For more information on using type maps with arrays, see "Using a Type Map to Map Array Elements".

#### Retrieving a Subset of Array Elements

If you do not want to retrieve the entire contents of an array, then you can use signatures of `getArray()`, `getResultSet()`, and `getOracleArray()` that let you retrieve a subset. To retrieve a subset of the array, pass in an index and a count to indicate where in the array you want to start and how many elements you want to retrieve. As described above, you can specify a type map or use the default type map for your connection to convert to Java types. For example:

    Object object = arr.getArray(index, count, map);
    Object object = arr.getArray(index, count);

Similar examples using `getResultSet()` are:

    ResultSet rset = arr.getResultSet(index, count, map);
    ResultSet rset = arr.getResultSet(index, count);

A similar example using `getOracleArray()` is:

    Datum arr = arr.getOracleArray(index, count);

Where `arr` is an `oracle.sql.ARRAY` object, `index` is type `long`, `count` is type `int`, and `map` is a `java.util.Map` object.

Note: There is no performance advantage in retrieving a subset of an array, as opposed to the entire array.

#### Retrieving Array Elements into an oracle.sql.Datum Array

Use `getOracleArray()` to return an `oracle.sql.Datum[]` array. The elements of the returned array will be of the `oracle.sql.*` type that correspond to the SQL datatype of the elements of the original array. For example:

    Datum arraydata[] = arr.getOracleArray();

Where `arr` is an `oracle.sql.ARRAY` object. For an example of retrieving an array and its contents, see "Weakly Typed Arrays--ArrayExample.java".

The following example assumes that a connection object `conn` and a statement object `stmt` have already been created. In the example, an array with the SQL type name `NUM_ARRAY` is created to store a VARRAY of `NUMBER` data. The `NUM_ARRAY` is in turn stored in a table `VARRAY_TABLE`.

A query selects the contents of the `VARRAY_TABLE`. The result set is cast to an `OracleResultSet` object; `getARRAY()` is applied to it to retrieve the array data into `my_array`, which is an `oracle.sql.ARRAY` object.

Because `my_array` is of type `oracle.sql.ARRAY`, you can apply the methods `getSQLTypeName()` and `getBaseType()` to it to return the name of the SQL type of each element in the array and its integer code.

The program then prints the contents of the array. Because the contents of `my_array` are of the SQL datatype `NUMBER`, it must first be cast to the `BigDecimal` datatype. In the `for` loop, the individual values of the array are cast to `BigDecimal` and printed to standard output.

    stmt.execute ("CREATE TYPE num_varray AS VARRAY(10) OF NUMBER(12, 2)");
    stmt.execute ("CREATE TABLE varray_table (col1 num_varray)");
    stmt.execute ("INSERT INTO varray_table VALUES (num_varray(100, 200))");

    ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table");
    ARRAY my_array = ((OracleResultSet)rs).getARRAY(1);

    // return the SQL type names, integer codes,
    // and lengths of the columns
    System.out.println ("Array is of type " + array.getSQLTypeName());
    System.out.println ("Array element is of typecode " + array.getBaseType());
    System.out.println ("Array is of length " + array.length());

    // get Array elements
    BigDecimal[] values = (BigDecimal[]) my_array.getArray();

    for (int i=0; i<values.length; i++)
    {
       BigDecimal out_value = (BigDecimal) values[i];
       System.out.println(">> index " + i + " = " + out_value.intValue());
    }

Note that if you use `getResultSet()` to obtain the array, you would first get the result set object, then use the `next()` method to iterate through it. Notice the use of the parameter indexes in the `getInt()` method to retrieve the element index and the element value.

    ResultSet rset = my_array.getResultSet();
    while (rset.next())
    {
        // The first column contains the element index and the
        // second column contains the element value
        System.out.println(">> index " + rset.getInt(1)+" = " + rset.getInt(2));
    }

### Passing Arrays to Statement Objects

This section discusses how to pass arrays to prepared statement objects or callable statement objects.

#### Passing an Array to a Prepared Statement

Pass an array to a prepared statement as follows (use similar steps to pass an array to a callable statement). Note that you can use arrays as either `IN` or `OUT` bind variables.

1. Construct an `ArrayDescriptor` object for the SQL type that the array will contain (unless one has already been created for this SQL type). See "Steps in Creating ArrayDescriptor and ARRAY Objects" for information about creating `ArrayDescriptor` objects.

       ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor
                                          (sql_type_name, connection);

   Where `sql_type_name` is a Java string specifying the user-defined SQL type name of the array, and `connection` is your `Connection` object. See "Oracle Extensions for Collections (Arrays)" for information about SQL typenames.

2. Define the array that you want to pass to the prepared statement as an `oracle.sql.ARRAY` object.

       ARRAY array = new ARRAY(descriptor, connection, elements);

   Where `descriptor` is the `ArrayDescriptor` object previously constructed and `elements` is a `java.lang.Object` containing a Java array of the elements.

3. Create a `java.sql.PreparedStatement` object containing the SQL statement to execute.

4. Cast your prepared statement to an `OraclePreparedStatement` and use the `setARRAY()` method of the `OraclePreparedStatement` object to pass the array to the prepared statement.

       (OraclePreparedStatement)stmt.setARRAY(parameterIndex, array);

   Where `parameterIndex` is the parameter index, and `array` is the `oracle.sql.ARRAY` object you constructed previously.

5. Execute the prepared statement.

#### Passing an Array to a Callable Statement

To retrieve a collection as an `OUT` parameter in PL/SQL blocks, execute the following to register the bind type for your `OUT` parameter.

1. Cast your callable statement to an `OracleCallableStatement`:

       OracleCallableStatement ocs =
          (OracleCallableStatement)conn.prepareCall("{? = call func()}");

2. Register the `OUT` parameter with this form of the `regsiterOutParameter()` method:

       ocs.registerOutParameter
               (int param_index, int sql_type, string sql_type_name);

   Where `param_index` is the parameter index, `sql_type` is the SQL typecode, and `sql_type_name` is the name of the array type. In this case, the `sql_type` is `OracleTypes.ARRAY`.

3. Execute the call:

       ocs.execute();

4. Get the value:

       oracle.sql.ARRAY array = ocs.getARRAY(1);

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