Oracle Extensions

Working with Arrays

This section has these subsections:

The oracle.sql.ARRAY class enables you to access and manipulate arrays and their data within a JDBC program. The oracle.sql.ARRAY class implements the oracle.jdbc2.Array interface.

JDBC provides support for arrays as any of the following:

columns in a SELECT list
IN or OUT bind variables
attributes in an Oracle object

Note:
The term arrays in JDBC 2.0 is equivalent to collections in Oracle terminology.

Arrays include varrays (variable-length arrays) and nested tables. The methods in the oracle.sql.ARRAY class enable you to access and manipulate the array and its data even if it is a varray or nested table. That is, you do not have to add any special code when you are accessing a varray or nested table. The methods can determine if they are being applied to a varray or nested table, and respond by taking the appropriate actions.

Oracle supports only named arrays, where you specify a SQL type name to describe a type of array. The SQL type name is assigned to the array when you create it, as in the following SQL syntax:

CREATE TYPE <sql_type_name> AS <datatype>

The array can be either a nested table or a varray.

A varray is an array of varying size, thus the name "varray". A varray has an ordered set of data elements. All elements of a given varray are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the varray. The number of elements in a varray is the size of the varray. You must specify a maximum size when you declare the array type. For example:

CREATE TYPE myNumType AS VARRAY(10) OF NUMBER;

This statement defines myNumType as a SQL type name that describes a varray of NUMBERs that can contain no more than 10-elements.

A nested table is an unordered set of data elements, all of the same datatype. It has a single column, and the type of that column is a built-in type or an object type. If the table is an object type, it can also be viewed as a multi-column table, with a column for each attribute of the object type. Create a nested table with this SQL syntax:

CREATE TYPE myNumList AS TABLE OF integer;

This statement identifies myNumList as a SQL type name that defines the table type used for the nested tables of the type integer.

The remainder of this section describes how to access and update array data. For general information about the oracle.sql.ARRAY class, including how to manually create array objects, see "Class oracle.sql.ARRAY". For a complete code example of creating a table with an array column, then manipulating and printing the contents, see "Array Sample".

Retrieving an Array and its Elements

When you retrieve an array you get an oracle.sql.ARRAY object where each array element can be returned as a materialized Java array object or as a result set object.

You can retrieve a SQL array that has been selected into 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, you can get the data with the getObject() method of the oracle.sql.ResultSet class, then cast the output to oracle.sql.ARRAY.

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 provides versions of these methods that enable you to specify a type map so you can choose how you want your SQL datatypes to map to Java datatypes. Oracle also provides methods that enable you to retrieve all of an array's elements or a subset of the array (but note, there is no performance advantage in retrieving a subset of an array as opposed to retrieving the entire array).

Note:
Beginning in release 8.1.5, arrays are indexed from 1. In previous releases, arrays were indexed from 0.

getArray() Method:

The getArray() method retrieves the element values of the array into a java.lang.Object[] array. The elements are converted to the Java types corresponding to the SQL type of the data in the original array.

The getArray() materializes the data as an array of oracle.sql.* objects and does not use a type map. Oracle also provides a getArray(map) method to let you specify a type map and a getArray(index,count) method to retrieve a subset of the array.

getOracleArray() Method:

The getOracleArray() method retrieves the element values of the array into a Datum[] array. The elements are converted to the oracle.sql.* datatype corresponding to the SQL type of the data in the original array.

Note:
The getOracleArray() method is an Oracle-specific extension and does not belong to the oracle.jdbc2.ARRAY JDBC 2.0 interface.

The getOracleArray() method materializes the data as an array of oracle.sql.* objects and does not use the type map. Oracle also provides the getOracleArray(index,count).

getResultSet() Method:

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 that you use 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.

Retrieving All of an Array's Elements

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.

Similarly, if you use getResultSet() to return an array of primitive datatypes, then the JDBC drivers return 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 BigDecimal element value.

Retrieving Array Elements According to a Type Map

By default, if 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.

The getResultSet(map) method behaves in a similar manner to getArray(map).

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

Retrieving a Subset of an Array's Elements

To retrieve a subset of the array, you can 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 an Array as an oracle.sql.Datum

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 SQL array elements. 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 "Array Sample".

Example: Getting and Printing an Array of Primitive Datatypes from a Result Set

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 object of type oracle.sql.ARRAY.

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 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, 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 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):

Construct an ArrayDescriptor object for the SQL type that the array will contain (unless one has already been created for this SQL type). See "Class oracle.sql.ARRAY" 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 "Working with Arrays" for information about SQL typenames.
Define the array that you want to pass to the prepared statement as an oracle.sql.ARRAY object.
```
ARRAY array = new ARRAY(descriptor, elements);
```
where descriptor is the ArrayDescriptor object previously constructed and elements is a java.lang.Object containing a Java array of the elements. These objects are converted to raw bytes of the appropriate SQL type.
Create a java.sql.PreparedStatement object containing the SQL statement to execute.
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.
Execute the prepared statement.

Note:
You can use arrays as either IN or OUT bind variables.

Passing an Array to a Callable Statement

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

Cast your callable statement to an OracleCallableStatement:

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

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 type code, and sql_type_name is the name of the array type. In this case, the sql_type is OracleTypes.ARRAY.
Execute the query:
```
ocs.executeQuery()
```

Using a Type Map to Map Array Elements

If your array contains Oracle objects, then you can use a type map to associate each object in the array with its corresponding Java class. If you do not specify a type map or if the type map does not contain an entry for a particular Oracle object, then the element is returned as an oracle.sql.STRUCT.

If you want the type map to determine the mapping between the Oracle objects in the array and their associated Java classes, then you must add them to the type map if they are not already there. For instructions on how to add entries to an existing type map or how to create a new type map, see "Understanding Type Maps".

The following example illustrates how you can use a type map to map the elements of an array to a custom Java object class. In this case, the array is a nested table. The example begins by defining an EMPLOYEE object that has a name attribute and employee number attribute. EMPLOYEE_LIST is a nested table type of EMPLOYEE objects. Then an EMPLOYEE_TABLE is created to store the names of departments within a corporation and the employees associated with each department. In the EMPLOYEE_TABLE, the employees are stored in the form of EMPLOYEE_LIST tables.

stmt.execute("CREATE TYPE EMPLOYEE AS OBJECT(EmpName VARCHAR2(50),EmpNo 
INTEGER))");

stmt.execute("CREATE TYPE EMPLOYEE_LIST AS TABLE OF EMPLOYEE");

stmt.execute("CREATE TABLE EMPLOYEE_TABLE (DeptName VARCHAR2(20), Employees 
EMPLOYEE_LIST) NESTED TABLE Employees STORE AS ntable1");

stmt.execute("INSERT INTO EMPLOYEE_TABLE VALUES ("SALES", 
EMPLOYEE_LIST(EMPLOYEE('Susan Smith', 123), EMPLOYEE('Scott Tiger', 124)))");

If you want to select all of the employees belonging to the SALES department as the custom Java object EmployeeObj, then you must create a mapping in the type map between the EMPLOYEE SQL type and the EmployeeObj custom Java object class.

To do this, first create your statement and result set objects, then select the EMPLOYEE_LIST associated with the SALES department into the result set. Cast the result set to OracleResultSet so that the getARRAY() method can retrieve the EMPLOYEE_LIST object into the employeeArray object.

Note:
The EmployeeObj custom Java object type in this example implements the SQLData interface. "Creating the Custom Java Class" contains the code that creates the EmployeeObj type.

Statement s = conn.createStatement();
OracleResultSet rs = (OracleResultSet) 
     s.executeQuery("SELECT Employees FROM employee_table 
          WHERE DeptName = 'SALES'");

// get the array object 
ARRAY employeeArray = ((OracleResultSet)rs).getARRAY(1);

Now that you have the EMPLOYEE_LIST object, get the existing type map and add an entry that maps the EMPLOYEE SQL type to the EmployeeObj Java type.

// add type map entry to map SQL type 
// "EMPLOYEE" to Java type "EmployeeObj" 
Dictionary map = conn.getTypeMap();
map.put("EMPLOYEE", Class.forName("EmployeeObj"));

Retrieve the SQL EMPLOYEE objects from the EMPLOYEE_LIST. To do this, apply the getArray() method of the oracle.jdbc2.Array class to employeeArray. This method returns an array of objects. The getArray() method returns the EMPLOYEE objects into the employees object array.

// Retrieve array elements 
Object[] employees = (Object[]) employeeArray.getArray();

Finally, create a loop to assign each of the EMPLOYEE SQL objects to the EmployeeObj Java object emp.

// Each array element is mapped to EmployeeObj object.
for (int i=0; i<employees.length; i++)
   {
     EmployeeObj emp = (EmployeeObj) employees[i];
     ...
   }