The Oracle VARRAY and TABLE data types map to the JDBC ARRAY data type. To use these data types as a parameter, an array must be created using the createArray() method of the ExtConnection interface. See "JDBC Extensions" for more information about this method.
Note: The VARRAY and TABLE data types are not supported for bulk load operations.
Note: The Oracle driver does not support using the VARRAY and TABLE data types with Oracle index-by tables, also known as associative arrays.
If you want more accurate metadata information for either of these data types, set the CatalogOptions connection property to 8, but be aware that this can negatively affect performance. See "CatalogOptions" for details.
Example A: Selecting Data From an Array
This example selects two columns, reads them into separate arrays, and retrieves the objects from the arrays.
Statement st = connection.createStatement();
ResultSet resultSet = st.executeQuery(
"SELECT array_col, table_col FROM complex_type_table");
// Loop through the result set and retrieve the objects
while(resultSet.next()) {
// Use getArray to read the value into an array
java.sql.Array myVarray = resultSet.getArray(1);
java.sql.Array myTable = resultSet.getArray(2);
// Call getArray() method to retrieve the array of objects
Object varrayValues[] = (Object[])myVarray.getArray();
Object tableValues[] = (Object[])myTable.getArray();
}
Example B: Inserting Data into an Array
This example constructs two separate arrays and inserts the array objects into their respective columns.
Object objArray[] = new Object[4];
objArray[0] = new java.math.BigDecimal (1);
objArray[1] = new java.math.BigDecimal (2);
objArray[2] = new java.math.BigDecimal (3);
objArray[3] = new java.math.BigDecimal (4);
// Construct the array for the VARRAY
java.sql.Array colVarray =
((com.ddtek.jdbc.extensions.ExtConnection)connection).createArray(
"NUM_VARRAY", objArray);
// Construct the array for the Table
java.sql.Array colTable =
((com.ddtek.jdbc.extensions.ExtConnection)connection).createArray(
"NUM_TABLE", objArray);
