Truly Translating Booleans, Part 2

Boolean Translator Needed!

As I discussed in the March issue of OReview, you will find it very useful to bridge the gap between pseudo-Booleans in the database (a single-character column containing either "Y" or "N" - you hope) and real Booleans in PL/SQL (data structures with only three possible values: TRUE, FALSE, or NULL). What you will want is, in effect, a translator. This translator lets you read from and write to the database reliably from within a PL/SQL program. Reliability in this case means:

1. Programmers can move data between database and code as transparently as possible, while still taking full advantage of the Boolean datatype in their code.

2. Individual developers do not have to know about and remember the conventions established by the DBA to define pseudo-Booleans in the database.

In the context of PL/SQL, this translator is an API or application programmatic interface to the underlying data structures. In this issue of OReview I show you how to construct such an API. First of all, let's take a look at the different elements we will need in this API. I want the ability to:

· Define the value or values that are to be recognized as representing TRUE, FALSE, and NULL in the database. For example, I might want "F" to represent FALSE or I might even want to allow 0, "f," "F," and "N" to all represent FALSE.

· Retrieve the value or values that represent the literal Boolean values. I need functions to return the current value(s) for use in comparison or in passing in to the Oracle database.

· Convert a Boolean value to a string or numeric value. This is the translation step needed to move from PL/SQL code to an Oracle database table.

· Convert a string or numeric value into a Boolean value. This is the translation step needed to move from an Oracle database table to PL/SQL code.

With these programmatic pieces in place, I will be able to move smoothly from native Boolean values in my PL/SQL programs to consistent pseudo-Booleans in my database tables.

Employing Top-Down Design

Granted, this is not the most complicated set of functionality you could think of having to construct. Still, the best way to build this code is to employ top-down design. With this technique, I first develop a list of all of the different programs I need (a.k.a. "functional decomposition"). I don't actually implement the programs; instead, I come up with the program header only: the program type, name, list of parameters, and return information.

I then verify the correctness of these programs and how they would be used by writing code with them. That's right! Before I implement my code, I try out my code. It's like "try before you buy" for PL/SQL developers. How can I do this? By taking advantage of the PL/SQL package construction - just keep on reading to find out more.

Once I have verified the completeness and usefulness of my API, I implement the individual programs. Then I am done, right? Wrong. I still need to test my code, document it, and help others learn how to deploy it effectively. Wow! I am glad this is just an article. Sure, I have done all of those things for PLVbool, the PL/Vision Boolean Translator, but that doesn't mean I necessarily want to give you (or you want to hear) a blow-by-blow account of life with PL/Vision.

On to the top-down design of my Boolean API. First of all, as I hinted above in several ways, I will implement my API inside a package specification. That is, in fact, precisely the point of separating the package specification from its body: You can define the interface without implementing the underlying details.

Listing 1 shows a simplified version of PL/Vision's PLVbool package specification, which is the model for this article. The listing is "simplified" because the full implementation offers a degree of flexibility and additional functionality that I can't explore within the scope of this article (you can visit www.revealnet.com for more information about the comprehensive PLVbool package). This specification, in fact, implements only a portion of the API I described earlier in this article, again for reasons of space (to be specific, it does not support number-Boolean translation and only supports a single translation string for each possible Boolean value).

Take a look at this package specification and its program headers and constants. Think about how you might use them in your code. Then read on as I explain how these packaged elements support the Boolean translator as described in this article.

Getting Ready to Spec

I could dive right into building my package specification. I am, however, better off doing a little preparation. To test the effectiveness of my translator (both from the perspective of my API and from the resulting, executable code), I need a database table to translate from and to. Following is a table I will create to manipulate and view with my PLVbool package:

CREATE TABLE emp_with_pseudobool
(empno INTEGER,
ename VARCHAR2(20),
covered VARCHAR2(5) 
CONSTRAINT ck_bool 
CHECK (covered IN ('TRUE', 'FALSE')));

This "employee table with pseudo-Boolean column" contains an employee number and name. I have, however, added an "covered" column. By defining a constraint on this column, I ensure that the covered column in every row in emp_with_pseudobool can have only one of three values: NULL, TRUE, or FALSE (CHECK constraints implicitly allow nulls).

Now I can proceed with writing PL/SQL programs around this table that will easily verify the usefulness of my API. These same programs then become my test scripts after I write the package body.

The Translation Programs

Defining the programs to translate between pseudo-Booleans and Booleans is a breeze, so I'll do that first. I use a function to convert a Boolean to a string:

FUNCTION stg (bool IN BOOLEAN) RETURN VARCHAR2;

and I immediately "try before I buy" by using it inside a miniprogram. Remember the emp_with_pseudobool table? Following is a procedure that inserts a row into that table:

PROCEDURE addemp 
(empno_in IN emp_with_pseudobool.covered%TYPE,
ename_in IN emp_with_pseudobool.covered%TYPE,
covered_in IN BOOLEAN)
IS
v_pseudobool emp_with_pseudobool.covered%TYPE
:= PLVbool.stg (covered_in);
BEGIN
INSERT INTO emp_with_pseudobool
(empno, ename, covered)
VALUES (empno_in, ename_in, v_pseudobool); 
END;
/

The addemp procedure acts as a layer of code around the INSERT DML statement to the emp_with_pseudobool table. Its three parameters correspond to the three columns of the database table. The first two parameters take advantage of %TYPE to anchor the datatype of the parameter to the table's column. However, I define the third parameter simply as Boolean - the native datatype for this column within PL/SQL.

What's the point of all this code? I can now call addemp inside a PL/SQL program and pass it a Boolean variable. I don't have to translate it into a string myself. I don't have to be concerned with those details. Following is an example of addemp at work:

/* You get coverage if you've been around more than
three months and you are not covered by any 
other programs */
addemp (v_empno, v_ename, 
v_hiredate < ADD_MONTHS (SYSDATE, -3) AND
no_other_coverage (v_empno));

Notice that the third argument passed to addemp is a Boolean expression. If you have not worked much with Booleans, this might look odd to you, but it is perfectly legitimate. Based on these fragments of code utilizing PLVbool.stg, it looks like it will do the job. Now let's take a look at a function to convert a string to a Boolean:

FUNCTION val (stg IN VARCHAR2) RETURN BOOLEAN;

In this case, I pass in a string and get back a Boolean. That sounds right. Let's try it in code. This time I will build a script that reads data from emp_with_pseudobool and then manipulates it in a PL/SQL program:

DECLARE
CURSOR empbool_cur 
IS
SELECT * FROM emp_with_pseudobool;
v_covered BOOLEAN;
BEGIN
FOR emprec IN empbool_cur
LOOP
v_covered := PLVbool.val (emprec.covered);
IF v_covered 
THEN
send_new_policy (emprec.empno);
END IF;
END LOOP;
END;
/

In other words, if the employee is covered, then send them a copy of the new policies (hopefully not another notification of the "less care for more money"). The code I write to accomplish this is clean and readable, taking full advantage of Boolean variables and expressions.

At this point, I have verified my translator programs. I need more, however, than just these two functions. What if a user of PLVbool doesn't want to store TRUE, FALSE, or NULL in his or her pseudo-Boolean column? What if you want to use "Y" and "N" instead to minimize storage needs? To make PLVbool flexible enough for everyone, I need to provide a user-definable dictionary. You should be able to define the mapping of Boolean to string.

Building the Boolean-String Dictionary

The Boolean-String Dictionary is much smaller than Webster's, and each of us can have our own, different version of the dictionary. It has just five entries, which look like the following, unless and until you change them:

The definition of the string "TRUE" is the Boolean TRUE.

The definition of the string "FALSE" is the Boolean FALSE.

The definition of the Boolean TRUE is the string "TRUE."

The definition of the Boolean FALSE is the string "FALSE."

A NULL is a NULL is a NULL.

Of course, we are talking about PL/SQL here, so I had to implement the dictionary in PL/SQL code. (I cover those details later.) For now, we must ensure that I get the headers right for the programs that will let you change the entries to match the way you store pseudo-Boolean values.

First of all, how many programs do I need? I need two procedures: one to set my string definition of the literal TRUE (thereby defining the translation between pseudo-Boolean and Boolean), and one to set my string definition of the literal FALSE:

PROCEDURE sett (val IN VARCHAR2 := c_tval);
PROCEDURE setf (val IN VARCHAR2 := c_fval);

Notice that I have set the default value of these procedures to be the default Boolean values. This is an easy way to reset the current value back to the default. You just call PLVbool.sett or PLVbool.setf without passing any arguments, and PLVbool automatically applies the appropriate default.

It would also be polite to provide functions that return the current definitions (strings) for the Boolean values. That way you can always check to see what is currently in use, and you can even use these functions in your own code. Following are the headers for these two functions:

FUNCTION fval RETURN VARCHAR2;
FUNCTION tval RETURN VARCHAR2;

The following SQL*Plus session shows how to use these procedures to maintain your own Boolean-String Dictionary:

SQL> exec PLVbool.sett ('right-on');
SQL> exec PLVbool.setf ('no-way');

With these calls, my dictionary or translator now has the following five entries:

The definition of the string "right-on" is the Boolean TRUE.

The definition of the string "no-way" is the Boolean FALSE.

The definition of the Boolean TRUE is the string "right-on."

The definition of the Boolean FALSE is the string "no-way."

A NULL is a NULL is a NULL.

Implementing the Package

The PLVbool is not one of the more complicated pieces of code you will ever encounter - and that's the way it should be. If you break up the different areas of functionality finely enough, you can avoid writing complex, lengthy programs. Instead, you will build small, simple nuggets, test them easily and quickly, and then use those building blocks to implement the tougher requirements in your systems.

Listing 2 offers a simplified version of the PL/Vision Boolean Translation package, PLVbool. The first two lines of code declare variables to hold the current TRUE and FALSE string definitions:

v_tval PLV.identifier := c_tval;
v_fval PLV.identifier := c_fval;

PLV.identifier is a subtype defined in the PLV package to encapsulate the rule that a PL/SQL identifier can have up to 30 characters. Following is my definition of this subtype:

plsql_identifier VARCHAR2(30) := 'IRRELEVANT';
SUBTYPE identifier IS plsql_identifier%TYPE;

Notice that the default values for these definitions are the constants that encapsulate or hide the literal string values found in the package specification:

c_tval CONSTANT CHAR(4) := 'TRUE';
c_fval CONSTANT CHAR(5) := 'FALSE';

These constants appear in the package specification, so you can reference these values yourself. For example, you can display these constants as I do below using the PL/Vision substitute for DBMS_OUTPUT.PUT_LINE:

SQL> exec p.l (plvbool.c_tval)
TRUE

With this arrangement, whenever you first use the PLVbool package, the Boolean-String Dictionary is set to its default entries as described earlier. A program can modify the v_tval and v_fval variables with calls to sett and setf, respectively. Following is the implementation of sett:

PROCEDURE sett (val IN VARCHAR2 := c_tval) 
IS
BEGIN
v_tval := NVL (val, c_tval);
END;

In other words, this code says: If the user does not pass an argument, or if the argument is NULL, set the TRUE string back to the default of "TRUE." I like to claim in my trainings that if you leverage all aspects of PL/SQL properly, you do not have to provide comments. This program is an excellent example of that situation.

Let's move on to the actual translator programs, the stg and val functions. PLVbool.stg accepts a Boolean and returns the corresponding string value. I can use a simple IF statement to accomplish this task:

FUNCTION stg (bool IN BOOLEAN) RETURN VARCHAR2
IS
retval PLV.identifier;
BEGIN 
IF bool 
THEN 
retval := v_tval; 
ELSIF NOT bool 
THEN 
retval := v_fval; 
END IF; 
RETURN retval;
END;

The return value variable, retval, is initialized to NULL, so if bool is NULL, I don't have to do anything except RETURN the return value.

The val function, as one might expect, is the mirror of stg. In this case, the IF statement is used to see if the string value you pass to it matches the TRUE or FALSE string definitions. If so, those values are returned; otherwise, the function returns NULL.

FUNCTION val (stg IN VARCHAR2) RETURN BOOLEAN
IS
retval BOOLEAN;
BEGIN
IF stg = v_tval
THEN 
retval := TRUE;
ELSIF stg = v_fval
THEN
retval := FALSE; 
END IF;
RETURN retval;
END; 

And that is it for this simplified version of PLVbool. For a more comprehensive implementation of a Boolean-String translation package, check out PL/Vision Professional on the www.revealnet.com Web page.

Truth and Consistency in PL/SQL

The usefulness of PLVbool is that it let you translate from Boolean to pseudo-Boolean and back again in a consistent way. The code you write based on PLVbool is clean and straightforward, allowing you to take full advantage of Booleans in your PL/SQL code.

As I mentioned in the previous section, PLVbool is not a hard package to conceptualize or to build. The most difficult aspects of a package like PLVbool are (1) recognizing the need and (2) deciding to do something about it. If you are worried about how you will coordinate those pseudo-Booleans in your various database columns with your PL/SQL programs, take action! Worrying doesn't solve your problems, but a package that encapsulates your concerns, rules, and ideas almost always does the trick.

About PL/Vision
PL/Vision is the first third-party library of packages for PL/SQL. Designed and developed by Steven Feuerstein, this software can have a dramatic impact on PL/SQL development productivity and code quality. PL/Vision Lite, a static, free version of PL/Vision is provided with Steven's book Advanced Oracle PL/SQL: Programming with Packages. PL/Vision Professional, the supported and constantly upgraded version of PL/Vision, is available from RevealNet (www.revealnet. com). All packages referenced in Steven's articles that start with the PLV prefix are packages from PL/Vision.

Listing 1 A simplified version of the PLVbool package specification

CREATE OR REPLACE PACKAGE PLVbool /*---------------------------------------------------------------- || Excerpted from PL/Vision Professional PLVbool Package ||---------------------------------------------------------------- || Copyright (C) 1996-97 RevealNet, Inc. || All rights reserved. || || For more information, call RevealNet at 1-800-REVEAL4 || or check out our Web page: www.revealnet.com -----------------------------------------------------------------*/ IS /* Packaged Constants */ c_tval CONSTANT CHAR(4) := 'TRUE'; c_fval CONSTANT CHAR(5) := 'FALSE'; /* Set TRUE, FALSE, NULL Values */ PROCEDURE sett (val IN VARCHAR2 := c_tval); FUNCTION tval RETURN VARCHAR2; PROCEDURE setf (val IN VARCHAR2 := c_fval); FUNCTION fval RETURN VARCHAR2; /* Conversion Functions */ FUNCTION stg (bool IN BOOLEAN) RETURN VARCHAR2; FUNCTION val (stg IN VARCHAR2) RETURN BOOLEAN; END PLVbool; /

Back to text

Listing 2 A simplified version of the PLVbool package body.

CREATE OR REPLACE PACKAGE BODY PLVbool /*---------------------------------------------------------------- || Excerpted from PL/Vision Professional PLVbool Package ||---------------------------------------------------------------- || Copyright (C) 1996-97 RevealNet, Inc. || All rights reserved. || || For more information, call RevealNet at 1-800-REVEAL4 || or check out our Web page: www.revealnet.com -----------------------------------------------------------------*/ IS v_tval PLV.identifier := c_tval; v_fval PLV.identifier := c_fval; PROCEDURE sett (val IN VARCHAR2 := c_tval) IS BEGIN v_tval := NVL (val, c_tval); END; FUNCTION tval RETURN VARCHAR2 IS BEGIN RETURN v_tval; END; PROCEDURE setf (val IN VARCHAR2 := c_fval) IS BEGIN v_fval := NVL (val, c_fval); END; FUNCTION fval RETURN VARCHAR2 IS BEGIN RETURN v_fval; END; FUNCTION stg (bool IN BOOLEAN) RETURN VARCHAR2 IS retval PLV.identifier; BEGIN IF bool THEN retval := v_tval; ELSIF NOT bool THEN retval := v_fval; END IF; RETURN retval; END; FUNCTION val (stg IN VARCHAR2) RETURN BOOLEAN IS retval BOOLEAN; BEGIN IF stg = v_tval THEN retval := TRUE; ELSIF stg = v_fval THEN retval := FALSE; END IF; RETURN retval; END; END PLVbool; /

Back to text

Copyright 1997 Miller Freeman Inc. All Rights Reserved
Redistribution without permission is prohibited

Questions? Comments? email us at webmaster@oreview.com