8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Boolean Data Type in Oracle Database 23c
Oracle database 23c introduced the boolean data type in SQL. Boolean data types have been available in PL/SQL for many years already.
Boolean Table Columns
We can define a boolean data type using the BOOLEAN or BOOL keywords.
drop table if exists boolean_test purge; create table boolean_test ( id number generated always as identity, active boolean, archived bool );
We can assign a value to a Boolean type in a number of ways. The following all set the active flag to true and the archived flag to false.
insert into boolean_test (active, archived) values (true, false);
insert into boolean_test (active, archived) values (TRUE, FALSE);
insert into boolean_test (active, archived) values ('true', 'false');
insert into boolean_test (active, archived) values ('TRUE', 'FALSE');
insert into boolean_test (active, archived) values ('yes', 'no');
insert into boolean_test (active, archived) values ('YES', 'NO');
insert into boolean_test (active, archived) values ('on', 'off');
insert into boolean_test (active, archived) values ('ON', 'OFF');
insert into boolean_test (active, archived) values (1, 0);
insert into boolean_test (active, archived) values ('1', '0');
insert into boolean_test (active, archived) values ('t', 'f');
insert into boolean_test (active, archived) values ('T', 'F');
insert into boolean_test (active, archived) values ('y', 'n');
insert into boolean_test (active, archived) values ('Y', 'N');
commit;
We can see the result of this below.
select * from boolean_test;
ID ACTIVE ARCHIVED
---------- ----------- -----------
1 TRUE FALSE
2 TRUE FALSE
3 TRUE FALSE
4 TRUE FALSE
5 TRUE FALSE
6 TRUE FALSE
7 TRUE FALSE
8 TRUE FALSE
9 TRUE FALSE
10 TRUE FALSE
11 TRUE FALSE
12 TRUE FALSE
13 TRUE FALSE
14 TRUE FALSE
14 rows selected.
SQL>
Old versions of SQL*Plus and all versions of SQLcl up to and including SQLcl 23.1 will display true and false as 1 and 0. This is because of the drivers being used, which don't currently understand true/false. Later releases will display Booleans as true/false.
select * from boolean_test;
ID ACTIVE ARCHIVED
---------- ---------- ----------
1 1 0
2 1 0
3 1 0
4 1 0
5 1 0
6 1 0
7 1 0
8 1 0
9 1 0
10 1 0
11 1 0
ID ACTIVE ARCHIVED
---------- ---------- ----------
12 1 0
13 1 0
14 1 0
14 rows selected.
SQL>
A Boolean column can also accept NULL values, unless a NOT NULL constraint has been applied.
insert into boolean_test (active, archived) values (null, null);
commit;
select *
from boolean_test
where active is null;
ID ACTIVE ARCHIVED
---------- ----------- -----------
15
SQL>
delete from boolean_test
where active is null;
commit;
Boolean columns can have NOT NULL and CHECK constraints applied to them. They can be part of UNIQUE, PRIMARY KEY or FOREIGN KEY constraints.
SQL Support
Boolean expressions can be used in SQL anywhere expressions are allowed. Here are some examples, but you can find more details about comparisons, assignments, conditions and operators in the documentation.
select count(*)
from boolean_test
where active;
COUNT(*)
----------
14
SQL>
select count(*)
from boolean_test
where active is true;
COUNT(*)
----------
14
SQL>
select count(*)
from boolean_test
where active and not archived;
COUNT(*)
----------
14
SQL>
select count(*)
from boolean_test
where active or archived;
COUNT(*)
----------
14
SQL>
select count(*)
from boolean_test
where active is null;
COUNT(*)
----------
0
SQL>
select count(*)
from boolean_test
where active > archived;
COUNT(*)
----------
14
SQL>
The TO_BOOLEAN function has been added, along with overloads of the TO_NUMBER, TO_CHAR and TO_NCHAR functions to support Booleans.
select to_boolean('true'),
to_boolean('false'),
to_number(active),
to_number(archived),
to_char(active),
to_char(archived),
to_nchar(active),
to_nchar(archived)
from boolean_test
where id = 1;
TO_BOOLEAN( TO_BOOLEAN( TO_NUMBER(ACTIVE) TO_NUMBER(ARCHIVED) TO_CH TO_CH TO_NC TO_NC
----------- ----------- ----------------- ------------------- ----- ----- ----- -----
TRUE FALSE 1 0 TRUE FALSE TRUE FALSE
SQL>
PL/SQL Support
PL/SQL has had support for Boolean types for many years. Now they can be persisted in the database like many other data types.
declare l_active boolean := true; l_archived boolean := false; begin insert into boolean_test (active, archived) values (l_active, l_archived); commit; end; / PL/SQL procedure successfully completed. SQL>
The TO_BOOLEAN function mentioned previously is available from PL/SQL by default.
declare
l_true boolean;
l_false boolean;
begin
-- Work
l_true := to_boolean('true');
l_false := to_boolean('false');
end;
/
In Oracle 23.2 the overloads of the other conversion functions do not work by default in PL/SQL. For these to work the PLSQL_IMPLICIT_CONVERSION_BOOL initialisation parameter has to be set to true at system or session level. In Oracle 23.3 you no longer need to set the PLSQL_IMPLICIT_CONVERSION_BOOL initialisation parameter for these overloads to work.
-- Oracle 23.2 only alter session set plsql_implicit_conversion_bool=true; declare l_true boolean := true; l_false boolean := false; l_number number; begin l_number := to_number(l_true); l_number := to_number(l_false); end; / declare l_true boolean; l_false boolean; l_string varchar2(5); begin l_string := to_char(l_true); l_string := to_char(l_false); end; / declare l_true boolean; l_false boolean; l_string nvarchar2(5); begin l_string := to_nchar(l_true); l_string := to_nchar(l_false); end; /
For more information see:
Hope this helps. Regards Tim...