04 March 2009

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)
return 1;
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

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 /

1 comment: