JDBC Data Extension: how to read an Oracle Type with an array property

[raycurls]

I all!
How can I read an oracle type with an array property? The oracle type is the following:

create or replace type OBJ_USER as object
(
  ID_USER    NUMBER(15),
  NAME       VARCHAR2(50),
  SURNAME    VARCHAR2(50),
  BIRTH_DATE DATE,
 EMAIL      OBJ_EMAIL_ARRAY
) not final;

where OBJ_EMAIL_ARRAY is:

create or replace type OBJ_EMAIL_ARRAY is table of OBJ_EMAIL;

and OBJ_EMAIL is:

create or replace type OBJ_EMAIL as object
(
  ID_EMAIL      NUMBER(15),
  PRIORITY      NUMBER(3),
  EMAIL         VARCHAR2(100)
) final;

The Java mapping class has these properties (setter and getter omitted):

public class User {
	private Long idUser;
	private String name;
	private String surname;
	private Date birthDate;
	private List<Email> email;

If I try to read this object with this PL/SQL package function (it sets internally the array property):

function ReadUser(pIdUser in number) return Obj_user;

using this Java code:

    this.getUser =
	            new SimpleJdbcCall(dataSource).withProcedureName(SQL_READ_USER)
	                .declareParameters(
	                    new SqlOutParameter("PARAM_RESULT", OracleTypes.STRUCT, OBJ_USER,  
	                            new SqlReturnStruct(User.class)));		
		this.getUser.setFunction(true);
		this.getUser.setCatalogName(PACKAGE_NAME);


	@Override
	public User readUser(Long idUser) {
            Map in = Collections.singletonMap("pIdUser", idUser);
	    return getUser.executeObject(User.class, in); 		
	}

I get:
org.springframework.beans.ConversionNotSupportedEx ception: Failed to convert property value of type 'oracle.sql.ARRAY' to required type 'java.util.List' for property 'email'; nested exception is java.lang.IllegalStateException: Cannot convert value of type [oracle.sql.ARRAY] to required type [com.acme.oracletype.domain.Email] for property 'email[0]': no matching editors or conversion strategy found

Because of course nobody instructed SimpleJdbcCall how to convert the ARRAY of emails.

But how can be SimpleJdbcCall instructed to perform this conversion?

Thanks in advance!

[ChrLipp]

I have the same problem and was also not able to solve it.
After one day I found this link: http://stackoverflow.com/questions/6...dt-with-oracle and this implementation works (I used the first implementation of the accepted answer). I gave them both points :-)

[raycurls]

Thanks for your reply!
Your suggestion is useful and no doubt that both implementation will work. But I'm interested on an implementation based on Spring Data - JDBC Extension. Looking at http://static.springsource.org/sprin.../html/#d0e2178 it's quite easy to implement a read function of a simple oracle type . If I delete from my "Obj_user" the email property, everything works well:

                 this.getSimpleUser =
	            new SimpleJdbcCall(dataSource).withFunctionName(SQL_READ_SIMPLE_USER)
	                .declareParameters(
	                    new SqlOutParameter(PARAM_RESULT, OracleTypes.STRUCT, OBJ_USER_SIMPLE,  
	                            new SqlReturnStruct(SimpleUser.class)) 
	            );
		
		this.getSimpleUser.setCatalogName(PACKAGE_NAME);


       @Override
	public SimpleUser readSimpleUser(Long idUser) {		
	    Map in = Collections.singletonMap("pIdUser", idUser);
	    return getSimpleUser.executeObject(SimpleUser.class, in); 		
	}

So the question is: Spring Data - JDBC Extension is able to manage complex oracle types? In which way?