16 Working with Oracle Collections

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

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

• getJavaSQLConnection(): Returns the connection instance (java.sql.Connection) 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".

  
  

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.

CREATE TYPE person AS object 
         (c1 NUMBER(5), c2 VARCHAR2(30)); 
CREATE TYPE array_of_persons AS varray(10)
            OF 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.

  
  

Creating Multi-Level Collections

As with single-level collections, the JDBC application can create an oracle.sql.ARRAY instance to represent a multi-level collection, and then send the instance to the database. The oracle.sql.ARRAY constructor is defined as follows:

public ARRAY(ArrayDescriptor type, Connection conn, Object elements) 
throws SQLException

The first argument is an oracle.sql.ArrayDescriptor object that describes the multi-level collection type. The second argument is the current database connection. And the third argument is a java.lang.Object that holds the multi-level collection elements. This is the same constructor used to create single-level collections, but enhanced to create multi-level collections as well. The elements parameter can now be either a one dimension array or a nested Java array.

To create a single-level collection, the elements are a one dimensional Java array. To create a multi-level collection, the elements can be either an array of oracle.sql.ARRAY[] elements or a nested Java array or the combinations.

The following code shows how to create collection types with a nested Java array:

Connection conn = ...;          // make a JDBC connection 

// create the collection types 
Statement stmt = conn.createStatement (); 
stmt.execute ("CREATE TYPE varray1 AS VARRAY(10) OF NUMBER(12, 2)"); // one
                                                                     // layer 
stmt.execute ("CREATE TYPE varray2 AS VARRAY(10) OF varray1"); // two layers 
stmt.execute ("CREATE TYPE varray3 AS VARRAY(10) OF varray2"); // three layers 
stmt.execute ("CREATE TABLE tab2 (col1 index, col2 value)"); 
stmt.close (); 

// obtain a type descriptor of "SCOTT.VARRAY3" 
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("SCOTT.VARRAY3", conn); 

// prepare the multi level collection elements as a nested Java array 
int[][][] elems = { {{1}, {1, 2}}, {{2}, {2, 3}}, {{3}, {3, 4}} }; 

// create the ARRAY by calling the constructor 
ARRAY array3 = new ARRAY (desc, conn, elems); 

// some operations 
... 

// close the database connection 
conn.close();

In the above example, another implementation is to prepare the elems as a Java array of oracle.sql.ARRAY[] elements, and each oracle.sql.ARRAY[] element represents a SCOTT.VARRAY3.

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

• getJavaSQLConnection(): Returns the connection instance (java.sql.Connection) that was used in creating the ARRAY descriptor (a new descriptor must be created for each connection instance).

  
  

  Note: In releases prior to Oracle9i, you could not use a collection within a collection. You could, however, use a structured object with a collection attribute, or a collection with structured object elements. In Oracle9i and higher releases, you can use a collection within a collection.

  
  

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

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.

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.

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.

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.

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));
}

Accessing Multi-Level Collection Elements

The oracle.sql.ARRAY class provides three methods (which can be overloaded) to access collection elements. The JDBC drivers extend these methods to support multi-level collections. The three methods are the following:

• getArray() method : JDBC standard

• getOracleArray() method : Oracle extension

• getResultSet() method : JDBC standard

The getArray() method returns a Java array that holds the collection elements. The array element type is determined by the collection element type and the JDBC default conversion matrix.

For example, the getArray() method returns a java.math.BigDecimal array for collection of SQL NUMBER. The getOracleArray() method returns a Datum array that holds the collection elements in Datum format. For multi-level collections, the getArray() and getOracleArray() methods both return a Java array of oracle.sql.ARRAY elements.

The getResultSet() method returns a ResultSet object that wraps the multi-level collection elements. For multi-level collections, the JDBC applications use the getObject(), getARRAY(), or getArray() method of the ResultSet class to access the collection elements as instances of oracle.sql.ARRAY.

The following code shows how to use the getOracleArray(), getArray(), and getResultSet() methods:

Connection conn = ...;          // make a JDBC connection 
Statement stmt = conn.createStatement (); 
ResultSet rset = stmt.executeQuery ("select col2 from tab2 where idx=1"); 

while (rset.next()) 
{ 
  ARRAY varray3 = (ARRAY) rset.getObject (1); 
  Object varrayElems = varray3.getArray (1);  
// access array elements of "varray3" 
  Datum[] varray3Elems = (Datum[]) varrayElems; 

  for (int i=0; i<varray3Elems.length; i++) 
  { 
    ARRAY varray2 = (ARRAY) varray3Elems[i]; 
    Datum[] varray2Elems = varray2.getOracleArray(); 
    // access array elements of  "varray2" 

    for (int j=0; j<varray2Elems.length; j++) 
    { 
      ARRAY varray1 = (ARRAY) varray2Elems[j]; 
      ResultSet varray1Elems = varray1.getResultSet(); 
      // access array elements   of "varray1" 

      while (varray1Elems.next()) 
        System.out.println ("idx="+varray1Elems.getInt(1)+"
          value="+varray1Elems.getInt(2)); 
    } 
  } 
} 
rset.close (); 
stmt.close (); 
conn.close (); 

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 registerOutParameter() 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);