Java Database Connectivity (JDBC)

Hi

I'm trying to pass in a Java array of beans to a PL/SQL proc and I can't quite get it to work. I did have the more simple case of an array of strings working but I'm stumped as to how to get this more complicated case to work.
I'm using Java 5 and Oracle 10.

My Oracle User Defined Types

create or replace type MY_OBJECT as object (
      id integer,
      join_table_name varchar(30)
);

create or replace type MY_OBJECT_ARRAY as table of MY_OBJECT;

My PL/SQL proc

create or replace package threshold is
  function validateThresholdSequence (
      thresholdSeqId integer,
      testValue number,
      testDate date,
      validationCriteria in MY_OBJECT_ARRAY
  ) return number;
end;

My Java

  public class ThresholdValidationCriteriaBean
  {
    private String joinTableName = null;
    private Integer id = null;
    
    //Getters and setters...
  }

  
  //Map my bean to the PL/SQL UDT - thought this might help but it seems not!
  Map<String, Class<?>> map = c.getTypeMap();
  map.put("MY_OBJECT", ThresholdValidationCriteriaBean.class);
    
  //Prepair my statement
  String sql=new String("{call threshold.validateThresholdSequence(?,?,?,?) }"); 
  ps= c.prepareStatement(sql); 
    
  // Set the values to insert 
  ps.setInt(1, thresholdSequenceId);
  ps.setDouble(2, testValue);
  ps.setDate(3, new java.sql.Date(date.getTime()));

  //Sort out the array thing
  ArrayDescriptor desc = ArrayDescriptor.createDescriptor("MY_OBJECT_ARRAY", c); 
  ThresholdValidationCriteriaBean[] beanArray = new ThresholdValidationCriteriaBean[validationCriteria.size()]; 
  validationCriteria.toArray(beanArray); 
  ARRAY array = new ARRAY (desc, c, beanArray); 
  ((oracle.jdbc.driver.OraclePreparedStatement)ps).setARRAY(4, array); 

When I run this I get the following error on the creation of the ARRAY object

java.sql.SQLException: Fail to convert to internal representation: uk.co.cartesian.ascertain.imm.threshold.ThresholdValidationCriteriaBean@15c7850
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
	at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTypeADT.java:239)
	at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(OracleTypeADT.java:274)
	at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(OracleTypeUPT.java:115)
	at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1314)
	at oracle.sql.ARRAY.<init>(ARRAY.java:152)

I've spent most of the day so far going from one error to the next - but I seem to be stuck now.

Any help or hints very much appreciated

Cheers

Ian

Edited by: Yanis on Feb 28, 2008 12:12 PM