Oracle SQL: Why is my function outputting null?

Your problem is caused by a, sometimes, annoying quirk of Oracle. Namely that if a PL/SQL function is called in SQL and errors then null is returned, rather than anything else.

If I create a very simple table...

create table a ( b number, c varchar2(1) );
insert into a values (1,'Y');

... and two functions. One with no exception handling

create or replace function tmp_ben_fn (PId number) return char is
   l_status varchar2(1);   
begin  

   select c
     into l_status
     from a
    where b = PId;

   return coalesce(l_status, 'N');

end;
/

and one with exception handling.

create or replace function tmp_ben_fn2 (PId number) return char is
   l_status varchar2(1);   
begin  

   select c
     into l_status
     from a
    where b = PId;

   return coalesce(l_status, 'N');

   exception when no_data_found then
      return 'A';

end;
/

Then, we use these two functions to select the data. Remember we only have one row, so we're expecting only Y or N to be returned:

SQL> -- Expected output Y
SQL> select tmp_ben_fn(1) from dual;

TMP_BEN_FN2(1)
-------------------------------------------------

Y

SQL> -- Expected output, Error no_data_found
SQL> -- as row 2 does not exist.
SQL> select tmp_ben_fn(2) from dual;

TMP_BEN_FN(2)
-------------------------------------------------



SQL> -- Expected output, the same as tmp_ben_fn
SQL> select tmp_ben_fn2(2) from dual;

TMP_BEN_FN2(2)
-------------------------------------------------

A

SQL>

As you can see in the function with no error handling, when a no_data_found exception occurs a null is returned. When we capture the exception, we get the expected A instead.

I suspect that the member_id you're trying to select does not exist.

As a general rule: Always be aware when an exception might occur, select into... being a particularly worrisome example with the opportunity to select both too many and no rows. If your table is unique on member_id then you should change your function as follows:

CREATE OR REPLACE FUNCTION get_status_by_member_id (p_member_id NUMBER) 
RETURN CHAR IS
  v_status CHAR(1);
BEGIN
  select status
  into v_status
  from members
  where member_id = p_member_id;

  return v_status;

-- If the member_id does not exist
-- return N
exception when no_data_found then
   return 'N';
END get_status_by_member_id;

Try adding an exception handler to your code, as in:

CREATE OR REPLACE FUNCTION get_status_by_member_id 
  (p_member_id NUMBER) RETURN CHAR 
as 
  v_status CHAR(1); 
begin 
  BEGIN
    select status 
      into v_status 
      from members 
      where member_id = p_member_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_status := NULL;
    WHEN TOO_MANY_ROWS THEN
      v_status := NULL;
  END;

  if v_status is null then 
    return 'N'; 
  else 
    return v_status; 
  end if; 
end get_status_by_member_id; 

This will handle the cases where there's no data which matches p_member_id, or where there are multiple rows which match p_member_id. This may or may not correct the issues you're seeing. It would be helpful if you would edit your post and add in the code you're using which calls get_status_by_member_id.

Share and enjoy.