16 Working with Oracle Collections

Retrieving the Array

You can retrieve a SQL array from a result set by casting the result set to OracleResultSet 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 oracle.sql.ARRAY.

Data Retrieval Methods

Once you have 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.

getOracleArray

The getOracleArray method is an Oracle-specific extension that is not specified in the standard Array interface. The getOracleArray method retrieves the element values of the array into a Datum[] array. The elements are of the oracle.sql.* data type 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 default type map of the connection to determine the mapping between the SQL type of the Oracle object and its corresponding Java data type. If you do not want to use the default type map of the connection, 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 as a java.lang.Object, which you can cast as appropriate. 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, then the use by that method of oracle.sql.Datum instances avoids the expense of data conversion from SQL to Java. The non-character data inside the instance of a Datum class or any of its subclass remains in raw SQL format.

If you use getResultSet to return an array of primitive data types, 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 and the second column stores the element value.

If the elements of an array are of a SQL type that maps to a Java type, then getArray returns an array of elements of this Java type. The return type of the getArray method is java.lang.Object. Therefore, the result must be cast before it can be used.

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

Here 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 data type maps to Java BigDecimal, by default, according to 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 data types according to the default mapping. This is because these methods use the default type map of the connection 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.

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 previously described, 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 data type of the elements of the original array. For example:

Datum arraydata[] = arr.getOracleArray();

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 OracleResultSet; 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 NUM_ARRAY are of the SQL data type NUMBER, the elements of my_array are of the type, BigDecimal. Before you can use the elements, they must first be cast to BigDecimal. 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 type code " + 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, then you must would first get the result set object, and 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 Multilevel Collection Elements

The oracle.sql.ARRAY class provides three methods, which are overloaded, to access collection elements. The JDBC drivers extend these methods to support multilevel collections. These methods are:

• getArray method

• getOracleArray method

• getResultSet method

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 multilevel 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 multilevel collection elements. For multilevel 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 ();