Retrieving a 2d varray in Java with JDBC

Question

I have the following types:

create or replace TYPE mytimestamp AS VARRAY(300) OF TIMESTAMP(6);
create or replace TYPE trade_history_dts_array AS VARRAY(300) OF   mytimestamp;

The types are used in this way in the stored procedure:

trade_history_dts tradehistorydtsarray ;

FOR i IN 1..20
loop
  trade_history_dts(i) := mytimestamp();
    LOOP   
      trade_history_dts(i).extend();
      FETCH trade_history_cursor INTO 
      trade_history_dts(i)(j),
      dbms_output.put_line(trade_history_dts(i)(j)); 

      j := j+1;
      exit when trade_history_cursor%notfound;
    END LOOP;
    j:=1;
end loop;

The stored procedure is tested, and I can get the right the result:

information for trade 1

trade_history_dts(1)(1)17-OCT-05 03.49.57.000000 PM
trade_history_dts(1)(2)17-OCT-05 03.49.58.000000 PM

information for trade 2

trade_history_dts(2)(1)27-JUN-05 09.02.59.000000 AM
trade_history_dts(2)(2)27-JUN-05 09.02.59.000000 AM
trade_history_dts(2)(3)27-JUN-05 09.03.01.000000 AM

information for trade 3

trade_history_dts(3)(1)09-FEB-06 09.31.03.000000 AM
trade_history_dts(3)(2)09-FEB-06 09.31.05.000000 AM

....

Now I want to get the this result in java, and transform it to a 2-dimensional array.

I tried the following:

Timestamp[][] trade_history_dts = (Timestamp[][])trade_history_dts_arr.getArray();

but it does not work. Does anybody know how to transform it to a 2-dimensional array in java? Thank you!

score 4 · Accepted Answer · answered Jun 01 '11 at 03:28

The approach that you've adopted will not work, because JDBC on it's own does not have any support for Oracle's collection types. You'll need to work on objects of type contained in the Oracle JDBC driver for accessing the contents of the resultset in the manner that you desire. This would involve casting the ResultSet object into an OracleResultSet so that the inner elements of the collection can be accessed via instances of oracle.sql.ARRAY elements.

You could also read an Object from the ResultSet using the getObject method, and then cast it into the oracle.sql.ARRAY instance which represents the collection. Details on this mechanism, can be found in the Oracle Database JDBC Developer's Guide and Reference.

Additionally, you'll need to parse each element in the result set,and process them in a similar manner, for you are returning a multi-level collection. Details for this are provided in the same guide in a separate section.

Retrieving a 2d varray in Java with JDBC

1 Answers1