PL/SQL 101: Why can't I display a Boolean value with DBMS_OUTPUT.PUT_LINE?

DBMS_OUTPUT. PUT_LINE is the built-in procedure that PL/SQL developers use to display output on the screen.

Let's watch it do it's thing on LiveSQL:

So I displayed a string, a date, a date converted to a string, and a number. Cool. 

Now let's display a Boolean value (TRUE, FALSE or NULL):

Ouch! It did not like a Boolean value, that's for sure. But why not? To figure that out, we need to take a look at the specification of the DBMS_OUTPUT package.

That's easy in SQL Developer: just right-click and choose Popup Describe. 

Searching for "procedure put_line", I see:

create or replace package dbms_output authid definer as
...
  procedure put_line(a varchar2);

Huh. That's weird. It only accepts a string. So how could it display a number and a date with no problem, but then choke on a Boolean?

It all has to do with implicit conversions. Generally, Oracle Database in both SQL and PL/SQL will automatically and implicitly convert a value from one data type to another "when such a conversion makes sense." This matrix shows the implicit conversions that are supported, but I can summarize for you in terms of this post:

1. Dates, timestamps and numbers can be implicitly converted to strings.
2. Booleans cannot be converted to strings. 

In fact, the BOOLEAN datatype doesn't even appear in the matrix. That is probably in part because BOOLEAN is a PL/SQL-specific datatype; it's not supported at all in Oracle SQL.

Does that mean that it will never be possible for DBMS_OUTPUT.PUT_LINE to display a Boolean? Not at all. For example, the PL/SQL development team could add an overloading for PUT_LINE in the DBMS_OUTPUT package:

create or replace package dbms_output authid definer as
...
  procedure put_line(a varchar2);
  procedure put_line(a boolean);

And then they just have to figure out what to display. That's the "funny" thing about the Boolean values of TRUE and FALSE. They are in English. If you are using a different language, should the text displayed for a Boolean value change to the words for TRUE and FALSE in that language?

So many questions, so little time - but no matter how many questions there are, adding an overloading to this built-in package is well out of our control.

So what's a developer to do?

Well, first of all, please do not do this, over and over again throughout your code:

BEGIN
   IF my_boolean
   THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
   ELSIF NOT my_boolean
   THEN
      DBMS_OUTPUT.PUT_LINE ('FALSE');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('NULL');
   END IF;
END;

Instead, why not install a "Boolean Manager" in your environment that allows you to easily display Boolean values (and convert them from/to strings)?

A final note: we recommend that you avoid implicit conversions whenever possible, and tell Oracle Database exactly what you want, and how you want it done. A simple example demonstrating the benefit of being explicit has to do with displaying date values.

When I ask to display a date, as in:

BEGIN
   DBMS_OUTPUT.put_line (DATE '2016-10-31');
END;
/

31-OCT-16

Oracle Database uses the default NLS date format setting for my session to do the implicit conversion. The default format is DD-MON-YY, which honestly very few humans find helpful. But notice in particular that the time component is ignored.

Now let's try it again after changing the session date format:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
/

BEGIN
   DBMS_OUTPUT.put_line (DATE '2016-10-31');
END;
/

2016-10-31 00:00:00

So rather than assuming the session date format is what you hope or desire, make it explicit when you  ask to display a date, as in:

BEGIN
   DBMS_OUTPUT.put_line (TO_CHAR (DATE '2016-10-31'
      , 'YYYY-MM-DD HH24:MI:SS'));
END;
/

31-OCT-16