Can I pass a list of String in the IN clause of sql statement(Oracle) using Java code

Question

Can I pass a list of String in the IN clause of sql statement(Oracle 12c) using Java code.

My code is given below:

    Connection con= abc.getConnection();
    OracleConnection oracleConnection = null;
    OraclePreparedStatement ops=null;
    if (con.isWrapperFor(OracleConnection.class)){
           oracleConnection= con.unwrap(OracleConnection.class);  
        }else{
           // recover, not an oracle connection
        }
    PreparedStatement ps=oracleConnection.prepareStatement(sql);
    if (ps.isWrapperFor(OraclePreparedStatement.class)){
        ops= ps.unwrap(OraclePreparedStatement.class);  
        }else{
           // recover, not an oracle connection
        }
    List<String >Ids=new ArrayList<String>();
    Ids.add("12345");
    Ids.add("12346");
    java.sql.Array array1 = oracleConnection.createOracleArray("MY_NESTED_TABLE", Ids.toArray());
    ops.setArray(1, array1 );
    ResultSet rSet= ops.executeQuery();

I have defined my Oracle Nested Table as:

create or replace TYPE MY_NESTED_TABLE AS TABLE OF VARCHAR2(8 BYTE);

And the sql queries I tried to execute are:

1. SELECT * FROM MY_TABLE where MY_COLUMN IN (select column_value v from table(?))
2. SELECT * FROM MY_TABLE where MY_COLUMN IN (select column_value v from table(cast(? AS MY_NESTED_TABLE)))

There is no exception, just that I get no data in resultset. I have seen people using this code working with PL/SQL. Should it work with a SQL statement as well?

Answer 1

Tested your approach and it is working fine. Please check if your mapping is correct and data is present in database.

Yes, you can use oracle arrays in sql statement.

How to create an oracle.sql.ARRAY object?