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; /
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.
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...