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.
