When creating an object, if that object already exists, an “already exists” error will occur. Similarly, if you try to drop an object that does not exist, you’ll encounter a “does not exist” error. To prevent these two errors, you can use the IF [NOT] EXISTS clause.
For example, if a table named TB already exists but you're not sure about it, you can use the IF NOT EXISTS clause in the CREATE TABLE statement to avoid a possible ORA-00955 error:
SQL> create table tb (c1 number(10));
ORA-00955: name is already used by an existing object
SQL> create table if not exists tb (c1 number(10));
Table created
SQL> create table if not exists tb (c1 number(10));
Table created
This clause is also applicable to other DDL statements:
SQL> drop sequence ss;
ORA-02289: sequence does not exist
SQL> drop sequence if exists ss;
Sequence dropped
SQL> create user usef identified by ss;
ORA-01920: user name 'USEF' conflicts with another user or role name
SQL> create user if not exists usef identified by ss;
User created
SQL> create user if not exists usef identified by ss;
User created
SQL> drop user vahid cascade;
ORA-01918: user 'VAHID' does not exist
SQL> drop user if exists vahid cascade;
User dropped
SQL> drop procedure proc1;
ORA-04043: Object PROC1 does not exist.
SQL> drop procedure if exists proc1;
Procedure dropped
However, note that the IF [NOT] EXISTS clause does not cover all DDL statements:
SQL> alter table if exists tb add (id number);
Table altered
SQL> alter table if exists tb add (id number);
ORA-01430: column being added already exists in table
SQL> create or replace view vw_tb as select * from tb;
View created
SQL> create or replace view if not exists vw_tb as select * from tb;
ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement
Top comments (0)