Oleksandr Gavenko's blog
2017-03-05 23:50 Create table if not exists for Oracle database

We need to reset our FlyWay history due to major DB refactoring. All changes would be squashed into single that creates base schema so you can 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 is already exist:

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

Unfortunately Oracle database hasn't such syntax. But it can be mimicked with PL/SQL code block and exception handling. Because PL/SQL code doesn't allow DDL code we use dynamic queries:

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;
/

Feeds

all / emacs / java / python

Tags

admin(1), anki(1), blog(1), css(2), cygwin(2), emacs(3), fs(1), git(1), hg(2), html(1), interview(11), java(1), js(3), lighttpd(1), mobile(1), naming(1), printer(1), problem(5), quiz(6), rst(1), security(1), sql(1), srs(1), unit(1), utils(1), vcs(1), web(2), win(2)

Archive