Oracle: CREATE … IF NOT EXISTS … / DROP … IF EXISTS …

Oracle 23c has added the if not exists clause to the create SQL verb, and if exists clause to the drop statement which prevent errors from being thrown when creating or dropping objects (tables, views etc.).

Demonstration

The following statement creates a table with one column, named num:

create table tq84_tab (num number);

The following statement creates the table tq84_tab if it does not already exist. Since the table aready exists, the table is not created, but the error ORA-00955: name is already used by an existing object is not thrown, either:

create table if not exists tq84_tab(txt varchar2(10));

When showing the table structure with SQL*Plus desc command, it shows that the table's column's name is num, not txt.

SQL> desc tq84_tab

Name Null? Type   
---- ----- ------ 
NUM        NUMBER

The following statement drop the table if it exists:

drop table if exists tq84_tab;

The following statement does not throw the error message ORA-00942: table or view does not exist because of the if exists clause:

drop table if exists tq84_tab;

Oracle: CREATE … IF NOT EXISTS … / DROP … IF EXISTS …

Demonstration

See also