Oracle AI Database 26ai: Usage of BOOLEAN Data Type and New SQL Common Sense

Oracle AI Database 26ai, the latest Long Term Support (LTS) release, was announced in October 2025.

In this version, the Oracle BOOLEAN data type is natively supported in SQL (inheriting and enhancing features from the former 23ai). Flag management, which was previously substituted with NUMBER(1) or CHAR(1), becomes dramatically simpler with the introduction of the Boolean type (logical type).

This article explains its mechanism and how to use it in the 26ai environment for beginners.

Conclusion/Shortest Steps
1. Background: 26ai and BOOLEAN Type
2. Procedure/Implementation (From User Creation to Execution)
3. Execution Examples: Smart Search and Update
1. Improvement of SELECT (Search)
2. UPDATE (Update) and Logical Operations
4. Troubleshooting
5. Operations, Monitoring, and Security Notes
1. Notes on Version Confirmation
2. Client Compatibility
6. FAQ (Frequently Asked Questions)
7. Summary

Conclusion/Shortest Steps

Here is the shortest list to use the BOOLEAN type in Oracle AI Database 26ai.

Environment Check: Confirm the version is 26ai (internal version 23.26 or higher) using v$version etc.
Table Creation: Specify BOOLEAN in the column definition (e.g., is_active BOOLEAN).
Data Registration: Directly INSERT values as TRUE or FALSE.
Search: Filter with WHERE col IS TRUE or the column itself.

1. Background: 26ai and BOOLEAN Type

What is Oracle AI Database 26ai?

It is the latest database that comprehensively enhances AI capabilities, appearing as a replacement for the conventional “Oracle Database 23ai”.

Name: Oracle AI Database 26ai
Positioning: New Long Term Support (LTS) version
Features: Fully inherits 23ai features while including enhancements to AI Vector Search and support for Post-Quantum Cryptography (PQC).

What is BOOLEAN Type?

It is a data type that holds only one of two states: “True” or “False“.

Image for Beginners

ON = TRUE (Correct, Valid, Yes)
OFF = FALSE (Incorrect, Invalid, No)

Conversion processes to “0/1” or “Y/N”, which were conventionally required, are no longer necessary, enabling direct mapping with boolean types in applications (Java/Python, etc.).

When to Use? (Use Cases)

The BOOLEAN type is optimal for “binary choice” state management like the following:

Status Management: Member valid/invalid, article published/private, delete flag (logical delete)
Settings Switches: Email notification ON/OFF, dark mode usage
Check Results: Verified identity (is_verified), payment made (is_paid)
JSON Integration: When saving true/false from JSON data received from an application as is

2. Procedure/Implementation (From User Creation to Execution)

This explains the procedure to operate on an actual machine assuming Oracle AI Database 26ai on Oracle Linux.

Prerequisites

OS: Oracle Linux 8/9, etc.
DB Version: Oracle AI Database 26ai (Enterprise / Free)
Connection Tool: SQL*Plus (Newer version, 19c or later recommended) or SQLcl

※Note: Regarding Oracle Database Free Environment

The procedures in this article assume operation on Oracle Database 26ai Free (Developer Release), but the Free version has resource limits (CPU/Memory/Data volume), and some behaviors or performance characteristics may differ from the Enterprise Edition. For application to commercial environments, verification with a licensed version is recommended.

Step 1: Creation of Verification User and Granting Privileges

-- 1. Connect as SYS user
-- Specify the PDB service name for 26ai (formerly 23ai series)
CONN sys/password@localhost:1521/freepdb1 AS SYSDBA

-- 2. Create user
CREATE USER demo_bool IDENTIFIED BY "Welcome_26ai_Test#" QUOTA UNLIMITED ON users;

-- 3. Grant necessary privileges
-- Grant RESOURCE role including sequence privileges necessary for IDENTITY column creation
GRANT CONNECT, RESOURCE TO demo_bool;

-- 4. Connect with the created user
CONN demo_bool/"Welcome_26ai_Test#"@localhost:1521/freepdb1

Explanation: Meaning of Commands

CREATE USER ... QUOTA UNLIMITED ON users: Creates a user and grants permission to write data without limit to the storage area (tablespace) called users.
GRANT CONNECT, RESOURCE:
- CONNECT: Basic privilege to connect to the database.
- RESOURCE: A set of privileges (role) necessary for development, such as creating tables and sequences.

Step 2: Creation of Table with BOOLEAN Type

Specify BOOLEAN in the column definition.

CREATE TABLE user_settings (
    user_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_name   VARCHAR2(50),
    is_premium  BOOLEAN,          -- Whether premium member
    has_notify  BOOLEAN DEFAULT TRUE -- Whether to receive notifications (Default TRUE)
);

Explanation: Meaning of Commands

GENERATED BY DEFAULT AS IDENTITY: A setting to automatically number user_id (member number, etc.). Even if you do not specify a number when inserting data, sequential numbers like 1, 2, 3… are automatically assigned.
BOOLEAN DEFAULT TRUE: If data is registered without specifying a value, it automatically sets TRUE (ON). This is convenient for operations like “enable it for now if there is an omission in settings”.

SQL> CREATE USER demo_bool IDENTIFIED BY "Welcome_26ai_Test#" QUOTA UNLIMITED ON users;

User created.

SQL> GRANT CONNECT, RESOURCE TO demo_bool;

Grant succeeded.

SQL> CONN demo_bool/"Welcome_26ai_Test#"@localhost:1521/freepdb1
Connected.
SQL> SHOW USER
USER is "DEMO_BOOL"
SQL> SHOW CON_NAME

CON_NAME
------------------------------
FREEPDB1
SQL> CREATE TABLE user_settings (
  2      user_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  3      user_name   VARCHAR2(50),
  4      is_premium  BOOLEAN,
  5      has_notify  BOOLEAN DEFAULT TRUE
  6  );

Table created.

SQL> DESC user_settings
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID                                   NOT NULL NUMBER
 USER_NAME                                          VARCHAR2(50)
 IS_PREMIUM                                         BOOLEAN  ★
 HAS_NOTIFY                                         BOOLEAN  ★

SQL>

Step 3: INSERT (Registration) of Data

Use TRUE / FALSE keywords as they are.

-- 1. Basic registration
INSERT INTO user_settings (user_name, is_premium, has_notify) 
VALUES ('Tanaka', TRUE, FALSE);

-- 2. Use of default value (has_notify becomes TRUE)
INSERT INTO user_settings (user_name, is_premium) 
VALUES ('Suzuki', FALSE);

-- 3. Commit
COMMIT;

Explanation: Meaning of Commands

VALUES (..., TRUE, FALSE): For BOOLEAN type columns, describe TRUE or FALSE as they are without adding quotes (single quotation marks ') representing strings. This is the same sensation as writing numerical values 1 or 2 as they are.

SQL> INSERT INTO user_settings (user_name, is_premium, has_notify)
  2  VALUES ('Tanaka', TRUE, FALSE);

1 row created.

SQL> INSERT INTO user_settings (user_name, is_premium)
  2  VALUES ('Suzuki', FALSE);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM user_settings;

   USER_ID USER_NAME  IS_PREMIUM  HAS_NOTIFY
---------- ---------- ----------- -----------
         1 Tanaka     TRUE        FALSE
         2 Suzuki     FALSE       TRUE

SQL>

3. Execution Examples: Smart Search and Update

SQL statements become closer to natural language (English), improving readability.

Improvement of SELECT (Search)

-- How to read: Select if is_premium is TRUE
SELECT * FROM user_settings WHERE is_premium IS TRUE;

-- Or, simply writing the column name is also OK (common feature in 26ai/23ai)
SELECT * FROM user_settings WHERE is_premium;

Explanation: Meaning of Commands

WHERE is_premium IS TRUE: Extracts data that “is a premium member (value is true)”.
WHERE is_premium: This is the abbreviated form of the above. It means “case where is_premium holds true”, enabling very intuitive description. Conversely, “not a premium member” can be written as WHERE NOT is_premium.

SQL> SELECT * FROM user_settings WHERE is_premium IS TRUE;

   USER_ID USER_NAME  IS_PREMIUM  HAS_NOTIFY
---------- ---------- ----------- -----------
         1 Tanaka     TRUE        FALSE

SQL> SELECT * FROM user_settings WHERE is_premium;

   USER_ID USER_NAME  IS_PREMIUM  HAS_NOTIFY
---------- ---------- ----------- -----------
         1 Tanaka     TRUE        FALSE

SQL>

UPDATE (Update) and Logical Operations

You can invert values with the NOT operator.

-- Invert Suzuki's notification setting (TRUE->FALSE / FALSE->TRUE)
UPDATE user_settings 
SET has_notify = NOT has_notify 
WHERE user_name = 'Suzuki';

-- Confirm results
SELECT user_name, has_notify FROM user_settings;

Execution Result Image:

SQL> UPDATE user_settings
  2  SET has_notify = NOT has_notify
  3  WHERE user_name = 'Suzuki';

1 row updated.

SQL> SELECT user_name, has_notify FROM user_settings;

USER_NAME  HAS_NOTIFY
---------- -----------
Tanaka     FALSE
Suzuki     FALSE  ★

SQL>

Explanation: Meaning of Commands

SET has_notify = NOT has_notify: Rewrites to FALSE if the current value is TRUE, and to TRUE if it is FALSE. This is a convenient way to write operations like “flipping a switch” in a single line of SQL.

(Note: Depending on SQLPlus settings, it may be displayed as 1/0 instead of TRUE/FALSE)

4. Troubleshooting

Error Code	Error Message (Example)	Cause	Solution
ORA-00902	Invalid datatype	DB version is old	Check the version with `SELECT banner FROM v$version`. It needs to be 26ai or 23.x series.
ORA-00932	Inconsistent datatypes…	Mixed strings (‘Y’) or numbers (1)	Please use `TRUE` / `FALSE` explicitly.

5. Operations, Monitoring, and Security Notes

Notes on Version Confirmation

Oracle AI Database 26ai may return an internal version number (Release Update) such as 23.26.x.

SQL for confirmation:

SELECT banner_full FROM v$version;

SQL> SELECT banner_full FROM v$version;

BANNER_FULL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0


SQL>

Confirm that the output includes Oracle AI Database 26ai or Release 23.0.0.0.0 – Production Version 23.26…, etc.

Client Compatibility

Even when connecting to a 26ai server, if the client (JDBC driver or OCI client) is old, it may not correctly interpret the BOOLEAN type and result in an error. Be sure to use Oracle Client 23ai/26ai compatible versions.

6. FAQ (Frequently Asked Questions)

Q1: Is there a difference in BOOLEAN type behavior between 23ai and 26ai?
- A1: There is no difference in basic SQL specifications. Since 26ai was brand-integrated as the successor to 23ai, the features are compatible.
Q2: Can I convert an existing NUMBER(1) column to BOOLEAN?
- A2: Direct ALTER TABLE often results in data conversion errors, so it is recommended to create a new column and migrate data (convert using CASE WHEN flag=1 THEN TRUE ELSE FALSE END).
Q3: Can indexes be created?
- A3: Yes, you can create bitmap indexes or B-Tree indexes for high-speed searching.

7. Summary

Oracle AI Database 26ai is the latest Long Term Support (LTS) version.
BOOLEAN type (TRUE/FALSE) is available as standard in SQL.
Code readability improves, and affinity with app development increases.
Prepare the latest client drivers when building the environment.

This article explains targeting Oracle AI Database 26ai.

[reference]
Oracle Database Changes, Desupports, and Deprecations