changeset 116:f8d45fa94539

Create table if not exists for Oracle database.
author Oleksandr Gavenko <gavenkoa@gmail.com>
date Mon, 06 Mar 2017 00:09:24 +0200
parents 41d2109cecab
children cac91c4dfae8
files 144b3e26-1301-4cc6-8e87-9395163ea633/index.rst
diffstat 1 files changed, 41 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/144b3e26-1301-4cc6-8e87-9395163ea633/index.rst	Mon Mar 06 00:09:24 2017 +0200
@@ -0,0 +1,41 @@
+
+================================================
+ Create table if not exists for Oracle database
+================================================
+:created: 2017-03-05 23:50
+:updated: 2017-03-05
+
+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;
+  /
+