Loki - Installation and configuration. No more code conflicts in the shared Oracle DEV Database.

In my previous blog post (link), you learned about Loki and how it can help you avoid conflicts in a shared Oracle development database.

Now it’s time to learn how to install & set up Loki in your database.

Requirements

APEX 24.1.7 - Loki’s last version was created in this release of APEX, so that’s the minimum you need.

Oracle Database - the required version is 19c. That’s the minimum DB release required to install APEX 24.1.7

However, if you take the time to examine the code thoroughly, I am sure you will be able to adapt it to work with lower versions of APEX or the database easily.

My environment setup for this blog

• I’m using Oracle DB in OCI with the latest APEX installed

• My APEX workspace is called “BOMBSHELL”, and the administrator username is also “BOMBSHELL”.

• My workspace already has two schemas assigned - CO (Customer Orders) and HR (Human Resources)

• There are two developers, John and Rafal, who are about to work together on those schemas.

• I’ve created proxy users for JOHN and RAFAL ( read about PROXY users here )

• John and Rafal both have APEX Builder access as users JOHN_APEX and RAFAL_APEX

1. Download the source code.

Go to “Loki’s” repo here and download the APEX application “loki.sql” by clicking “download RAW file”

2. Create LOKI schema

Log in as an ADMIN or other privileged user and:

• Create a new user LOKI

• Grant necessary privileges to LOKI

create user loki;
grant create table to loki;
grant unlimited tablespace to loki;
grant create procedure to loki;
grant create job to loki;

3. Add LOKI schema to your APEX workspace

The newly created LOKI schema must be visible in your APEX workspace (the one where you will later install the LOKI APEX app). My workspace name is BOMBSHELL.

begin
  apex_instance_admin.add_schema(
    p_workspace => 'BOMBSHELL',
    p_schema    => 'LOKI'
  );
end;
/

You can also do it through APEX builder on INTERNAL workspace.

4. Install the APEX “Loki” app

I am installing Loki on my BOMBSHELL workspace.

Don’t forget to install supporting objects!

There’s quite a lot of those scripts:

Click “Run application” to configure it.

5. Configure the “Loki” setup

After clicking “Run application”, you will see a screen similar to this:

5.1 Add users

• My workspace user, BOMBSHELL, was automatically added as an Administrator.

• I will also create two more users for JOHN and RAFAL, which map to users JOHN, RAFAL ( db users) and JOHN_APEX, RAFAL_APEX (APEX users)

5.2 Add schema triggers

Loki also requires us to add schema triggers.

So I clicked to add it on schemas CO and HR

Copy “grant code” and “trigger code” and execute them manually.

Please note that I added a schema prefix to triggers in my code below, allowing me to execute it as an ADMIN user.

--execute as ADMIN
grant execute on LOKI.loki_lock to CO;
grant execute on LOKI.loki_lock to HR;

create or replace trigger co.loki_before_ddl_tgr before ddl on CO.schema
begin
  execute immediate 'begin LOKI.loki_lock.handle_ddl_event(); end;';
end loki_before_ddl_tgr;
/

create or replace trigger hr.loki_before_ddl_tgr before ddl on HR.schema
begin
  execute immediate 'begin LOKI.loki_lock.handle_ddl_event(); end;';
end loki_before_ddl_tgr;
/

Run the Loki application again to verify that the settings are as you want, and then click “Complete setup”.

You will be redirected to the Loki app:

Summary

In this blog post, I shared how to get Loki up and running in your Oracle development database. I covered everything from the prerequisites to the nitty-gritty steps for setting up users and schemas, so you can avoid those annoying conflicts while working with your team!

I hope you like it.

Cheers.

Rafal