Exploring the New Boolean Data Type in Oracle 23c AI

This exciting release of Boolean Data Type Lab for Oracle Database 23c AI introduces native support for the boolean data type, a game-changer for database developers. With this enhancement, you can natively represent true, false, and null values, simplifying data modeling and making SQL and PL/SQL development more efficient.

To illustrate, let’s recreate a scenario involving a Student Information System, where we’ll track whether students are currently enrolled in courses using the new boolean data type.

This feature enhances developer productivity and makes Oracle Database more intuitive to work with.

Creating a Table With the Boolean Data Type

We will begin by creating a STUDENTS table that includes a boolean column named CURRENTLY_ENROLLED:

DROP TABLE IF EXISTS STUDENTS CASCADE CONSTRAINT;

-- Create STUDENTS table with a boolean column
CREATE TABLE STUDENTS (
    STUDENT_ID         NUMBER PRIMARY KEY,
    NAME               VARCHAR2(100),
    COURSE             VARCHAR2(100),
    CURRENTLY_ENROLLED BOOLEAN
);

Inserting Data With Boolean Values

Now let’s populate the table with sample data using different boolean literals:

-- Insert sample data using boolean values and accepted boolean literals
INSERT INTO STUDENTS VALUES (1, 'Alice', 'Mathematics', TRUE),
                             (2, 'Bob', 'Physics', FALSE),
                             (3, 'Charlie', 'Chemistry', 'yes'),
                             (4, 'Diana', 'Biology', 'no'),
                             (5, 'Eve', 'Computer Science', 'on'),
                             (6, 'Frank', 'Statistics', 'off'),
                             (7, 'Grace', 'History', 1),
                             (8, 'Hank', 'Philosophy', 0),
                             (9, 'Ivy', 'Engineering', NULL);

Oracle maps various inputs, like 'yes', 'no', 1, and 0 to their corresponding boolean values. Let’s verify the data:

SELECT student_id, name, course, currently_enrolled
FROM students;

Querying Boolean Data

Finding Currently Enrolled Students

To retrieve students who are currently enrolled:

SELECT student_id, name, course
FROM students
WHERE currently_enrolled = TRUE;

Logical Operations on Boolean Columns

Boolean values allow for direct logical operations. For example, filtering students considered "actively enrolled":

SELECT student_id, name, course
FROM students
WHERE currently_enrolled AND TRUE;

Finding Non-Enrolled Students

To identify students who are not currently enrolled:

SELECT student_id, name, course
FROM students
WHERE NOT currently_enrolled;

Combining Boolean Logic With Other Conditions

Let’s retrieve students who are either enrolled or whose names start with 'A':

SELECT student_id, name, course
FROM students
WHERE currently_enrolled OR name LIKE 'A%';

Updating Boolean Values

Marking a Student as Enrolled

Suppose we want to update Bob’s enrollment status:

UPDATE students
SET currently_enrolled = TRUE
WHERE student_id = 2;

-- Verify the update
SELECT name, currently_enrolled
FROM students
WHERE student_id = 2;

Using PL/SQL With Boolean Data Type

PL/SQL seamlessly integrates with the new boolean data type. Let’s add a new student:

DECLARE
    l_student_id NUMBER := 10;
    l_name       VARCHAR2(100) := 'John';
    l_course     VARCHAR2(100) := 'Data Science';
    l_enrolled   BOOLEAN := TRUE;
BEGIN
    INSERT INTO students (student_id, name, course, currently_enrolled)
    VALUES (l_student_id, l_name, l_course, l_enrolled);

    COMMIT;
END;

Let's validate the newly inserted data.

Conclusion

The introduction of the boolean data type in Oracle Database 23c AI simplifies database design and improves the readability and maintainability of your SQL and PL/SQL code. Reducing reliance on workarounds like numeric or character codes empowers developers to write cleaner and more intuitive queries.

Whether managing a Student Information System or any other application, the boolean data type makes handling logical values straightforward and robust. Get hands-on experience today, and see how this feature can elevate your database projects.