Giving NULL value to a parameter

[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

Code: Select all

q->Params->Items[2]->Clear();

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.

[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))

[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;

[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;

[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.

[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.