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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

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 perspective…