Conditional Table Creation for Oracle

A quick example script for creating a table in an Oracle database only when it does not already exist.

declare objExists number; theSql varchar2(4000); begin begin select 1 into objExists from USER_TABLES where TABLE_NAME = 'MY_NEW_TABLE'; exception when no_data_found then null; end; if (objExists is null) then theSql := 'create table MY_NEW_TABLE ( MY_NEW_TABLE_ID number(38), A_VARCHAR varchar2(1000) )'; execute immediate theSql; end if; -- We can also add constraints conditionally begin select 1 into objExists from USER_CONSTRAINTS where CONSTRAINT_NAME = 'MY_NEW_TABLE_PK'; exception when no_data_found then null; end; if (objExists is null) then theSql := 'alter table MY_NEW_TABLE add constraint MY_NEW_TABLE_PK primary key (MY_NEW_TABLE_ID)'; execute immediate theSql; end if;

Hat tip to Vance Duncan.