Hello guys I was wondering anyone knows why this pl/sql code give a result false but I was excpeting the result will be raise exception but I didn't get it why like that nvl protect us from null value as I know.

declare
i_send_type varchar2(100) := null;
begin
    if nvl(i_send_type,'') <> 'cash' and nvl(i_send_type,'') <> 'card'  then
      raise_application_error(-20032,'o_userMessage');
  end if; 
end;

CodePudding user response：

That's because an empty string - in Oracle - is equivalent of NULL, which means that NVL actually did nothing; it said

if i_send_type is NULL, then take empty string (i.e. NULL again) instead

In that case, you compared NULL to 'cash' and 'card' using <>, but NULL can't be compared that way - you have to use IS NULL or IS NOT NULL.

So, what to do?

One option: don't use empty string in NVL but e.g. 'x' (or some other string that can't be contents of i_send_type:

SQL> declare
  2    i_send_type varchar2(100) := null;
  3  begin
  4      if nvl(i_send_type,'x') <> 'cash' and nvl(i_send_type,'x') <> 'card'  then
  5        raise_application_error(-20032,'o_userMessage');
  6      end if;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-20032: o_userMessage
ORA-06512: at line 5


SQL>

Another (better) option: check - as I said - whether variable's value IS NULL:

SQL> declare
  2    i_send_type varchar2(100) := null;
  3  begin
  4      if i_send_type not in ('cash', 'card') or i_send_type is null then
  5        raise_application_error(-20032,'o_userMessage');
  6      end if;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-20032: o_userMessage
ORA-06512: at line 5


SQL>

CodePudding user response：

why this pl/sql code give a result false

It does not. It gives a result as not true.

Not true is only the same as false in binary logic where there are two possible values, true or false. In databases (and not just Oracle), they use trinary logic and can have the values: true, false or unknown (NULL).

For example, if you compare the = operator:

Then NULL = <anything> or <anything> = NULL always gives the result NULL (and not false) because you cannot tell if an unknown is equal to anything else (even another unknown) as the values are unknown.

For the <> operator, the logic is similar to above with TRUE and FALSE swapped in the output but the result is still NULL (and not TRUE or FALSE) when either input is NULL.

In Oracle, the empty string '' is identical to NULL so nvl(i_send_type,'') is the same as nvl(i_send_type,NULL) which is the same as i_send_type so you end up with the comparisons NULL <> 'cash' OR NULL <> 'card' which results in NULL (and not TRUE or FALSE).

To solve it, you can reverse the operator and use = or IN and then use an ELSE condition:

DECLARE
  i_send_type varchar2(100) := null;
BEGIN
  IF i_send_type IN ('cash', 'card') THEN
    -- Do nothing
    NULL;
  ELSE
    raise_application_error(-20032,'o_userMessage');
  END IF; 
END;
/

or, if you want to get rid of the ELSE then you have to explicitly check for NULL values using IS NULL:

DECLARE
  i_send_type varchar2(100) := null;
BEGIN
  IF i_send_type IS NULL OR i_send_type NOT IN ('cash', 'card') THEN
    raise_application_error(-20032,'o_userMessage');
  END IF; 
END;
/