'How to use bind variable for null value?

I have below SQL with bind variables.

UPDATE Table1 SET column1='102.0', column2='12-OCT-1999', column3='AG'
WHERE column4 =:1 and column5 =:2 and column6 =:3 and column7 =:4
AND column8=:5 AND column9=:6 AND NVL(column10,0) <=:7;

When the 3rd and 4th bind variable values are NULL and the actual values in the table are also null it is not updating that record, as there should be "is NULL" over there. Please suggest how I can use bind variables in this case?

sqloraclebind-variables


Solution 1:[1]

As you said, you need to use is null, so you can do both possible checks with an or - i.e. - that the column value and the variable are both null, or they both not null (implicitly) and equal:

AND ((column7 IS NULL AND :4 IS NULL) OR column7 =:4)

Or in situ:

UPDATE Table1 SET column1='102.0', column2='12-OCT-1999', column3='AG'
WHERE column4 =:1
AND column5 =:2
AND column6 =:3
AND ((column7 IS NULL AND :4 IS NULL) OR column7 =:4)
AND ((column8 IS NULL AND :5 IS NULL) OR column8 =:5)
AND column9=:6
AND NVL(column10,0) <=:7;

Solution 2:[2]

I had a similar question on how to perform a NULL check when a bind variable references a set of values that will be either populated or empty using an Oracle DB. I needed a way to bypass the actual execution of the associated AND clause in those scenarios that data did not exist.

Here is a variation that shows how to handle a set of data that needs to either be processed or ignored.

AND ( (:user_id_set IS NULL)  OR  user_id IN (:user_id_set) )

In Oracle, if the tag_id_set is empty or NULL, it is considered the same. When true, it drops out without processing. When false (values exist in set), Oracle processes the AND clause as intended, and the tag_id will be checked.

Solution 3:[3]

Despite the previous solution is nice

where ( bind_variable IS NULL or table.column = bind_variable )

it will be really bad performing.

The optimizer looks at that OR and says -- hmm, maybe we will do the compare, maybe no -- no index for you (it would not be able to use an index on table.column effectively whereas the ref cursor with sys_context will)

Source: ASK Tom

See also this Oracle Blog

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Alex Poole
Solution 2 Michael M
Solution 3

Related Questions