Create table if not exists in Oracle

2017-03-05 23:50 Create table if not exists in Oracle

We need to reset our Flyway history due to major DB refactoring. All changes would be squashed into a single that creates a base schema to be able to recreate database from scratch.

But we don't want conflicts with existing tables in production.

MySQL database has syntax to prevent error on create table if table already exists:

create table if not exist "TBL" (...);

Unfortunately Oracle database doesn't have such syntax. It can be mimicked with PL/SQL code block and exception handling. Because PL/SQL code doesn't allow DDL code dynamic queries are used:

declare
begin
  execute immediate 'create table "TBL" ("ID" number not null)';
  exception when others then null;
end;
/

More safer implementation checks for error code:

ORA-00955: name is already used by an existing object

which same for create table, and create index, and create sequence, etc and report error otherwise. Final solution looks like:

declare
begin
  execute immediate '
    create table "TBL" ("ID" number not null)';
  exception when others then
    if SQLCODE = -955 then null; else raise; end if;
end;
/

sql, oracle

Feeds

Tags

Archive