Using IF [NOT] EXISTS Syntax (23c)

Oracle Database 23c introduces a powerful enhancement in Data Definition Language (DDL) with the introduction of the IF [NOT] EXISTS syntax. This feature streamlines the management of database objects by allowing developers and database administrators to conditionally execute DDL statements based on the existence of the specified objects.

Syntax Overview

The IF [NOT] EXISTS syntax can be used with various DDL statements, such as CREATE, ALTER, and DROP. The basic syntax is as follows:

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype1,
    column2 datatype2,
    ...
);

DROP TABLE IF EXISTS table_name;

Example Use Case:

Let’s consider a scenario where you want to ensure the existence of a table before creating it, avoiding errors if the table already exists.

-- Attempt to create a table without IF NOT EXISTS 
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    salary NUMBER
);

-- Create the table only if it doesn't already exist
CREATE TABLE IF NOT EXISTS employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    salary NUMBER
);

In the first example, if the employees table already exists, attempting to create it again without the IF NOT EXISTS clause results in an error. The second example, using the new syntax, ensures that the table is created only if it does not already exist.