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

Question

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

My code is 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 defined my nested Oracle table as:

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

And the sql queries I tried to execute are as follows:

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

There is no exception, I just do not get any data in the result set. I have seen people use this code to work with PL / SQL. Should it work with SQL statement?

Answer 1

, . , .

, oracle sql.

oracle.sql.ARRAY?