If you’ve worked with PL/SQL, you’ve always had access to BOOLEAN. But in Oracle SQL — in actual table columns, queries, and DML — it simply didn’t exist. You had to emulate it with NUMBER(1), CHAR(1) with check constraints, or VARCHAR2 with values like 'Y'/'N'. Each team had its own convention. None were truly boolean.
Oracle 23ai ends this inconsistency. BOOLEAN is now a native SQL data type.
Defining a boolean column:
CREATE TABLE feature_flags (
feature_name VARCHAR2(100),
is_enabled BOOLEAN DEFAULT FALSE NOT NULL,
is_beta BOOLEAN DEFAULT TRUE
);Inserting and querying boolean data:
INSERT INTO feature_flags VALUES ('dark_mode', TRUE, FALSE);
SELECT feature_name
FROM feature_flags
WHERE is_enabled = TRUE;Accepted literal values:
Oracle is flexible about what it accepts as boolean input. All of the following work:
TRUE/FALSE'YES'/'NO''ON'/'OFF''1'/'0'1/0
JSON integration
One of the biggest wins is JSON interoperability. Oracle’s JSON functions now map SQL BOOLEAN columns directly to JSON true/false — no more "is_active": "Y" in your API responses.
Migration note
If you have existing columns using NUMBER(1) or CHAR(1) as boolean proxies, you’ll need to handle conversion carefully. Oracle does not auto-cast these to BOOLEAN on column migration. Plan your data migration scripts accordingly.
The BOOLEAN type in SQL is one of those features that seems minor until you realize how much friction it removes. Cleaner DDL, cleaner application code, cleaner JSON output. A long-overdue improvement.
