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,
