Boolean Data Type – Oracle database 23c New Feature


SQL Boolean data type is introduced in Oracle database 23c. Boolean data type has the truth values TRUE and FALSE. Keywords BOOLEAN or BOOL with/without default value can be specified as data type.

SQL keywords TRUE, FALSE and NULL as values can be used.

TO_BOOLEAN Function

Used to convert numeric or character values to Boolean values. Numbers “0” translates to FALSE and “1” translates to TRUE.

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> SELECT TO_BOOLEAN(true) union all
          SELECT TO_BOOLEAN(false) union all
          SELECT TO_BOOLEAN(0) union all
          SELECT TO_BOOLEAN(1) union all
          SELECT TO_BOOLEAN('true') union all
          SELECT TO_BOOLEAN('false') union all
          SELECT TO_BOOLEAN('yes') union all
          SELECT TO_BOOLEAN('no');
TO_BOOLEAN(
-----------
TRUE
FALSE
FALSE
TRUE
TRUE
FALSE
TRUE
FALSE
8 rows selected.

Boolean Data Type Example

Boolean columns can have constraints like CHECK, NOT NULL, PRIMARY KEY, UNIQUE KEY, FORIEGN KEY. Example of creating table with Boolean data type and insert records.

SQL> CREATE TABLE emp (
emp_id NUMBER,
emp_name VARCHAR2(30),
emp_status BOOLEAN default TRUE,
emp_benefits BOOL);

Table created.

SQL> INSERT INTO emp (emp_id, emp_name, emp_status, emp_benefits) VALUES (1, 'SMITH', TRUE, TRUE);
INSERT INTO emp (emp_id, emp_name, emp_status, emp_benefits) VALUES (2, 'JACK', TRUE, false);
INSERT INTO emp (emp_id, emp_name, emp_status, emp_benefits) VALUES (3, 'LARRY', false, false);
INSERT INTO emp (emp_id, emp_name, emp_status, emp_benefits) VALUES (4, 'JOHN', false, TRUE);
INSERT INTO emp (emp_id, emp_name, emp_status, emp_benefits) VALUES (4, 'KARTHIK', false, TRUE);
commit;

1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
SQL>

Comparison parameters like =, !=, < >, <, <=, >, >=, GREATEST, LEAST, [NOT] IN can be used. Here are some samples.

NOT, AND, and OR operators can be used in SQL conditions on Boolean columns.

Note : In SQL developer older versions Boolean data type shows as “0” and “1”.

Leave a Comment