Page 1 of 1
Giving NULL value to a parameter
Posted: Wed 15 Jul 2015 07:19
by albourgz
Using c++ builder xe8 on win 7x64 to build win 32 vcl apps, odac professional 9.5.17.
I have the following sql in TOraQuery *q:
Code: Select all
UPDATE tab SET mycol=:0 WHERE searchKey=:1 AND tab IS NULL AND col2=:2
col2 is number(9) null.
By setting
I expected to update tab when col2 is null. It is NOT the case. If I want to filter on null in an update, I have to prepare another query with "... AND col2 is null" at the end.
Is there any way to specify oracle's NULL value to a TOraQuery Parameter?
Regards.
Re: Giving NULL value to a parameter
Posted: Wed 15 Jul 2015 08:27
by AlexP
Hello,
For such a case, when a parameter can be either NULL or have a value, you should modify the query in the following way:
Code: Select all
UPDATE tab SET mycol=:p0 WHERE searchKey=:p1 AND tab IS NULL AND (col2=:p2 OR (:p2 Is Null and col2is null))
Re: Giving NULL value to a parameter
Posted: Tue 28 Jul 2015 08:39
by m.ghilardi
Oracle uses ternary logic: null = null is not true, is null.
In PL/SQL this can lead to interesting (and potentially dangerous) behaviour
Code: Select all
declare
a number := null;
b number := 0;
begin
if a = b then
a := 0;
elsif not(a = b) then
a := 1;
else
a := 2;
end if;
-- a is 2 !
end;
Re: Giving NULL value to a parameter
Posted: Tue 28 Jul 2015 09:43
by AlexP
No, when comparing with a NULL value using the '=' symbol instead of IS, Oracler returns UNKNOWN instead of NULL (
http://docs.oracle.com/cd/B19306_01/ser ... nts005.htm ). So your sample returns 2. The following is the fixed sample returning the correct value:
Code: Select all
declare
a number := null;
b number := 0;
begin
if a = b then
a := 0;
elsif not(a = b) then
a := 1;
elsif a = null then
a := 2;
elsif a is null then
a := 3;
else
a := 4;
end if;
DBMS_OUTPUT.put_line(TO_CHAR(a)); -- a is 3 !
end;
Re: Giving NULL value to a parameter
Posted: Tue 28 Jul 2015 10:14
by m.ghilardi
Hi AlexP,
I don't know why the documentation says that the evaluation is UNKNOWN. Whatever UNKNOWN is, it pretty much behaves as NULL.
Look:
Code: Select all
declare
a number := null;
b number := 0;
begin
if (a = b) = true then
a := 0;
elsif (not(a = b)) = true then
a := 1;
elsif (a = b) is null then
a := 2;
else
a := 3;
end if;
-- a is 2
end;
If you swap a and b in the conditions, the result is the same.
Re: Giving NULL value to a parameter
Posted: Tue 28 Jul 2015 11:11
by AlexP
This is a specificity of Oracle behavior and is not related to our components. See more details for comparison of NULL values in the Oracle documentation.