Boolean Data Type in Oracle 26ai: Native TRUE/FALSE Support Finally Arrives

/ 26ai New Features / By

The End of Y/N and 0/1 Workarounds

For decades, Oracle developers have used NUMBER(1) with 0/1 values or VARCHAR2(1) with Y/N flags to represent Boolean logic in database tables. Every team had its own convention, every application its own mapping layer, and every table its own set of CHECK constraints to enforce valid values. Oracle Database 23ai/26ai finally introduces a native BOOLEAN data type that works across SQL, PL/SQL, table columns, and application bind variables – bringing Oracle in line with the SQL standard and eliminating years of ambiguity.

Why This Matters

The native BOOLEAN type delivers several immediate benefits:

  • Code clarity: WHERE is_active replaces WHERE is_active = 1 or WHERE is_active = 'Y'
  • ORM compatibility: Modern frameworks like Hibernate, Entity Framework, and Django expect Boolean column support – no more custom type mappings
  • Implicit conversions: Oracle handles conversion between BOOLEAN and numeric (TRUE=1, FALSE=0) and character (‘TRUE’/’FALSE’) types automatically
  • SQL/PL/SQL unification: BOOLEAN now works seamlessly in both contexts, including function return values used directly in SQL queries

Example 1: Creating Tables with BOOLEAN Columns

You can now define BOOLEAN columns with defaults, NOT NULL constraints, and use TRUE/FALSE literals directly.

CREATE TABLE customers (
    customer_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR2(200) NOT NULL,
    email         VARCHAR2(200) NOT NULL,
    is_active     BOOLEAN DEFAULT TRUE NOT NULL,
    is_verified   BOOLEAN DEFAULT FALSE,
    accepts_marketing BOOLEAN DEFAULT FALSE NOT NULL
);

INSERT INTO customers (customer_name, email, is_active, is_verified, accepts_marketing)
VALUES ('Ahmed Baraka', 'ahmed@example.com', TRUE, TRUE, FALSE);

INSERT INTO customers (customer_name, email)
VALUES ('Sara Khan', 'sara@example.com');
-- is_active defaults to TRUE, is_verified to FALSE, accepts_marketing to FALSE

Example 2: Querying with Boolean Expressions

BOOLEAN columns can be used directly in WHERE clauses without comparison operators. Oracle also supports IS TRUE, IS FALSE, and IS NOT NULL predicates.

-- Direct Boolean predicate (no = TRUE needed)
SELECT customer_name, email
FROM   customers
WHERE  is_active
AND    NOT accepts_marketing;

-- Using IS TRUE / IS FALSE for explicit readability
SELECT customer_name,
       is_active,
       is_verified,
       CASE WHEN is_verified IS TRUE THEN 'Confirmed' ELSE 'Pending' END AS status
FROM   customers
WHERE  is_verified IS FALSE;

-- Implicit conversion: BOOLEAN to NUMBER in aggregation
SELECT COUNT(*)                     AS total_customers,
       SUM(TO_NUMBER(is_active))     AS active_count,
       SUM(TO_NUMBER(is_verified))   AS verified_count
FROM   customers;

Example 3: BOOLEAN in PL/SQL Functions Used in SQL

Previously, PL/SQL BOOLEAN functions couldn’t be called from SQL. Now they can be used directly in queries, SELECT lists, and WHERE clauses.

CREATE OR REPLACE FUNCTION is_premium_customer(
    p_customer_id IN NUMBER
) RETURN BOOLEAN IS
    v_order_total NUMBER;
BEGIN
    SELECT NVL(SUM(order_amount), 0)
    INTO   v_order_total
    FROM   orders
    WHERE  customer_id = p_customer_id;

    RETURN v_order_total > 10000;
END;
/

-- Call Boolean function directly in SQL
SELECT customer_name,
       is_premium_customer(customer_id) AS is_premium
FROM   customers
WHERE  is_active;

Example 4: Migrating Legacy Flag Columns to BOOLEAN

For existing tables using NUMBER(1) or VARCHAR2 flags, you can migrate to native BOOLEAN using ALTER TABLE with implicit conversion and maintain backward compatibility through views.

-- Original legacy table
-- ALTER TABLE employees ADD is_manager_bool BOOLEAN;
-- UPDATE employees SET is_manager_bool = (is_manager = 1);

-- Simplified approach: add new BOOLEAN column with conversion
ALTER TABLE employees ADD (
    is_manager_new BOOLEAN DEFAULT FALSE NOT NULL
);

UPDATE employees
SET    is_manager_new = CASE WHEN is_manager = 1 THEN TRUE ELSE FALSE END;

-- Backward-compatible view for legacy applications
CREATE OR REPLACE VIEW employees_legacy_v AS
SELECT employee_id,
       employee_name,
       is_manager_new AS is_manager_bool,
       TO_NUMBER(is_manager_new) AS is_manager  -- returns 1 or 0
FROM   employees;

-- Once legacy apps are updated, drop the old column
-- ALTER TABLE employees DROP COLUMN is_manager;
-- ALTER TABLE employees RENAME COLUMN is_manager_new TO is_manager;

Key Takeaways

  • Use BOOLEAN for all new true/false columns – stop using NUMBER(1) or VARCHAR2 flag patterns in Oracle 23ai/26ai.
  • Simplify your WHERE clauses – write WHERE is_active instead of WHERE is_active = 1.
  • PL/SQL Boolean functions now work in SQL – a major unification that eliminates wrapper functions and type conversion hacks.
  • Implicit conversions are built in – BOOLEAN converts to/from NUMBER (1/0) and VARCHAR2 (‘TRUE’/’FALSE’) automatically where needed.
  • Plan your migration – use ALTER TABLE, online redefinition, and backward-compatible views to refactor legacy flag columns incrementally.
Scroll to Top