DBA Hub

📋Steps in this guide1/5

IF [NOT] EXISTS DDL Clause in Oracle Database 23ai/26ai

Prevent errors during object creation and removal using the IF [NOT] EXISTS DDL clause in Oracle Database 23ai/26ai.

oracle 23configurationintermediate
by OracleDba
25 views
1

Setup

The following objects are required by the examples below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Test user.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create user testuser1 identified by testuser1;
grant connect, resource to testuser1;


-- Test objects.
conn testuser1/testuser1@//localhost:1521/freepdb1

create table t1 (id number);
create sequence t1_seq;

create view t1_v as
select * from t1;

create procedure p1 as
begin
  null;
end;
/
2

The Problem

When creating database change scripts for applications we may not know if an object already exists, which can lead to errors. For example, the following commands would fail because the objects are already present. In a similar way, attempting to drop an object that doesn't exist will result in an error.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SQL> conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

Connected.

SQL> create user testuser1 identified by testuser1;
create user testuser1 identified by testuser1
            *
ERROR at line 1:
ORA-01920: user name 'TESTUSER1' conflicts with another user or role name


SQL>


SQL> conn testuser1/testuser1@//localhost:1521/freepdb1

Connected.

SQL> create table t1 (id number);
create table t1 (id number)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>


SQL> create sequence t1_seq;
create sequence t1_seq
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>

SQL> drop table t3 purge;
drop table t3 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
3

The Solution : IF [NOT] EXISTS

Oracle 23ai/26ai allows us to use the DDL clause to mask errors when we attempt to create objects that already exist, or drop objects that don't exist. Here is an example with user creation. The user already exists, so it is not created, but the command doesn't return an error. When we attempt to drop the user, which doesn't exist, it also doesn't give an error. We see similar behaviour with object creation. As expected, we can attempt to drop objects which do not exist without errors. We can include a presence check in an statement, but in the case of adding column, we can't guard against a column already being present.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
SQL> conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

Connected.

SQL> create user if not exists testuser1 identified by testuser1;

User created.

SQL>


SQL> drop user if exists testuser3 cascade;

User dropped.

SQL>

SQL> conn testuser1/testuser1@//localhost:1521/freepdb1

Connected.

SQL> create table if not exists t1 (id number);

Table created.

SQL>


SQL> create sequence if not exists t1_seq;

Sequence created.

SQL>


SQL> create view if not exists t1_v as
select * from t1;

View created.

SQL>


SQL> create procedure if not exists p1 as
begin
  null;
end;
/

Procedure created.

SQL>

SQL> drop table if exists t3;

Table dropped.

SQL> drop sequence if exists t3_seq;

Sequence dropped.

SQL> drop view if exists t3_v;

View dropped.

SQL> drop procedure if exists p3;

Procedure dropped.

SQL>

SQL> alter table if exists t1 add (id number);
alter table if exists t1 add (id number)
                              *
ERROR at line 1:
ORA-01430: column being added already exists in table


SQL>
4

CREATE OR REPLACE

We can't mix and the clause.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> create or replace view if not exists t1_v as
select * from t1;
create or replace view if not exists t1_v as
                                     *
ERROR at line 1:
ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement


SQL>


SQL> create or replace procedure if not exists p1 as
begin
  null;
end;
/
create or replace procedure if not exists p1 as
                                          *
ERROR at line 1:
ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement


SQL>
5

Considerations

Here are some things to consider when using this functionality. - If we are using the output from a script as an audit of the actions processed, this can be misleading. We will get reports of object creations that didn't actually happen. - During object creation the clause is a simple presence check based on the object name. It doesn't compare the structure of the object and report if there is a difference. - There are limitations to the clause. As demonstrated above, we can't prevent an error if we try to add a column to a table that is already present. - The output of some older versions of tools is different to what was presented above in 23ai/26ai SQL*Plus. For example older versions of SQL Developer, SQLcl and SQL Developer Web all return "Table IF created." rather than "Table {table-name} created" when the presence check is added. Upgrade to the latest tools where possible. For more information see: - Using IF EXISTS and IF NOT EXISTS Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!