Preparations: Setting up the database

[Back to Overview]

In order to actually run the examples, you have a couple of options:

• If you have a local Oracle database available, you can of course run the below setup-script in any schema you want on that database.

• If you have docker installed, you can very easily create an oracle-xe container of the free-to-use Oracle 21c XE version

docker run -d -p 1521:1521 -e ORACLE_PASSWORD=<your password> --name test-impdb gvenzl/oracle-xe

• Gerald Venzl also put the images on Github

docker pull ghcr.io/gvenzl/oracle-xe:slim

• You can also head straight to Oracle Live-SQL, create a free account and use an online Oracle database to try out the example

Setting up a user

If you were going with a new database, you first need to create a user and schema you can use to work with. Working with the SYS user is dangerous and strongly not recommended.

You can use the following script (run as sys) to create a new user TESTERSAM (script can also be found here)

create user testersam 
  identified by :password 
  default tablespace users 
  quota unlimited on users;

grant 
  create session, 
  create sequence, 
  create procedure, 
  create type, 
  create table, 
  create view, 
  create synonym, 
  create trigger 
to testersam;

grant alter session to testersam;

Setup-Script

The following script will set up all the objects you need to start with the Examples.

You can also find it here.

/* Cleanup: This will delete all the elements created by the examples */
declare
  procedure drop_if_exists(i_name varchar2, i_type varchar2) as
    l_exists integer;
  begin
    select count(*) into l_exists from user_objects
      where object_name = i_name
        and object_type = i_type;
    if l_exists > 0 then
      execute immediate 'drop '||i_type||' "'||i_name||'"';
      dbms_output.put_line('Dropped '||i_type||' "'||i_name||'"');
    else
      dbms_output.put_line(i_type||' "'||i_name||'" does not exist');
    end if;
  end;
begin
  drop_if_exists('DEATHSTAR_USERS', 'VIEW');
  drop_if_exists('V_USERS_DARKSIDE', 'VIEW');
  drop_if_exists('T_DEATHSTAR_USERS', 'TABLE');
  drop_if_exists('DEATHSTAR_USERS', 'TABLE');
  drop_if_exists('ALIGNMENTS', 'TABLE');
  drop_if_exists('ROLES', 'TABLE');
end;
/


/* Setup: This will create the necessary data in their form before the first example */
create table deathstar_users (
  id integer primary key,
  name varchar2(4000),
  alignment varchar2(256),
  salary varchar2(100),
  role varchar2(256)
);

insert into deathstar_users ( id, name, alignment, salary, role ) values (1, 'Darth Vader'   , '„dark“'  , '10‘000.00', 'Sith Lord');
insert into deathstar_users ( id, name, alignment, salary, role ) values (2, 'Darth Vader'   , ''        , '10000.00' , 'Boss'     );
insert into deathstar_users ( id, name, alignment, salary, role ) values (3, 'darth VAder'   , '„Dark“'  , '15000.00' , 'Sith Lord');
insert into deathstar_users ( id, name, alignment, salary, role ) values (4, 'Mace Windu'    , '„light“' , '-'        , 'Prisoner' );
insert into deathstar_users ( id, name, alignment, salary, role ) values (5, 'Luke Skywalker', '„bright“', '-300.42'  , 'Pirsoner' );
insert into deathstar_users ( id, name, alignment, salary, role ) values (6, 'Darth Maul'    , '„dark „' , '5000,00'  , 'Sith'     );
insert into deathstar_users ( id, name, alignment, salary, role ) values (7, 'Obi-Wan Kenobi', '„light“' , '0.00'     , 'Prisoner' );

commit;

create or replace view v_users_darkside as
  select
    *
  from deathstar_users
  where lower(regexp_replace(alignment, '[^[:alpha:]]', '')) = 'dark';

Next: Example 1: High-level availability checks with NodeJS and the Mocha testing framework