CODEWITHSUNDEEP
javasqlarraysoracle-databasearraylist
Shrikant Dande
Shrikant Dande

Reputation: 85

java.sql.SQLException: Fail to convert to internal representation: while passing ArrayList to Oracle.sql.ARRAY

Hi this is my code

List<String> listA = new ArrayList<String>(0);
String[] arrA = new String[100];
listA.add("aaa");
listA.add("bbb");
arrA[0]="aaa";
arrA[1]="bbb";
conn = DriverManager.getConnection(URL, USER, PASS);
oracle.jdbc.OracleConnection oraConn = (oracle.jdbc.OracleConnection) conn;
ArrayDescriptor des = ArrayDescriptor.createDescriptor("ARRAY_TABLE", oraConn);
//*****************
ARRAY array_to_pass1 = new ARRAY(des, conn, arrA );//* exception
CallableStatement cstm = (OracleCallableStatement) conn.prepareCall("{call xyz.procedure1(?,?)}");
cstm.setArray(1, array_to_pass1);
cstm.setInt(2, 1);


this is working fine but when I pass listA instead of arrA to ARRAY constructor..it is throwing above sql exception.. I want to pass Arraylist instead of string[] array

Upvotes: 3

Views: 10993

Answers (3)

troy
troy

Reputation: 2515

The Oracle's document about ARRAY is incomplete, and i find it's all pass string[] array in everywhere.

So I suggest you change the ArrayList to String[] array like that:

String[] arrA = listA.toArray(new String[listA.size()]);

Upvotes: 1

Jacob
Jacob

Reputation: 14741

Try this approach

List list = new ArrayList();
callableStatement = 
          (OracleCallableStatement)connection.prepareCall("{call yourprocedure(?)}");
            StructDescriptor structDescriptor = 
                StructDescriptor.createDescriptor("TYPE_ARRAY", 
                                                  connection);
            STRUCT[] structs = new STRUCT[list.size()];
            for (int i = 0; i < list.size(); ++i) {

                Yourclass str = 
                    (Yourclass)list.get(i);
                Object[] objects =

                    new Object[] { str.getProdId(), 
                                   str.getName()
                                 };
                STRUCT struct = 
                    new STRUCT(structDescriptor, connection, objects);
                structs[i] = struct;
            }
            ArrayDescriptor arrayDescriptor = 
                ArrayDescriptor.createDescriptor("ARRAY_MY", 
                                                 connection);

            ARRAY array = new ARRAY(arrayDescriptor, connection, structs);
            callableStatement.setArray(1, array);
            callableStatement.executeUpdate();

Upvotes: 1

MihaiC
MihaiC

Reputation: 1583

Convert your list to array before the line

ARRAY array_to_pass1 = new ARRAY(des, conn, arrA );

You can add/remove items from the list prior to this.

String[] arrA= new String[listA.size()];
arrA= listA.toArray(arrA);

Upvotes: 3

Related Questions