Oracle 23c, if exists and if not exists.

In the old days before Oracle 23c, you had two options when creating build scripts. 1) Take the error message such as ORA-00942 Table or View does not exists, or you could write pl/sql and trap the error like this.

declare
  procedure Drop_Table(p_table in varchar2) is
    Table_Doesnt_Exist exception;
    pragma Exception_Init(Table_Doesnt_Exist, -00942);
  begin
    execute immediate 'drop table '||p_table;
  exception when Table_Doesnt_Exist then null;
  end Drop_Table;
begin
  Drop_Table(‘t’);
end;
/

This worked well; however, as you can see your cleanup code could take up some space. So, your choice, accept the error message or write some code. Both worked. But now we have a better way, if Oracle 23c you now have the if exists and if not exists options. Life is good.

SQL> create table if not exists t (x number);

Table T created.

SQL> drop table if exists t;

Table T dropped.