Oracle 23c - SQL

BOOLEAN Data Type is Finally Supported in Oracle SQL 23c

/ Oracle DB 23c New Features, SQL 23c / By

Prior to Oracle Database 23c, representing logical values like TRUE and FALSE in tables was, well, less than straightforward. We resorted to creative juggling acts, employing numbers, characters, or even entire table structures to mimic binary states. The result? Code shrouded in cryptic workarounds, data prone to misinterpretations, and performance begging for optimization.

With 23c, Oracle finally embraces the ISO SQL standard and grants us a dedicated data type for our little TRUEs and FALSEs. Let’s see how this transforms our data lives:

Pre-23c Juggling Act:

Imagine you want to track employee activity in a table. The “is_active” flag, a crucial piece of information, would traditionally involve:
– Number Magic: “1” for active, “0” for inactive – simple, but prone to confusion. “Is 2 active?” – Oops, wrong interpretation!
– Character Charades: “Y” for yes, “N” for no – understandable, but bulky and not truly aligned with the binary nature of the data.

Using BOOLEAN Data Type

With the BOOLEAN data type, the juggling act stops. We define our “is_active” column as simply:

CREATE TABLE employees (
  id NUMBER,
  is_active BOOLEAN
);


SQL> DESC employees

Name         Null?    Type
____________ ________ __________
ID                    NUMBER
IS_ACTIVE             BOOLEAN

Now, “is_active” holds its rightful place as a TRUE or FALSE value, intuitively representing the employee’s status. No more decoding cryptic numbers or remembering arcane character codes.

Inserting into BOOLEAN Columns

The straightway of inserting into BOOLEAN columns is to use the keywords TRUE or FALSE as follows:

INSERT INTO employees VALUES (1,TRUE);
INSERT INTO employees VALUES (2,FALSE);

Though it is not advisable, we can replace them with different other compatible values like the following:

INSERT INTO employees VALUES (3,'TRUE');
INSERT INTO employees VALUES (4,'FALSE');

INSERT INTO employees VALUES (5,'yes');
INSERT INTO employees VALUES (6,'no');

INSERT INTO employees VALUES (7,'yes');
INSERT INTO employees VALUES (8,'no');

INSERT INTO employees VALUES (9,1);
INSERT INTO employees VALUES (10,0);

This feature allows easier migration to Oracle database from third-party data sources.

Retrieving BOOLEAN Data Type

We can simply retrieve BOOLEAN data type as we would retrieve any other scalar data type. However, in SQL*Plus older than 23.2, we receive error when trying to directly retrieve BOLLEAN data type from tables:

SQL>  SELECT * FROM employees ;

Error starting at line : 1 in command -
 SELECT * FROM employees
Error report -
ORA-17001: Internal error: Unknown or unimplemented accessor type: 252

An easy way to handle this issue is to convert the BOOLEAN data type in the query to character data type as follows:

SQL> SELECT id,
     DECODE(is_active, TRUE, 'Yes', FALSE, 'No') AS is_active
     FROM employees;

   ID IS_ACTIVE
_____ ____________
    1 Yes
    2 No
    3 Yes
    4 No
    5 Yes
    6 No
    7 Yes
    8 No
    9 Yes
   10 No

10 rows selected.

We can apply the conversion functions TO_CHAR, TO_NUMBER on BOOLEAN data types in SQL statements.

Using BOOLEAN data type in WHERE Condition

We can apply conditions on BOOLEAN data types in the WHERE clauses in several formats. Here are some examples:

-- using no operators:
SELECT ID FROM EMPLOYEES WHERE IS_ACTIVE ;
SELECT ID FROM EMPLOYEES WHERE NOT IS_ACTIVE ;

-- using equal operator:
SELECT ID FROM EMPLOYEES WHERE IS_ACTIVE=TRUE;
SELECT ID FROM EMPLOYEES WHERE IS_ACTIVE=FALSE;

-- using implicit conversion:
SELECT ID FROM EMPLOYEES WHERE IS_ACTIVE='TRUE';
SELECT ID FROM EMPLOYEES WHERE IS_ACTIVE='f';
Conclusion:

In conclusion, the BOOLEAN data type in Oracle Database 23c simplifies how we store and work with logical values. It brings several benefits, including clearer code, better data integrity, and potentially faster performance.

Scroll to Top