18 Working with Oracle Collections

18.1.1 Choices in Materializing Collections

In your application, you have the choice of materializing a collection as an instance of the oracle.sql.ARRAY class, which is weakly typed, or materializing it as an instance of a custom Java class that you have created in advance, which is strongly typed. Custom Java classes used for collections are referred to as custom collection classes. A custom collection class must implement the Oracle oracle.sql.ORAData interface. In addition, the custom class or a companion class must implement oracle.sql.ORADataFactory. The standard java.sql.SQLData interface is for mapping SQL object types only.

The oracle.sql.ARRAY class implements the standard java.sql.Array interface.

The ARRAY class includes functionality to retrieve the array as a whole, retrieve a subset of the array elements, and retrieve the SQL base type name of the array elements. However, you cannot write to the array, because there are no setter methods.

Custom collection classes, as with the ARRAY class, enable you to retrieve all or part of the array and get the SQL base type name. They also have the advantage of being strongly typed, which can help you find coding errors during compilation that might not otherwise be discovered until run time.

Furthermore, custom collection classes produced by JPublisher offer the feature of being writable, with individually accessible elements.

18.1.2 Creating Collections

Because Oracle supports only named collections, you must declare a particular VARRAY type name or nested table type name. VARRAY and nested table are not types themselves, but categories of types.

A SQL type name is assigned to a collection when you create it using the SQL CREATE TYPE statement:

CREATE TYPE <sql_type_name> AS <datatype>;

A VARRAY is an array of varying size. It has an ordered set of data elements, and all the elements are of the same data type. Each element has an index, which is a number corresponding to the position of the element 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 VARRAY 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 NUMBER values that can contain no more than 10 elements.

A nested table is an unordered set of data elements, all of the same data type. The database stores a nested table in a separate table which 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, then it can also be viewed as a multi-column table, with a column for each attribute of the object type. You can create a nested table as follows:

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.

18.1.3 Creating Multilevel Collection Types

The most common way to create a new multilevel collection type in JDBC is to pass the SQL CREATE TYPE statement to the execute method of the java.sql.Statement class. The following code creates a one-level nested table, first_level, and a two- levels nested table, second_level:

Connection conn = ....                            // make a database
                                                  // connection 
Statement stmt = conn.createStatement();          // open a database
                                                  // cursor 
stmt.execute("CREATE TYPE first_level AS TABLE OF NUMBER");  // create a nested
                                                  // table of number 
stmt.execute("CREATE second_level AS TABLE OF first_level"); // create a
           // two-levels nested table
...        // other operations here
stmt.close();                                     // release the
                                                  // resource 
conn.close();                                     // close the
                                                  // database connection

Once the multilevel collection types have been created, they can be used as both columns of a base table as well as attributes of a object type.

18.2.1 Array Getter and Setter Methods

Use the following result set, callable statement, and prepared statement methods to retrieve and pass collections as Java arrays.

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 an oracle.sql.ARRAY instance or a java.sql.Array instance.

18.2.2 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, that is, 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 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

  Is identical to getArray, but retrieves the elements in oracle.sql.* format.

• getBaseType

  Returns the SQL type code for the array elements.

• getBaseTypeName

  Returns the SQL type name of the elements of this array.

• getSQLTypeName

  Returns the fully qualified SQL type name of the array as a whole. This method is an Oracle extension.

• getResultSet

  Materializes the array elements as a result set.

• getJavaSQLConnection

  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.

  
  

18.3.1 Accessing oracle.sql.ARRAY Elements as Arrays of Java Primitive Types

The oracle.sql.ARRAY class contains methods that return array elements as Java primitive types. These methods allow you to access collection elements more efficiently than accessing them as Datum instances and then converting each Datum instance to its Java primitive value.

Here are the methods:

• public int[] getIntArray() throws SQLException

  public int[] getIntArray(long index, int count) throws SQLException

• public long[] getLongArray() throws SQLException

  public long[] getLongArray(long index, int count) throws SQLException

• public float[] getFloatArray() throws SQLException

  public float[] getFloatArray(long index, int count) throws SQLException

• public double[] getDoubleArray() throws SQLException

  public double[] getDoubleArray(long index, int count) throws SQLException

• public short[] getShortArray() throws SQLException

  public short[] getShortArray(long index, int count) throws SQLException

Each method using the first signature returns collection elements as an XXX[], where XXX is a Java primitive type. Each method using the second signature returns a slice of the collection containing the number of elements specified by count, starting at the index location.

18.3.2 ARRAY Automatic Element Buffering

The Oracle JDBC driver provides public methods to enable and disable buffering of ARRAY contents.

The following methods are included with the oracle.sql.ARRAY class:

• public void setAutoBuffering(boolean enable)

• public boolean getAutoBuffering()

The setAutoBuffering method enables or disables auto-buffering. The getAutoBuffering() method returns the current auto-buffering mode. By default, auto-buffering is disabled.

It is advisable to enable auto-buffering in a JDBC application when the ARRAY elements will be accessed more than once by the getAttributes and getArray methods, presuming the ARRAY data is able to fit into the Java virtual machine (JVM) memory without overflow.

When you enable auto-buffering, the oracle.sql.ARRAY object keeps a local copy of all the converted elements. This data is retained so that a second access of this information does not require going through the data format conversion process.

18.3.3 ARRAY Automatic Indexing

If an array is in auto-indexing mode, then the array object maintains an index table to hasten array element access.

The oracle.sql.ARRAY class contains the following methods to support automatic array-indexing:

• public synchronized void setAutoIndexing(boolean enable, int direction) throws SQLException

• public synchronized void setAutoIndexing(boolean enable) throws SQLException

The setAutoIndexing method sets the auto-indexing mode for the oracle.sql.ARRAY object. The direction parameter gives the array object a hint. Specify this parameter to help the JDBC driver determine the best indexing scheme. The following are the values you can specify for the direction parameter:

• ARRAY.ACCESS_FORWARD

• ARRAY.ACCESS_REVERSE

• ARRAY.ACCESS_UNKNOWN

The setAutoIndexing(boolean) method signature sets the access direction as ARRAY.ACCESS_UNKNOWN by default.

By default, auto-indexing is not enabled. For a JDBC application, enable auto-indexing for ARRAY objects if random access of array elements may occur through the getArray and getResultSet methods.

18.4.1 Creating ARRAY Objects and Descriptors

This section describes how to create ARRAY objects and descriptors and lists useful methods of the ArrayDescriptor class. This section covers the following topics:

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 for the array, if one does not already exist.

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 re-creating 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, you create a collection with the CREATE TYPE statement, as follows:

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

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 connect to the database to gather meta data, to the constructor.

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

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

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 Multilevel Collections

As with single-level collections, the JDBC application can create an oracle.sql.ARRAY instance to represent a multilevel 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 multilevel collection type. The second argument is the current database connection. And the third argument is a java.lang.Object that holds the multilevel collection elements. This is the same constructor used to create single-level collections, but can create multilevel 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 multilevel 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 preceding 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 type code of the array elements, and if it is an array of structured objects, then the SQL name of the elements. The descriptor also contains the information about converting 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 the type code and type name of an element:

• createDescriptor

  Is a factory for ArrayDescriptor instances. It looks up the name in the database and determine the characteristics of the array.

• getBaseType

  Returns the integer type code associated with this ARRAY descriptor.

• 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 that was used in creating the ARRAY descriptor.

  
  

  Note: In releases prior to Oracle9i Database, 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 Database and later releases, you can use a collection within a collection.

  
  

Serializable ARRAY Descriptors

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. Re-create 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, then 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 

18.4.2 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. This section covers the following topics:

• Retrieving the Array

• Data Retrieval Methods

• Comparing the Data Retrieval Methods

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

• Retrieving a Subset of Array Elements

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

• Accessing Multilevel Collection Elements

ResultSet rset = intArray.getResultSet();

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

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

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

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

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

Datum arraydata[] = arr.getOracleArray();

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

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

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

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

1. Construct an ArrayDescriptor object for the SQL type that the array will contain, as follows:

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

   sql_type_name is a Java string specifying the user-defined SQL type name of the array and connection is your Connection object.

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

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

4. Cast your prepared statement to OraclePreparedStatement, and use setARRAY to pass the array to the prepared statement.

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

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

5. Run the prepared statement.

Passing an Array to a Callable Statement

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

1. Cast your callable statement to OracleCallableStatement, as follows:

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

2. Register the OUT parameter with the following form of the registerOutParameter method:

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

   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.

3. Run the call, as follows:

   ocs.execute();
   
   

4. Get the value, as follows:

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