Skip to content

Instantly share code, notes, and snippets.

@DamianReeves
Created January 13, 2016 20:35
Show Gist options
  • Save DamianReeves/3cf07240a3c69c3c1f68 to your computer and use it in GitHub Desktop.
Save DamianReeves/3cf07240a3c69c3c1f68 to your computer and use it in GitHub Desktop.
Even Oracle schema template
declare
table_count integer;
begin
select count (object_id) into table_count from user_objects where exists (
select object_name from user_objects where (object_name = upper('{0}') and object_type = 'TABLE'));
if table_count = 0 then
dbms_output.put_line('Creating the {0} table');
execute immediate (
'create table {0} (
GlobalSequence number not null,
EventID varchar2(36) not null,
StreamHash raw(20) not null,
StreamName varchar2(200) not null,
EventType varchar2(50) not null,
UtcTimeStamp timestamp not null,
Metadata blob,
Payload blob not null,
PayloadFormat number not null,
constraint PK_{0} primary key (GlobalSequence))');
execute immediate ('create index IX_{0}_ on {0} (StreamHash)');
execute immediate ('create sequence {0}_GlobSeq');
execute immediate ('
create or replace trigger {0}_GlobSeq_Trig
before insert on {0}
for each row
begin
select {0}_GlobSeq.nextval
into :new.GlobalSequence
from dual;
end;');
else
dbms_output.put_line ('The {0} table already exists in the database.');
end if;
select count (object_id) into table_count from user_objects where exists (
select object_name from user_objects where (object_name = upper('{1}') and object_type = 'TABLE'));
if table_count = 0 then
dbms_output.put_line('Creating the {1} table');
execute immediate (
'create table {1} (
ProjectionStreamHash raw(20) not null,
ProjectionStreamSequence number(19) not null,
GlobalSequence number(19) not null,
constraint PK_{1} primary key (ProjectionStreamHash, ProjectionStreamSequence)
)');
else
dbms_output.put_line ('The {1} table already exists in the database.');
end if;
select count (object_id) into table_count from user_objects where exists (
select object_name from user_objects where (object_name = upper('{2}') and object_type = 'TABLE'));
if table_count = 0 then
dbms_output.put_line('Creating the {2} table');
execute immediate (
'create table {2} (
ProjectionStreamHash raw(20) not null primary key,
LastGlobalSequence number(19) not null
)');
else
dbms_output.put_line ('The {2} table already exists in the database.');
end if;
exception when others then dbms_output.put_line('An unexpected exception has occured. Please re-evaluate the PL/SQL script');
end;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment