IF EXISTS and IF NOT EXISTS in SQL in Oracle 23ai Database

Oracle 23ai database now allows you to use IF EXISTS and IF NOT EXISTS.

e.g.

CREATE TABLE IF NOT EXISTS demo (d_id number, d_name VARCHAR2(10));

DROP TABLE IF EXISTS demo;

This helps with portability of code across vendors and also means less errors when creating or dropping certain database objects.

NOTE: It can’t be used together with REPLACE – so using something like:

CREATE OR REPLACE SYNONYM IF NOT EXISTS test_syn FOR test;

would result in an error ORA-11541.