Skip to main content

Go "native" with Booleans in PL/SQL

This post was inspired by a Twitter conversation doing the Twitter version of shaking heads over the kind of code developers write with Booleans.
Keep it simple and native and intuitive: Booleans are TRUE or FALSE (ok, also maybe NULL). So you don't have to write code like "IF my_boolean = TRUE".
Suppose that I needed to implement a function IS_A_FACT so that I can compile and run the following block:

BEGIN
   IF is_a_fact ('Steven says: The sun revolves around the earth.')
   THEN
      DBMS_OUTPUT.put_line ('Fact!');
   ELSE
      DBMS_OUTPUT.put_line ('Opinion!');
   END IF;
END;

Here are four different ways of getting the job done. They all work. Which would you prefer?

1. Lay it all out there, Steven

FUNCTION is_a_fact (statement_in IN VARCHAR2)
   RETURN BOOLEAN AUTHID DEFINER
IS
   l_is_a_fact   BOOLEAN;
BEGIN
   IF statement_in LIKE 'Steven says:%'
   THEN
      l_is_a_fact := TRUE;
   ELSE
      l_is_a_fact := FALSE;
   END IF;

   IF (l_is_a_fact = TRUE)
   THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;

2. OK, a bit more concise

FUNCTION is_a_fact (statement_in IN VARCHAR2)
   RETURN BOOLEAN AUTHID DEFINER
IS
   l_is_a_fact   BOOLEAN;
BEGIN
   IF statement_in LIKE 'Steven says:%'
   THEN
      l_is_a_fact := TRUE;
   ELSE
      l_is_a_fact := FALSE;
   END IF;

   RETURN CASE l_is_a_fact WHEN TRUE THEN TRUE ELSE FALSE END;
END;

3. I'm melting, melting...

FUNCTION is_a_fact (statement_in IN VARCHAR2)
   RETURN BOOLEAN AUTHID DEFINER
IS
   l_is_a_fact   BOOLEAN;
BEGIN
   IF statement_in LIKE 'Steven says:%'
   THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;

4. Seriously, this is all I need to write?

FUNCTION is_a_fact (statement_in IN VARCHAR2)
   RETURN BOOLEAN AUTHID DEFINER
IS
BEGIN
   RETURN statement_in LIKE 'Steven says:%';
END;

Yes, quite seriously: that is all you need to write. It's wonderful that PL/SQL supports the Boolean datatype (named after George Boole, "father" of symbolic logic) - and not so wonderful that Oracle SQL still does not. So use it in the most natural, readable way possible!

Oh and by the way, the block of code at the beginning of the post could be changed to a single call to DBMS_OUTPUT.PUT_LINE as follows:

BEGIN
   DBMS_OUTPUT.put_line (
      CASE
         WHEN is_a_fact ('Steven says: The sun revolves around the earth.')
         THEN
            'Fact!'
         ELSE
            'Opinion!'
      END);
END;

Hurray, Booleans and CASE!

Comments

  1. Stephen,
    You're truly mastering writing good examples.
    Great example of refactoring towards simple and functional code.

    ReplyDelete
    Replies
    1. You are very kind, Jacek. I will send you the check for payment for this praise next Tuesday. :-)

      Delete
  2. I think a lot of developers assume that PL/SQL doesn't have a Boolean data type because SQL doesn't. I see a lot of PL/SQL code where a char(1) is declared and the value is set to 'Y' or 'N' instead of using a Boolean (obviously this may be necessary in some situations such as if it's needed with a SQL statement).

    ReplyDelete
    Replies
    1. I do that constantly so I can test the function using SQL.

      Delete
  3. That's interesting, never thought of that. As you say, we still do it because the data is going into or coming out of a SQL pseudo-Boolean column.

    Well, I hope my blog post and similar quizzes at the Oracle Dev Gym (devgym.oracle.com) can help inform developers on this topic!

    Do you have any other thoughts of how we might move people away from this poor practice?

    ReplyDelete
  4. I myself use the C type booleans (a numeric value; 0=false, 1=true) often because of 2 reasons

    - There is no boolean data type in SQL. Sometimes I need to store them in a Table however, and reference them in PL/SQL. Also more often than not I have functions returning booleans which afer a while need to be invoked from SQL for whatever reason. Well, either I write a SQL wrapper for the boolean function, or I simply write it as numeric in the first place if I can estimate it'll be used in SQL soon.
    - At least in JDBC up until 12.1 boolean isn't supported (If memory serves it'll be supported from 12.1 onward, but I am not 100% certain here).
    As for now until extended support for 11.2 ends at least I am stuck with 11.2.
    Suppose I want to pass a boolean from java to PL/SQL. I either convert the java boolean to a numeric value in java, pass it to a stored procedure and in PL/SQL convert it back to boolean or I simply use the C type booleans. Btw. if I need to pass the boolean further to SQL I need to convert it back to C Type booleans *again* (and as for now there is no way around that even with 12.2). Or I skip the whole boolean => numeric conversion tragedy alltogether and use my own implementation of the boolean datatype which can be used from java via PL/SQL down to SQL.

    Bottom Line: unless I can't pass a boolean datatype from the client language (e.g. java) to the server (PL/SQL) to the SQL Engine the boolean datatype in PL/SQL is nice, but somehow (sorry to be blunt) often useless. More often than not I cannot use it even though I want to.
    And until you don't have to do back and forth conversions from boolean to something else because one part of the database supports them (PL/SQL) while the other doesn't (SQL) you'll see more or less elegant custom implementations of the boolean datatype (maybe they reflect the language they started learning programming - yes, I started with C ;-)).

    cheers

    ReplyDelete
  5. Thanks, Christian. Your points are very convincing. I wish we did offer a smoother path all along the stack.

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel