To avoid errors and extra errors handling in script or PL/SQL code Oracle extended CREATE/DROP DDL with IF[NOT]EXISTS clause

CREATE TABLE IF NOT EXISTS test_tbl
(
id1 NUMBER
);

DROP TABLE IF EXISTS test_tbl;

it’s real game changer for coding. Life is more simple now.

IF NOT EXIST

Example SCRIPT1 should run without errors if objects dont exists in your schema.

CREATE TABLE test_tbl
(
id1 NUMBER
);

CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1;

CREATE VIEW test_vw AS SELECT * FROM test_tbl;

CREATE USER test_user IDENTIFIED BY test_user;

CREATE FUNCTION fn_test
RETURN NUMBER
IS
BEGIN
RETURN 1;
END;
/

as output

Table TEST_TBL created.

Sequence TEST_SEQ created.

View TEST_VW created.

User TEST_USER created.

Function FN_TEST compiled

but if you would like to rerun it you should get errors

CREATE TABLE test_tbl
(
id1 NUMBER
);

CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1;

CREATE VIEW test_vw AS SELECT * FROM test_tbl;

CREATE USER test_user IDENTIFIED BY test_user;

CREATE FUNCTION fn_test
RETURN NUMBER
IS
BEGIN
RETURN 1;
END;
/

output

Error starting at line : 1 in command -
CREATE TABLE test_tbl
(
id1 NUMBER
)
Error report -
ORA-00955: name is already used by an existing object


Error starting at line : 6 in command -
CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1
Error report -
ORA-00955: name is already used by an existing object


Error starting at line : 8 in command -
CREATE VIEW test_vw AS SELECT * FROM test_tbl
Error report -
ORA-00955: name is already used by an existing object


Error starting at line : 10 in command -
CREATE USER test_user IDENTIFIED BY test_user
Error report -
ORA-01920: user name 'TEST_USER' conflicts with another user or role name


Error starting at line : 12 in command -
CREATE FUNCTION fn_test
RETURN NUMBER
IS
BEGIN
RETURN 1;
END;
Error report -
ORA-00955: name is already used by an existing object

With simple modification of the script you can make it more resistant for errors

CREATE TABLE IF NOT EXISTS test_tbl
(
id1 NUMBER
);

CREATE SEQUENCE IF NOT EXISTS test_seq START WITH 1 INCREMENT BY 1;

CREATE VIEW IF NOT EXISTS test_vw AS SELECT * FROM test_tbl;

CREATE USER IF NOT EXISTS test_user IDENTIFIED BY test_user;

CREATE FUNCTION IF NOT EXISTS fn_test
RETURN NUMBER
IS
BEGIN
RETURN 1;
END;
/

as output you get

Table TEST_TBL created.

Sequence TEST_SEQ created.

View TEST_VW created.

User TEST_USER created.

Function IF compiled

This time no error

IF EXISTS

Example SCRIPT2 requires SCRIPT1 to be executed

DROP TABLE test_tbl;

DROP SEQUENCE test_seq;

DROP VIEW test_vw;

DROP USER test_user;

DROP FUNCTION fn_test;

First time execution output

Table TEST_TBL dropped.
Sequence TEST_SEQ dropped.
View TEST_VW dropped.
User TEST_USER dropped.
Function FN_TEST dropped.

Second time execution output

Error starting at line : 20 in command -
DROP TABLE test_tbl
Error report -
ORA-00942: table or view does not exist

Error starting at line : 21 in command -
DROP SEQUENCE test_seq
Error report -
ORA-02289: sequence does not exist

Error starting at line : 22 in command -
DROP VIEW test_vw
Error report -
ORA-00942: table or view does not exist

Error starting at line : 23 in command -
DROP USER test_user
Error report -
ORA-01918: user 'TEST_USER' does not exist

Error starting at line : 24 in command -
DROP FUNCTION fn_test
Error report -
ORA-04043: Object FN_TEST does not exist.

as solution you need to modify the script

DROP TABLE IF EXISTS test_tbl;

DROP SEQUENCE IF EXISTS test_seq;

DROP VIEW IF EXISTS test_vw;

DROP USER IF EXISTS test_user;

DROP FUNCTION IF EXISTS fn_test;

so rerun is not giving error

Table TEST_TBL dropped.

Sequence TEST_SEQ dropped.

View TEST_VW dropped.

User TEST_USER dropped.

Function FN_TEST dropped.

Have a fun 🙂

  Tomasz