Oracle的SQL支持两种形式的IN运算符

• 比如IN (1, 2, 3, :4)。这些值是标量，可以是常量或绑定变量，它们的数量必须在执行SQL之前定义/已知。
• 在子查询IN (select single_column from ...)中。在本例中，有一个子查询返回游标。

这两个选项都不直接适用于您。如果你运气好并且JDBC驱动程序支持它，你可以使用TABLE()操作符将集合转换为内存表：

SELECT * from example where tag in (select * from TABLE(?));

TABLE操作符将集合转换为表，子查询将返回游标。
看这个example：

import java.math.BigDecimal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.sql.DataSource;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

import oracle.jdbc.pool.OracleDataSource;

/**
 * Tests an SQL query that uses the IN operator and the TABLE keyword.
 * The TABLE keyword transforms a PL/SQL collection into a regular table.
 * Hence a query can use the IN operator with a PL/SQL collection.
 * A PL/SQL collection maps to a java array.
 * Hence you can execute a query using a <code>PreparedStatement</code> and an
 * array as the parameter value.
 * Note, however, that the PL/SQL collection type <em>must</em> be a database
 * named type, one that is defined using the CREATE TYPE [DDL] statement.
 */
public class SqlQuery {
  public static void main(String[] args) {
    Connection c = null;
    Object[] oa = new Object[3];
    oa[0] = new BigDecimal("9712061");
    oa[1] = new BigDecimal("9712062");
    oa[2] = new BigDecimal("9712063");
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "select ENAME " +
                   "from EMP " +
                  "where EMPNO in (" +
                                  "select * " +
                                    "from table(?)" +
                                 ")";
    String url = "jdbc:oracle:thin:scott/tiger@//localhost:1521/orcl";
    try {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL(url);
      c = ods.getConnection();
      ArrayDescriptor ad = ArrayDescriptor.createDescriptor(
                                                       "<nested table type name>",
                                                       c);
      ARRAY a = new ARRAY(ad, c, (Object) oa);
      ps = c.prepareStatement(sql);
      ps.setArray(1, a);

      /* overcome deprecated ArrayDescriptor 
      int[] values = { 1, 2, 3 };
      Array javaSqlArray = ((OracleConnection) conn).createOracleArray("<nested table type name>", values);
      ps.setObject(1, javaSqlArray);
      */
      rs = ps.executeQuery();
      while (rs.next()) {
        System.out.println(rs.getString(1));
      }
    }
    catch (Exception x) {
      x.printStackTrace();
    }
    finally {
      if (rs != null) {
        try {
          rs.close();
        }
        catch (Exception x) {
          System.err.println("Failed to close result set.");
          x.printStackTrace();
        }
      }
      if (ps != null) {
        try {
          ps.close();
        }
        catch (Exception x) {
          System.err.println("Failed to close statement.");
          x.printStackTrace();
        }
      }
      if (c != null) {
        try {
          c.close();
        }
        catch (Exception x) {
          System.err.println("Failed to close database connection.");
          x.printStackTrace();
        }
      }
    }
  }
}

更新：@gouessej，我确实检查了Metalink笔记（文档ID 2563257.1）和（文档ID 1364193.1），现在看起来Oracle JDBC将不支持Connection. createArrayOf。

int[] intArray = { 5, 7, 9};
oracle.sql.ArrayDescriptor aDescriptor = new oracle.sql.ArrayDescriptor("<user>.TYPE1", connection);
oracle.sql.ARRAY array = new oracle.sql.ARRAY(aDescriptor, connection, intArray);

Should be changed to:

int[] intArray = { 5, 7, 9};
java.sql.Array array = (OracleConnection)connection.createOracleArray("<user>.TYPE1", intArray);

注意：Oracle不支持匿名数组类型，因此不支持标准的Connection.createArrayOf方法。
我认为原因是PL/SQL受到ADA的启发，而ADA是强类型语言。Oracle没有String数组的泛型类型，用户必须为每次使用非标量类型创建一个命名类型。