Search code examples
sqloraclejdbcprepared-statementoracle18c

Catch-all value for a parameter in the WHERE clause of a Prepared Statement?

I have the following Prepared Statement which I send via the JDBC driver to an Oracle database:

SELECT * FROM mytable WHERE ? = ANY(col1, col2)

Now, normally when I execute this as a query I provide a value which exists in col1 or col2 and Oracle dutifully returns all rows containing the value.

However I would like to use the same prepared statement to fetch all rows in the table by providing something like a wildcard parameter. I tried Strings like "*" and "" but it always returns no rows.

Does a value for the parameter in this prepared statement exist with which I fetch all rows?

Solution

  • You could try:

    where nvl(?, col1) in (col1, col2)

    and pass a null in if you want all values.