Send ArraList to SP Oracle

Question

I have an ArrayList of Strings in my code Java, and I need to send this list to my sp in Oracle. I can´t set the list, because Array it's not compatible with ArrayList. How can I send this list?

public static boolean ejecutarPackageSeteado(String procedimiento,ArrayList<String> lista) throws Exception{            
    try{
        cs=getCallableStatement("{call "+procedimiento+"}");
        cs.setArray(1,lista);//THIS LINE SHOW ERROR         
        cs.execute();
    }catch(Exception e){
        System.out.println(e.getMessage());
        throw e;
    }finally{
        cs.close();
        CallableStatementConnection.close();
    }
    return true;
}

This is my PrepareStatement

cs=getCallableStatement("{call "+procedimiento+"}");

//procedimiento is ="INSERTAR.INSERTAR_RUTFORZADA(?)"

private static CallableStatement getCallableStatement(String sql) throws Exception{
        if(CallableStatementConnection==null || CallableStatementConnection.isClosed()){
            CallableStatementConnection = getConnection();
        }
        return CallableStatementConnection.prepareCall(sql);
    }

Possible duplicate of [Convert java.util.List into java.sql.Array](https://stackoverflow.com/questions/2880304/convert-java-util-liststring-into-java-sql-array) — azro, Sep 26 '17 at 12:52
@ΦXocę웃Пepeúpaツ its a interface called CallableStatement that extends PreparedStatement — Luis Pavez, Sep 26 '17 at 13:07
@Optional what yousay give this error "Type mismatch: cannot convert from String[] to Array". — Luis Pavez, Sep 26 '17 at 13:08
@LuisPavez I see it later. Do this `cs.setArray(1,CallableStatementConnection.createArrayOf(String, lista.toArray(new String[0]));` You may need to see if your connection variable is CallableStatementConnection or something different — Optional, Sep 26 '17 at 13:12
@Optional show a error in this line "createArrayOf" it says in spanish "Función no soportada", in english its something like "unsupported function" — Luis Pavez, Sep 26 '17 at 15:20
@LuisPavez See this https://docs.oracle.com/javase/tutorial/jdbc/basics/array.html#creating_array — Optional, Sep 26 '17 at 15:24

azro · Answer 1 · 2017-09-26T13:22:40.347

2

You need to adapt a little from java.util.ArrayList to java.sql.Array

Array sqlArray = connection.createArrayOf("VARCHAR", lista.toArray(new String[0]));
cs.setArray(1,sqlArray );

edited Sep 26 '17 at 13:22

answered Sep 26 '17 at 12:55

azro

53,056
7
34
70

this does not recognize the word String : "String cannot be resolved to a variable". – Luis Pavez Sep 26 '17 at 13:12
@azro now the error is other,in this line "createArrayOf" it says in spanish "Función no soportada", in english its something like "unsupported function". What do you think about this – Luis Pavez Sep 26 '17 at 15:17
@LuisPavez what is your SQL statement ? – azro Sep 26 '17 at 16:15

Send ArraList to SP Oracle

1 Answers1