How can I prevent this exception? java.sql.SQLException: Fail to convert to internal representation:

Jimmy picture Jimmy · Nov 24, 2010 · Viewed 16.3k times · Source

My code is throwing the above exception on the following line (line 2 of this) :

final ArrayDescriptor tParamArrayDescriptor = ArrayDescriptor.createDescriptor("MY_SYSTEM.T_PARAM_ARRAY", databaseHandler.getConnection());
final ARRAY oracleArray = new ARRAY(tParamArrayDescriptor, databaseHandler.getConnection(), myObjects.toArray());

Its giving me the following exception :

java.sql.SQLException: Fail to convert to internal representation: 

The myObjects is an ArrayList of the following POJO:

public class MyObject
{
    private String name;
    private String surname;

    private int age;

    ...

    // Accessors etc..

}

The T_PARAM_ARRAY on the database looks as follows :

create or replace
TYPE               T_PARAM_ARRAY AS OBJECT (NAME VARCHAR2(50), SURNAME VARCHAR2(50), AGE NUMBER(1));

After some research, I believe that the data type mapping between my POJO and the database type don't match up correctly. I'm reasonably confident that String is matching onto VARCHAR2 ok, but I think there is an issue converting the int to a NUMBER.

I've tried using BigDecimal, but that didn't improve the situation.

Any suggestions?

EDIT: According to the Oracle documentation : Where intArray is an oracle.sql.ARRAY, corresponding to a VARRAY of type NUMBER. The values array contains an array of elements of type java.math.BigDecimal, because the SQL NUMBER datatype maps to Java BigDecimal by default, according to the Oracle JDBC drivers.

Answer

Vincent Malgrat picture Vincent Malgrat · Nov 24, 2010

I think you need to build the array yourself, see this question "How to call oracle stored procedure which include user-defined type in java?" for a working example.