Starting from Oracle 23ai, Oracle Database now supports IF EXISTS or IF NOT EXISTS clauses in DDL statements. This alerts you whether an error should be raised if an object exists or does not exist. Let us demontsrate the same with some examples. |
CREATE TABLE: Oracle 19c: Let's create a table in Oracle 19c database. SQL> select banner from v$version; BANNER ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 16:15:17 SQL> create table employee (emp_id number, emp_name varchar2(20),salary number); Table created. 16:15:30 SQL> set time on 16:15:45 SQL> set lines 300 pages 3000 16:15:45 SQL> col owner for a13 16:15:45 SQL> col status for a12 16:15:45 SQL> col object_name for a23 16:15:45 SQL> col object_type for a14 16:15:46 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED ------- ------------- ------------ ------- -------------------- SYS EMPLOYEE TABLE VALID 22-NOV-2024 16:15:30 #Now let's create the same table again and check. 16:23:21 SQL> create table employee (emp_id number, emp_name varchar2(20),salary number); create table employee (emp_id number, emp_name varchar2(20),salary number) * ERROR at line 1: ORA-00955: name is already used by an existing object From the above output, you can see that an error is raised when same object is created again. Let's try with IF NOT EXISTS option. #Now let's create the same table again with "IF NOT EXISTS" clause and check. 16:15:57 SQL> create table if not exists employee (emp_id number, emp_name varchar2(20),salary number); create table if not exists employee (emp_id number, emp_name varchar2(20),salary number) * ERROR at line 1: ORA-00922: missing or invalid option From the above output, you can see that "IF NOT EXISTS" option is invalid in Oracle 19c database. Oracle 23ai: Now let's try the same examples in Oracle 23ai database. SQL> select banner from v$version; BANNER --------------------------------------------------------- Oracle Database 23ai Free Release 23.0.0.0.0 - Production #Create a table in Oracle 23ai database and check the output. 16:28:53 SQL> create table employee (emp_id number, emp_name varchar2(20),salary number); Table created. 16:29:17 SQL> set time on 16:29:25 SQL> set lines 300 pages 3000 16:29:25 SQL> col owner for a13 16:29:25 SQL> col status for a12 16:29:25 SQL> col object_name for a23 16:29:25 SQL> col object_type for a14 16:29:26 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED ------ ------------ ----------- ------- --------------------- SYS EMPLOYEE TABLE VALID 22-NOV-2024 16:29:17 #Let's create the same table again and check. 16:29:58 SQL> create table employee (emp_id number, emp_name varchar2(20),salary number); create table employee (emp_id number, emp_name varchar2(20),salary number) * ERROR at line 1: ORA-00955: name is already used by an existing object Help: https://docs.oracle.com/error-help/db/ora-00955/ You can see in the above output, you will face similar error message as that of Oracle 19c if you try to create the same object again. #Now let's try to create the same table again with "IF NOT EXISTS" option and check. 16:30:00 SQL> create table if not exists employee (emp_id number, emp_name varchar2(20),salary number); Table created. 16:31:49 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED ------ ------------- ------------ ------- -------------------- SYS EMPLOYEE TABLE VALID 22-NOV-2024 16:29:17 You can see that the object is created without any ORA- error message, but the object created time is same as the previous old time when it was created. This means that the operation is skipped instead of raising an error message. This is possible in Oracle 23ai database. #Now let's try to create same table again with adding one new column with IF NOT EXISTS cluase and check. 16:31:53 SQL> create table if not exists employee (emp_id number, emp_name varchar2(20),salary number, address varachar2(20)); create table if not exists employee (emp_id number, emp_name varchar2(20),salary number, address varachar2(20)) * ERROR at line 1: ORA-03060: Data type VARACHAR2 is invalid. Help: https://docs.oracle.com/error-help/db/ora-03060/ You can see that it doesn't allow to create the same table with additional column. An error is raised if you try to do it. |
ALTER TABLE: Oracle 19c: Let's create a table in Oracle 19c database. SQL> select banner from v$version; BANNER ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production #Let's check the table in Oracle 19c database 16:51:31 SQL> set time on 16:51:32 SQL> set lines 300 pages 3000 16:51:32 SQL> col owner for a13 16:51:32 SQL> col status for a12 16:51:32 SQL> col object_name for a23 16:51:32 SQL> col object_type for a14 16:51:32 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED ------- ------------ ----------- ------- --------------------- SYS EMPLOYEE TABLE VALID 22-NOV-2024 16:15:30 #Let's alter the table with addition of new column. 16:54:32 SQL> alter table employee add (address varchar2(20)); Table altered. 16:59:00 SQL> select owner,object_name,object_type,status,to_char(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') last_ddl_time from dba_objects where object_name='EMPLOYEE'; OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_TIME ------ ------------ ----------- ------- -------------------- SYS EMPLOYEE TABLE VALID 22-NOV-2024 16:54:57 #Let's try to alter the table with same column and check. 16:59:23 SQL> alter table employee add (address varchar2(20)); alter table employee add (address varchar2(20)) * ERROR at line 1: ORA-01430: column being added already exists in table You can see that if you try to alter the table with same column then, you will face an error message. #Now let's the same command again, but with IF EXISTS clause and check. 17:01:15 SQL> alter table if exists employee add (address varchar2(20)); alter table if exists employee add (address varchar2(20)) * ERROR at line 1: ORA-01735: invalid ALTER TABLE option You can see in the above output, you will face an error message. This means that IF EXISTS clause if not supported in Oracle 19c. #Now let's try to alter the table with IF EXISTS clause by adding a new column. 17:22:21 SQL> alter table if exists employee add (emp_desig varchar2(20)); alter table if exists employee add (emp_desig varchar2(20)) * ERROR at line 1: ORA-01735: invalid ALTER TABLE option You can see in the above output, you will face same error message though you try to alter it with new column. This means that IF EXISTS clause if not supported in Oracle 19c. Oracle 23ai: Now let's try the same examples in Oracle 23ai database. SQL> select banner from v$version; BANNER --------------------------------------------------------- Oracle Database 23ai Free Release 23.0.0.0.0 - Production #Let's check the table in Oracle 24ai database 16:51:36 SQL> set time on 16:51:37 SQL> set lines 300 pages 3000 16:51:37 SQL> col owner for a13 16:51:37 SQL> col status for a12 16:51:37 SQL> col object_name for a23 16:51:37 SQL> col object_type for a14 16:51:37 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED ------ ------------- ----------- ------- -------------------- SYS EMPLOYEE TABLE VALID 22-NOV-2024 16:29:17 #Let's alter the table with addition of new column and check. 16:51:37 SQL> alter table employee add (address varchar2(20)); Table altered. 16:55:05 SQL> select owner,object_name,object_type,status,to_char(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') last_ddl_time from dba_objects where object_name='EMPLOYEE'; OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_TIME ------ ------------ ------------ ------- -------------------- SYS EMPLOYEE TABLE VALID 22-NOV-2024 16:55:04 #Let's try to alter the table with same column and check. 17:01:56 SQL> alter table employee add (address varchar2(20)); alter table employee add (address varchar2(20)) * ERROR at line 1: ORA-01430: column being added already exists in table Help: https://docs.oracle.com/error-help/db/ora-01430/ You can see that even in 23ai also, if you try to alter the table with same column then, you will face an error message. #Now let's the same command again, but with IF EXISTS clause and check. 17:02:11 SQL> alter table if exists employee add (address varchar2(20)); alter table if exists employee add (address varchar2(20)) * ERROR at line 1: ORA-01430: column being added already exists in table Help: https://docs.oracle.com/error-help/db/ora-01430/ You can see in the above output, you will face same error message. This means that in Oracle 23ai also, you can't alter the table with same column again. #Now let's try the same command for new column with IF EXISTS clause. 17:02:12 SQL> alter table if exists employee add (emp_desig varchar2(20)); Table altered. You can see in Oracle 23ai, you can see IF EXISTS cluase is supported for adding new column in alter command. But anyways, this is not needed as you can directly alter the table withou IF EXISTS clause. 17:23:09 SQL> set lines 120 17:24:11 SQL> desc employee Name Null? Type -------------------------- -------- ------------- EMP_ID NUMBER EMP_NAME VARCHAR2(20) SALARY NUMBER ADDRESS VARCHAR2(20) EMP_DESIG VARCHAR2(20) You have seen the above ALTER examples for a table that already exists in the database. Now let's try with a table that does not exist in the database. Oracle 19c: #Let's drop the already created table. 18:05:47 SQL> drop table employee purge; Table dropped. #Now table does not exist in the database. 18:06:32 SQL> set time on 18:06:32 SQL> set lines 300 pages 3000 18:06:32 SQL> col owner for a13 18:06:32 SQL> col status for a12 18:06:32 SQL> col object_name for a23 18:06:32 SQL> col object_type for a14 18:06:32 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; no rows selected #Let's try to alter the table that does not exist in the database. 18:07:23 SQL> alter table employee add (emp_name varchar2(20)); alter table employee add (emp_name varchar2(20)) * ERROR at line 1: ORA-00942: table or view does not exist You can see in the above output, if you try to alter the table that does not exist then you will face below error message in older versions. #Let's try to alter the table that does not exist in the database with IF EXISTS clause. 18:07:24 SQL> alter table if exists employee add (emp_name varchar2(20)); alter table if exists employee add (emp_name varchar2(20)) * ERROR at line 1: ORA-01735: invalid ALTER TABLE option You can see in the above output, if you try to alter the table that does not exist then you will still face an error message in older versions since IF EXISTS clause is not supported below 23ai versions. Let's try with Oracle database 23ai. Oracle 23ai: #Let's drop the already created table and check the output. 17:24:12 SQL> drop table employee purge; Table dropped. 18:06:01 SQL> set time on 18:06:34 SQL> set lines 300 pages 3000 18:06:34 SQL> col owner for a13 18:06:34 SQL> col status for a12 18:06:34 SQL> col object_name for a23 18:06:34 SQL> col object_type for a14 18:06:34 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; no rows selected #Try to alter the table and check the error message. 18:06:35 SQL> alter table employee add (emp_name varchar2(20)); alter table employee add (emp_name varchar2(20)) * ERROR at line 1: ORA-00942: table or view "SYS"."EMPLOYEE" does not exist Help: https://docs.oracle.com/error-help/db/ora-00942/ You can see in the above output, if you try to alter the table then you will face same error message that you faced in older versions. #Let's try to alter the table that does not exist in the database with IF EXISTS clause. 18:07:27 SQL> alter table if exists employee add (emp_name varchar2(20)); Table altered. 18:07:37 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; no rows selected You can see in the above output, it is possible in Oracle database 23ai to alter the table even if the table does not exist in the database. User will not face any error message, instead the error will be skipped. You can see the table is not avaible in the database, but user will not face error. |
DROP TABLE: Let's create a table in Oracle 19c database. Oracle 19c: SQL> select banner from v$version; BANNER ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production #Let's check the table in Oracle 19c database and check the output. 18:48:29 SQL> create table employee (id number, name varchar2(20)); Table created. 18:48:53 SQL> set time on 18:50:31 SQL> set lines 300 pages 3000 18:50:31 SQL> col owner for a13 18:50:31 SQL> col status for a12 18:50:31 SQL> col object_name for a23 18:50:31 SQL> col object_type for a14 18:50:31 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED ------ ------------ ----------- ------- -------------------- SYS EMPLOYEE TABLE VALID 22-NOV-2024 18:48:53 #Now drop the table with IF EXISTS cluase. 18:50:32 SQL> drop table if exists employee; drop table if exists employee * ERROR at line 1: ORA-00933: SQL command not properly ended You can see in the above output, IF EXISTS clause not available in older release and hence you will face above error message while dropping a table with IF EXISTS clause. Let's try the same examples in Oracle Database 23ai. Oracle 23ai: #Let's check the table in Oracle 23ai database and check the output. 18:47:33 SQL> create table employee (id number, name varchar2(20)); Table created. 18:49:11 SQL> set time on 18:50:35 SQL> set lines 300 pages 3000 18:50:35 SQL> col owner for a13 18:50:35 SQL> col status for a12 18:50:35 SQL> col object_name for a23 18:50:35 SQL> col object_type for a14 18:50:35 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED ------ ----------- ----------- ------ -------------------- SYS EMPLOYEE TABLE VALID 22-NOV-2024 18:49:11 #Now try to drop the table with IF EXISTS clause. 18:50:35 SQL> drop table if exists employee; Table dropped. 18:51:30 SQL> select owner,object_name,object_type,status,to_char(created,'DD-MON-YYYY HH24:MI:SS') created from dba_objects where object_name='EMPLOYEE'; no rows selected You can see in the above output, table has been dropped with IF EXISTS cluase in Oracle database 23ai. |
Visit "Oracle Database 23ai Free - All in one page" to access all blog posts on Oracle 23ai in one page.
Thanks for reading this post ! Please comment if you like this post ! Click FOLLOW to get future blog updates !
Great job sir
ReplyDeleteVery useful information
ReplyDelete