OR clause in sql query with single matching condition

Question

Can I use OR claue in oracle with single input condition as below snippet ( ? is a single input parameter) :

Select t1.name, t1.value from table t1 where t1.name ='abc' and (t1.col OR 
t1.value =? );

Answer 1

You can use an IN clause for that:

Select t1.name, t1.value 
from table t1 
where t1.name = 'abc' 
  and ? in (t1.col, t1.value);

Answer 2

There are several ways to do it:

Use an IN clause:

SELECT name, value
FROM   table
WHERE  name ='abc'
AND    ? IN ( col, value );

or, use a sub-query:

SELECT name, value
FROM   table t
       INNER JOIN
       ( SELECT ? AS input_var FROM DUAL ) v
       ON ( t.col = v.input_var OR t.value = v.input_var )
WHERE  name ='abc';

Answer 3

If you want to reuse a value in a query from your Java code you could consider using a prepared statement:

String sql = "select t1.name, t1.value from table t1 where t1.name =? and (t1.col = ? or t1.value = ?)";
int someVal = 10;
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString("abc");
ps.setInt(2, someVal);
ps.setInt(3, someVal);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    String name = rs.getString("name");
    String value = rs.getString("value");
}

If attempting a similar thing directly from Oracle, you could use a session variable:

declare
    some_val number := 10;
begin
    select t1.name, t1.value
    from table t1
    where t1.name ='abc' and (t1.col = some_val or t1.value = some_val);
end;
/