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.