How to use bind variable for null value?

Question

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?

score 3 · Answer 1 · answered Jun 12 '17 at 09:12

3

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;

answered Jun 12 '17 at 09:12

Alex Poole

183,384
11
179
318

2

If `column2` is a date data type then it would be better to use ANSI date literal `DATE '1999-10-12'` rather relying on implicit conversion from a string using the `NLS_DATE_FORMAT` session parameter as the format model. – MT0 Jun 12 '17 at 09:37
Yes, didn't really look at the rest of the statement. Similarly if `column1` is a number data type, it should be assigned as numeric literal `102` rather than as a text literal `'102.0'`. – Alex Poole Jun 12 '17 at 09:43
How the execute immediate syntax would be as it giving below error if i use 7 bind variables in using clause : ORA-01008: not all variables boundprocess_track – Arpan Paliwal Jun 12 '17 at 12:55
@ArpanPaliwal - [you need to repeat the bind values](https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS631). Why are you using `execute immediate` here though, the statement you showed doesn't need to be dynamic? (Unless you're actually concatenating in the table or columns names, but you haven't shown that if so). – Alex Poole Jun 12 '17 at 13:34

score 0 · Answer 2 · answered Sep 12 '18 at 17:54

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.

Thomasso2000 · Answer 3 · 2022-04-08T10:16:27.373

0

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

How to use bind variable for null value?

3 Answers3