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.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.