Oracle – Understanding NULL in Oracle Function’s IF-Statement

oracle

Given the following Oracle function:

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
  varX NUMBER;
  BEGIN
  select 1 into varX from dual where exists(select 1 from dual where a = 1);
    IF (varX = 1) THEN RETURN 1;
                  ELSE RETURN 0;
    END IF;
  END;

Then, I called it from a Oracle SQL Developer's worksheet:

select nullFunction(3) from dual returns (null).

Next, I modified my function to:

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
  varX NUMBER;
  BEGIN
  select 1 into varX from dual where exists(select 1 from dual where a = 1);
    IF (varX is NULL) THEN RETURN 0;
                      ELSE RETURN 1;
    END IF;
  END;

But, I received the same output when calling it with a value of 3 – null.

Why does it return NULL? I would've expected it to have returned 0 since varX, which I'm assuming to be equal to null, as part of the else.

Best Answer

If you pass any number except the number 1 then your select returns no data and raises a no_data_found exception. Not sure what you are wanting to do but a simple solution would be to add an exception.

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
varX NUMBER;
BEGIN
select 1 into varX from dual where exists(select 1 from dual where a = 1);
 RETURN 1;
EXCEPTION
WHEN no_data_found THEN
RETURN 0;

END;

Alternatively restructure the select from dual so that it cannot return a no_data_found.

Related Question