Month: January 2025

Oracle DBAs have a long and complicated relationship with DUAL. This single-row, single-column table has been the answer to a quirky limitation for decades: Oracle SQL required a FROM clause on every SELECT statement, even when you just wanted to evaluate an expression.

SELECT SYSDATE FROM DUAL;
SELECT 1 + 1 FROM DUAL;
SELECT SYS_GUID() FROM DUAL;

In every other major database — PostgreSQL, MySQL, SQL Server, SQLite — you can simply write:

SELECT SYSDATE;
SELECT 1 + 1;

With Oracle 23ai, this finally works in Oracle too. The FROM clause is now optional when no table data is needed.

Before (still valid, still works):

SELECT SYSDATE FROM DUAL;
SELECT USER FROM DUAL;
SELECT 100 * 1.08 FROM DUAL;

After (23ai):

SELECT SYSDATE;
SELECT USER;
SELECT 100 * 1.08;

Why does this matter?

Mostly for ergonomics and cross-database compatibility. If you’re writing SQL that needs to run across multiple databases, removing the FROM DUAL dependency makes your code more portable. It also reduces onboarding friction for developers coming from other database backgrounds who find DUAL confusing.

DUAL itself isn’t going anywhere — Oracle has committed to maintaining backward compatibility, and millions of existing queries reference it. But new code no longer needs it for expression evaluation.

This is a small quality-of-life improvement, but it’s the kind that makes you realize Oracle is finally listening more closely to developer ergonomics. Small changes compound. And as we’ll see throughout this series, 23ai has many of them.

Every DBA has seen it: a deployment script fails halfway through because a table already exists, or a DROP command fails because the object isn’t there. The usual workaround was wrapping DDL in PL/SQL exception blocks or using DBMS_METADATA tricks. Not anymore.

Oracle 23ai introduces IF [NOT] EXISTS syntax for DDL statements, bringing Oracle in line with PostgreSQL, MySQL, and SQL Server.

Creating objects safely:

CREATE TABLE employees (
    id     NUMBER,
    name   VARCHAR2(100)
) IF NOT EXISTS;

If the table already exists, Oracle simply skips the command — no error, no exception, no rollback. Clean and predictable.

Dropping objects safely:

DROP TABLE temp_staging IF EXISTS;
DROP INDEX idx_emp_name IF EXISTS;
DROP SEQUENCE seq_invoice IF EXISTS;

No more ORA-00942: table or view does not exist blowing up your scripts.

Supported objects include: TABLE, VIEW, INDEX, SEQUENCE, TRIGGER, PROCEDURE, FUNCTION, PACKAGE, TYPE, and more.

Why this matters for real deployments

If you manage database migrations with tools like Flyway or Liquibase, or if you run repeatable deployment scripts across multiple environments (dev, QA, prod), this feature is a genuine time-saver. You can now write idempotent DDL scripts natively in SQL without PL/SQL wrappers.

Before 23ai, a typical safe-drop pattern looked like:

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE temp_staging';
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/

Now it’s just:

DROP TABLE temp_staging IF EXISTS;

One line. No PL/SQL block. No hidden WHEN OTHERS THEN NULL swallowing real errors.

This is a small feature with a big impact on daily DBA work. If your team manages schema migrations, start using IF [NOT] EXISTS immediately.

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.