Search

July 21, 2013

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.