Select where id = array (Oracle JDBC)

Question

I'm currently working with trying to compare an ID in Oracle(A VARCHAR2) with an array of IDs I have as input.

This is what I want to do:

Select user, city where id = :arrayOfIds

In Postgres with JDBC I would use:

Select user, city where id = any(:arrayOfIds)

Is there an equivalent function in Oracle, or a way to do this?

NikNik · Accepted Answer · 2017-08-28 10:13:22Z

1

You should use:

Select user, city where id in (:arrayOfIds)

and in you code, you need to trasform your array in string with ids:

arrayOfIds[0] --> 1
arrayOfIds[1] --> 3
arrayOfIds[2] --> 5

... in

1, 3, 5, ...

and you can use:

Array array = conn.createArrayOf("arrayOfIds", new Object[]{"1", "2","3"});
pstmt.setArray(1, array);

answered Aug 28, 2017 at 8:06

NikNik

2,3012 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The Oracle JDBC driver does not support createArrayOf()