I asked RAmakrishna Menon, author of:

</code> http://www.apress.com/book/bookDisplay.html?bID=346 <code>

to look at this and he said:
...
> OK Tom.
>
> I am using PreparedStatement to select a collection from a table (ouch!) in my example using
> weakly typed Struct.
>
> If you need Strongly typed you will need to use jpub and I can give you an example for
> that if required.
>
> --------
> First the example schema for the case where we use PreparedStatement to select a
> collection from a table using weakly typed Array of Struct objects.
>
> This means that the collection would materialize as an "ARRAY" object
> each element of which would be a "STRUCT" object.
>
> benchmark@ORA10G> set echo on
> benchmark@ORA10G> drop table t1;
>
> Table dropped.
>
> benchmark@ORA10G> drop type nt_of_a;
>
> Type dropped.
>
> benchmark@ORA10G> drop type object_a;
>
> Type dropped.
>
> benchmark@ORA10G> create or replace type object_a as object
> 2 (
> 3 name varchar2(50),
> 4 id number
> 5 )
> 6 /
>
> Type created.
>
> benchmark@ORA10G> show errors;
> No errors.
> benchmark@ORA10G> create or replace type nt_of_a as table of object_a;
> 2 /
>
> Type created.
>
> benchmark@ORA10G> show errors;
> No errors.
> benchmark@ORA10G> create table t1
> 2 (
> 3 array_of_a nt_of_a
> 4 )
> 5 nested table array_of_a store as array_of_a_table;
>
> Table created.
>
> -- populate it with some data to select...
>
> benchmark@ORA10G> declare
> 2 l_array_of_a nt_of_a;
> 3 begin
> 4 l_array_of_a := nt_of_a
> 5 (
> 6 object_a( 'x', 1 ),
> 7 object_a( 'y', 2 )
> 8 );
> 9 insert into t1 values ( l_array_of_a );
> 10 commit;
> 11 end;
> 12 /
>
> PL/SQL procedure successfully completed.
>
> benchmark@ORA10G> show errors;
> No errors.
> benchmark@ORA10G> commit;
>
> Commit complete.
>
> benchmark@ORA10G> spool off
> So, if you are looking to use PreparedStatement to select from a table that
> contains collection, then the following class should work. Basically, you use
> getArray() on ResultSet to get the array object itself. Then you use
> either getArray() or getResultSet() on the resulting array to materialize
> each member as a Struct object. It is more or less from the book but
> modified it to remove dependencies on some utility classes
> (e.g. to get connection etc.)
> ----
> /** This program demonstrates how to select a collection of objects into
> * JDBC - and how by default they materialize in Java as
> * oracle.sql.STRUCT objects.
> * COMPATIBLITY NOTE:
> * runs successfully against 9.2.0.1.0 and 10.1.0.2.0
> */
> import java.sql.DriverManager;
> import oracle.jdbc.OracleDriver;
> import java.sql.Struct;
> import java.sql.SQLException;
> import java.sql.Connection;
> import java.sql.Array;
> import java.sql.PreparedStatement;
> import java.sql.Types;
> import java.sql.ResultSet;
> import oracle.sql.ARRAY;
> import oracle.jdbc.OracleConnection;
> import oracle.jdbc.OracleResultSet;
>
> class DemoCollectionOfObjectTypes
> {
> public static void main(String args[]) throws Exception
> {
> Connection conn = null;
> PreparedStatement pstmt = null;
> ResultSet rset = null;
> try
> {
> DriverManager.registerDriver( new OracleDriver());
> String thinDriverURL = "jdbc:oracle:thin:@rmenon-lap:1521:ora10g";
> conn = DriverManager.getConnection (
> thinDriverURL, "benchmark", "benchmark" );
> // Step 1 - prepare the statement
> String stmtString = "select array_of_a from t1";
> pstmt = conn.prepareStatement( stmtString );
> // Step 2 - execute the statement and get the result set
> rset = pstmt.executeQuery();
> while( rset.next() )
> {
> // instead of the following you could also use:
> // ARRAY array = ((OracleResultSet ) rset).getARRAY(1);
> Array array = rset.getArray(1);
> _doUseGetArray( array );
> _doUseResultSet( array );
> }
> }
> finally
> {
> // release JDBC resources
> try
> {
> rset.close();
> pstmt.close();
> conn.close();
> }
> catch( Exception e ) { } // ignore
> }
> }
> private static void _doUseGetArray( Array array )
> throws SQLException
> {
> System.out.println("In _doUseGetArray");System.out.flush();
> Object[] arrayInJava = (Object[])array.getArray();
> for( int i=0; i < arrayInJava.length; i++ )
> {
> Struct structObject = (Struct) (arrayInJava[i]);
> Object[] attributes = structObject.getAttributes();
> for( int j=0; j < attributes.length; j++ )
> {
> System.out.println(attributes[j]);
> }
> System.out.println();
> }
> System.out.println("Exiting _doUseGetArray");System.out.flush();
> }
> private static void _doUseResultSet( Array array )
> throws SQLException
> {
> System.out.println("In _doUseResultSet");
> ResultSet rset = null;
> try
> {
> rset = array.getResultSet();
> while( rset.next() )
> {
> int index = rset.getInt( 1 );
> Struct structObject = (Struct) rset.getObject( 2 );
> Object[] attributes = structObject.getAttributes();
> for( int j=0; j < attributes.length; j++ )
> {
> System.out.println(attributes[j]);
> }
> System.out.println();
>
> }
> }
> finally
> {
> try
> {
> rset.close();
> }
> catch( Exception e ) { } // ignore
> }
> System.out.println("Exiting _doUseResultSet");
> }
> }
>
> ----
> ---------------------------------------------------------------------------------------------------------
> Let me know if you need a jpub example..