What is the preferred way to make functions that have Boolean parameters or return types accessible from within queries

Question:
Thanks for all your books over the years, they have made me a much more effective developer.
Following on your mantra of encapsulating and naming business rules within functions (Oracle PL/SQL Best Practices, O'Reilly & Associates), I wonder if you have a standard or preferred way of making functions that have Boolean parameters or return types accessible from within queries.SQL lacks a Boolean data type and that leaves me wondering what to do and how best to do it.
Answer:
Dear Andy: That is an irritation, isn't it? The PL/SQL language features a native Boolean datatype, but BOOLEAN hasn't yet made its way into the Oracle SQL language. So if you want to store true/false flags in columns of a table, you will need to use string or integer values to represent those true/false values. You could, for example, decide that 'Y' and 'N' do the trick. Perhaps it should be 'T' and 'F'? Of course, you could always use the classic integer values 0 and 1?
To my mind, it doesn't really matter which approach you take, as long as it is self-documenting and applied consistently across all tables (and don't forget that NULL is a valid value for a Boolean).
And that is the main challenge. How do you make sure that these values are handled consistently across your application?
The best way to solve this problem is to create a package that performs the translations for you, based on functions that can be used both in PL/SQL programs and SQL statements. Here is a bare-bones implementation of such a package. (Click here to download this package.)
CREATE OR REPLACE PACKAGE boolean_pkg
IS
FUNCTION bool_to_str (boolean_in IN BOOLEAN)
RETURN VARCHAR2;

FUNCTION str_to_bool (string_in IN VARCHAR2)
RETURN BOOLEAN;

FUNCTION true_value
RETURN VARCHAR2;

FUNCTION false_value
RETURN VARCHAR2;
END boolean_pkg;
/

CREATE OR REPLACE PACKAGE BODY boolean_pkg
IS
c_true CONSTANT VARCHAR2 (1) := 'T';
c_false CONSTANT VARCHAR2 (1) := 'F';

FUNCTION bool_to_str (boolean_in IN BOOLEAN)
RETURN VARCHAR2
IS
BEGIN
IF boolean_in
THEN
RETURN c_true;
ELSIF NOT boolean_in
THEN
RETURN c_false;
ELSE
RETURN NULL;
END IF;
END bool_to_str;

FUNCTION str_to_bool (string_in IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
IF string_in = c_true
THEN
RETURN TRUE;
ELSIF string_in = c_false
THEN
RETURN FALSE;
ELSE
RETURN NULL;
END IF;
END str_to_bool;

FUNCTION true_value
RETURN VARCHAR2
IS
BEGIN
RETURN c_true;
END true_value;

FUNCTION false_value
RETURN VARCHAR2
IS
BEGIN
RETURN c_false;
END false_value;
END boolean_pkg;
/
With this package in place, I can write statements like these:
REM Create a demonstration table.

CREATE TABLE my_emp (
name VARCHAR2(100), bonus_eligibility VARCHAR2(1));

REM Find all employees eligible for a raise.

SELECT *
FROM my_emp
WHERE bonus_eligibility = boolean_pkg.true_value;

REM Steven is not eligible.

INSERT INTO my_emp VALUES ('Steven', boolean_pkg.false_value);

REM Convert from table-based string to PL/SQL Boolean variable.

DECLARE
l_is_eligible BOOLEAN;
BEGIN
FOR emp_rec IN (SELECT * FROM my_emp)
LOOP
l_is_eligible :=
boolean_pkg.str_to_bool (
emp_rec.bonus_eligibility);
IF l_is_eligible
THEN
END LOOP;
END;
/
One final thought: as I have recommended frequently in my writings, the best way to manage data in tables is to provide a table API or package layer that sits over the table and controls access to it. With this layer in place, you can also build the Boolean translation into the table API, thereby hiding it entirely from application developers (the users of this table).