The BOOLEAN Datatype in Oracle

The Oracle database does not have the BOOLEAN datatype. No direct way to represent that a column like IS_ACTIVE on table ITEMS is TRUE or FALSE.

The good news is that there are simple workarounds to the situation.

How to simulate a BOOLEAN-valued column in a table?

The most popular way is to create the table with a CHAR(1) column, on which a check constraint is placed.

SQL> create table items
  2  (  item_no number(3),
  3     isactive char(1) check (isactive in ('Y','N'))
  4  );

Table created.

SQL>
SQL> insert into items
  2  values (101, 'Y');

1 row created.

SQL>
SQL> insert into items
  2  values (101, 'X');
insert into items
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0012084) viola


SQL>
SQL> insert into items
  2  values (101, 'N');

1 row created.

A better way is to use a NUMBER(1) column, and use 0/1 to stand for FALSE/TRUE. I call it better, as NUMBER(1) is not language-dependent. Y/N presupposes that the language in use is English. Numbers make no such assumptions.

BOOLEAN datatype exists in PL/SQL!

Interestingly, PL/SQL does have a boolean datatype. A boolean PL/SQL variable can take the values TRUE, FALSE or NULL. Unfortunately, this handy PL/SQL variable cannot be made use of conveniently in SQL. You cannot insert the PL/SQL boolean value into a table column, or fetch from a table column into a PL/SQL boolean variable.

An example of using this datatype in a PL/SQL procedure:

SQL> create or replace procedure chkequal
  2  ( a number
  3  , b number)
  4  as
  5    isequal boolean;
  6  begin
  7    isequal := (a = b);
  8    if (isequal) then
  9      dbms_output.put_line('Input values are equal');
 10    else
 11      dbms_output.put_line('Input values not equal')
 12    end if;
 13  end;
 14  /

Procedure created.

SQL> exec chkequal (3,3);
Input values are equal

PL/SQL procedure successfully completed.

SQL> exec chkequal (3,4);
Input values not equal

PL/SQL procedure successfully completed.

Summary

• Oracle does not have a BOOLEAN datatype.
• A NUMBER(1) column with a check constraint to allow values 0 or 1 is a good alternative for the BOOLEAN datatype. CHAR(1) or VARCHAR(1) are fine too, but they are not language-independent.
• PL/SQL has a BOOLEAN datatype, but you cannot insert this value directly into a table or fetch from a table column directly into a PL/SQL boolean variable.

If you would like to see a new BOOLEAN datatype in SQL too, you can vote for this feature request on the Oracle community database ideas page.