Exploring the New BOOLEAN Data Type in Oracle Database 23ai

Oracle Database 23ai has introduced many features, and one of the nice additional in SQL is the support for the BOOLEAN data type. The BOOLEN data types are available for many years in Pl/SQL and now it is supported as native data type. This will help developers simplify application logic. Earlier developers often had to rely on CHAR(1) or NUMBER(1) fields to simulate boolean logic in SQL

The BOOLEAN data type represents logical values such as TRUE, FALSE, and NULL.

Advantages of native BOOLEAN support in SQL:

• Improved readability: No more cryptic 'Y', 'N', or 1, 0 values. Developers can use TRUE and FALSE make code more intuitive.
• Better integration: Direct support in SQL helps use BOOLEAN values easier to use in views, constraints, triggers, and queries.
• Less error-prone: removes confusion caused by using characters or numbers to represent boolean values
• Modernization: Aligns Oracle SQL more closely with other RDBMSs such as PostgreSQL and MySQL.

How to use BOOLEAN in Oracle 23ai

1. Creating a Table with a BOOLEAN Column

SQL> CREATE TABLE emp (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
is_active BOOLEAN);

2. Inserting BOOLEAN Values

INSERT INTO emp (employee_id, name, is_active)
VALUES (101, 'Samantha', TRUE);

INSERT INTO emp (employee_id, name, is_active)
VALUES (102, 'Rex', FALSE);

3. Querying BOOLEAN Values

SELECT name FROM emp
WHERE is_active = TRUE;

Or

SELECT name FROM emp WHERE is_active;

4. Using BOOLEAN in CASE Statements

SELECT name,
CASE
WHEN is_active THEN 'Active'
WHEN NOT is_active THEN 'Inactive'
ELSE 'Unknown'
END AS status FROM emp;

Key Considerations: -

• Note that BOOLEAN values can still be NULL, but in conditions where NULL might affect the logic.
• Not all Oracle tools and connectors fully support BOOLEAN yet and it’s user responsibility to check compatibility with client libraries
• Existing old code using CHAR(1) or NUMBER(1) won’t auto-convert for BOOLEAN, user must migrate manually

The BOOLEAN data type in Oracle Database 23ai is a game changer for developers and DBAs. It improves data modeling, simplifies SQL logic, helps developer experience like other RDBMS platforms and consider adopting BOOLEAN fields wherever required.

Thanks & Regards,

https://oracleracexpert.com