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 in the DataDirect Connect Series for JDBC Reference 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);