Overloading Functions that Return Boolean

When you have a function that returns a Boolean, you know that you can't use it in SQL. SQL just doesn't have a Boolean datatype.

Say you have a function like the following:

function ftest (p_param1 in varchar2
               ,p_param2 in varchar2
               )
   return boolean;

It is easy to overload the function, provided you placed it in a package, to return a datatype which is compatible with SQL.
All too often an overloading would look like

if ftest (param1, param2)
then
   return 1;
else
   return 0;
end if;

Can you spot the flaw in this?
The function will return a zero when the original function returns FALSE or NULL.

A better way to overload is to use SYS.DIUTIL to do the job

   return sys.diutil.bool_to_int (ftest (param1, param2));

This function will return a 1, 0 or NULL.

SQL> begin
  2     dbms_output.put_line ('True : '||sys.diutil.bool_to_int (true));
  3     dbms_output.put_line ('False: '||sys.diutil.bool_to_int (false));
  4     dbms_output.put_line ('NULL : '||sys.diutil.bool_to_int (null));
  5  end;
  6  /
True : 1
False: 0
NULL :

And there is also a "reverse" function, to turn an integer into a Boolean

SQL> begin
  2     if sys.diutil.int_to_bool (1)
  3     then
  4        dbms_output.put_line ('True');
  5     end if;
  6     if not sys.diutil.int_to_bool (0)
  7     then
  8        dbms_output.put_line ('False');
  9     end if;
 10     if sys.diutil.int_to_bool (null) is null
 11     then
 12        dbms_output.put_line ('Null');
 13     end if;
 14  end;
 15  /
True
False
Null