We covered IF [NOT] EXISTS in January. Let’s go deeper — specifically into how it integrates with migration tooling and advanced scripting patterns.
Idempotent schema migrations:
The dream of every migration tool is an idempotent script — one you can run multiple times safely, producing the same result. Oracle 23ai makes this achievable natively:
-- Safe initial setup script
CREATE TABLE customers IF NOT EXISTS ( ... );
CREATE SEQUENCE seq_customer IF NOT EXISTS START WITH 1;
CREATE INDEX idx_customer_email IF NOT EXISTS ON customers(email);
CREATE VIEW active_customers IF NOT EXISTS AS SELECT ...;
-- Safe cleanup
DROP TABLE temp_migration_log IF EXISTS;
DROP INDEX idx_old_email_format IF EXISTS;Conditional object creation based on environment:
-- Only create the debug table in non-production environments
CREATE TABLE debug_trace_log (
trace_id NUMBER,
trace_time TIMESTAMP DEFAULT SYSTIMESTAMP,
trace_msg CLOB
) IF NOT EXISTS;Combining with PL/SQL for complex logic:
BEGIN
-- Create the table if new, then add a column to it
EXECUTE IMMEDIATE 'CREATE TABLE config_params (
param_name VARCHAR2(100) PRIMARY KEY,
param_value VARCHAR2(4000)
) IF NOT EXISTS';
-- Add column (IF NOT EXISTS works for ALTER too)
EXECUTE IMMEDIATE 'ALTER TABLE config_params
ADD (last_modified DATE DEFAULT SYSDATE) IF NOT EXISTS';
END;ALTER TABLE … ADD column IF NOT EXISTS is new in 23ai and is one of the most requested migration helpers — no more checking ALL_TAB_COLUMNS before adding a column in a script.
For Flyway, Liquibase, or custom migration frameworks targeting Oracle 23ai, these additions dramatically reduce the complexity of migration scripts.
