Boolean Datatype

Oracle 23c introduced Boolean datatype in SQL, Boolean datatype has been available in PL/SQL for many years already, but now with this Oracle 23c release we can define a Boolean datatype using BOOLEAN or BOOL keywords in SQL language.
 
hr@FREEPDB1> create table emails( address varchar2(20), active boolean );
 
we can assign the values to a Boolean datatype in a number of ways – possible combinations below
 
hr@FREEPDB1> insert into emails values('email_id1',true);
hr@FREEPDB1> insert into emails values('email_id2',false);
hr@FREEPDB1> insert into emails values('email_id3','yes');
hr@FREEPDB1> insert into emails values('email_id4','no');
hr@FREEPDB1> insert into emails values('email_id5','on');
hr@FREEPDB1> insert into emails values('email_id6','off');
hr@FREEPDB1> insert into emails values('email_id7',1);
hr@FREEPDB1> insert into emails values('email_id8',0);
hr@FREEPDB1> insert into emails values('email_id9','y');
hr@FREEPDB1> insert into emails values('email_id10','n');
hr@FREEPDB1> insert into emails values('email_id11','t');
hr@FREEPDB1> insert into emails values('email_id12','f');
hr@FREEPDB1> commit;
hr@FREEPDB1> select  * from emails ;
 
ADDRESS           ACTIVE
-------------------- -----------
email_id1         TRUE
email_id2         FALSE
email_id3         TRUE
email_id4         FALSE
email_id5         TRUE
email_id6         FALSE
email_id7         TRUE
email_id8         FALSE
email_id9         TRUE
email_id10        FALSE
email_id11        TRUE
email_id12        FALSE
 
like all other datatypes, Boolean datatypes can also accept null values unless a NOT NULL constraint has been applied.
 
In prior releases of Oracle database, we will be using Varchar2(1) datatype with values as Y or N (or null) to denote an Boolean value in SQL
 
hr@FREEPDB1> create table emails( address varchar2(20), active varchar2(1) );
hr@FREEPDB1> insert into emails values('email_id1','Y');
hr@FREEPDB1> insert into emails values('email_id2',null);
hr@FREEPDB1> commit;
hr@FREEPDB1> select * from emails ;
 
ADDRESS           A
-------------------- -
email_id1         Y
email_id2
 
the question that comes up frequently is how we convert existing table like above to take the advantage of “Boolean” datatypes.
 
On a non-empty table, it will fail like this
 
hr@FREEPDB1> alter table emails modify active boolean;
alter table emails modify active boolean
                          *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
 
For smaller tables, add a new column with a new datatype we want, and then run an update command to copy the data into this new column, we have a new function called TO_BOOLEAN(..) which can convert char/varchar2/numeric datatypes to Boolean datatypes. For larger tables having zero downtime for migration, then dbms_redefinition is the way to go.
 
hr@FREEPDB1> alter table emails add active_new bool;
 
Table altered.
 
hr@FREEPDB1> update emails
  2  set active_new = to_boolean(active);
 
2 rows updated.
 
hr@FREEPDB1> select * from emails ;
 
ADDRESS           A ACTIVE_NEW
-------------------- - -----------
email_id1         Y TRUE
email_id2
 
hr@FREEPDB1>
 
then finally set the “original” column as unused and rename the new column as “original” column
 
hr@FREEPDB1> alter table emails set unused column active;
 
Table altered.
 
hr@FREEPDB1> alter table emails rename column active_new to active;
 
Table altered.
 
hr@FREEPDB1>
hr@FREEPDB1> select * from emails ;
 
ADDRESS           ACTIVE
-------------------- -----------
email_id1         TRUE
email_id2
 
hr@FREEPDB1> set linesize 71
hr@FREEPDB1> desc emails
Name             Null?    Type
---------------- -------- -------------
ADDRESS                   VARCHAR2(20)
ACTIVE                    BOOLEAN
 
Boolean expressions can be used in SQL anywhere expressions are allowed.
 
hr@FREEPDB1> select * from emails ;
 
ADDRESS                    ACTIVE
-------------------- -----------
email_id1                  TRUE
email_id2
 
hr@FREEPDB1> select * from emails where active ;
 
ADDRESS                    ACTIVE
-------------------- -----------
email_id1                  TRUE
 
hr@FREEPDB1> select * from emails where active or address is not null;
 
ADDRESS                    ACTIVE
-------------------- -----------
email_id1                  TRUE
email_id2
 
can be used in a CASE expressions
 
hr@FREEPDB1> select address,
  2    case when active then 'Active'
  3    else 'Inactive' end as status
  4  from emails ;
 
ADDRESS           STATUS
-------------------- --------
email_id1         Active
email_id2         Inactive
 
we can even index them
 
hr@FREEPDB1> create index email_idx on emails(active);
 
Index created.
 
Check constraints and virtual columns are possible from Boolean datatypes
 
hr@FREEPDB1> alter table emails    
  2  add constraint emails_chk
  3  check( active or address is not null );
 
Table altered.
 
hr@FREEPDB1>
hr@FREEPDB1> alter table emails
  2  add status_flag as (  case when active then 'Y' else 'N' end ) ;
 
Table altered.
 
hr@FREEPDB1> select * from emails;
 
ADDRESS                    ACTIVE S
-------------------- ----------- -
email_id1                  TRUE   Y
email_id2                         N
 
the overloads of other conversion function does not work by default in PL/SQL, for those to work we need to set this parameter plsql_implicit_conversion_bool either at session/system level
 
hr@FREEPDB1> show parameter plsql_implicit_conversion
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_implicit_conversion_bool       boolean     FALSE
hr@FREEPDB1>
hr@FREEPDB1> declare
  2     l_true boolean := true;
  3     l_false boolean := false;
  4     n number;
  5  begin
  6     n := to_number(l_true);
  7     n := to_number(l_false);
  8  end;
  9  /
        n := to_number(l_true);
             *
ERROR at line 6:
ORA-06550: line 6, column 7:
PLS-00306: wrong number or types of arguments in call to 'TO_NUMBER'
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored
ORA-06550: line 7, column 7:
PLS-00306: wrong number or types of arguments in call to 'TO_NUMBER'
ORA-06550: line 7, column 2:
PL/SQL: Statement ignored
 
 
hr@FREEPDB1> alter session set plsql_implicit_conversion_bool = true;
 
Session altered.
 
hr@FREEPDB1> declare
  2     l_true boolean := true;
  3     l_false boolean := false;
  4     n number;
  5  begin
  6     n := to_number(l_true);
  7     n := to_number(l_false);
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
hr@FREEPDB1>