Blockchain with Oracle and APEX

Thus the whole IT world talks about blockchains, the bitcoin price is as high as never (which underlying technology is a blockchain mechanism) and even Oracle has announced a new Blockchain Cloud Service at OOW, I looked into that topic and tried to create a simple blockchain by my own using the Oracle Database and of course APEX…

Before we can start developing on a blockchain mechanism we have to investigate some time to get a basic understanding what a blockchain is and how it works all together. So lets get started with some terminology:

What is a Blockchain?
A blockchain is a distributed database with a list (chain) of records (blocks) linked and secured by digital fingerprints (crypto hashes)

[#<Block:0x1eed2a0
  @index = 0,
  @timestamp = 1637-09-15 20:52:38,
  @data = "Genesis",
  @previous_hash = "0",
  @hash = "edbd4e11e69bc399a9ccd8faaea44fb27410fe8e3023bb9462450a0a9c4caa1b">,
 #<Block:0x1eec9a0
  @index = 1,
  @timestamp = 1637-09-15 21:02:38,
  @data = "Transaction Data...",
  @previous_hash = "edbd4e11e69bc399a9ccd8faaea44fb27410fe8e3023bb9462450a0a9c4caa1b",
  @hash = "eb8ecbf6d5870763ae246e37539d82e37052cb32f88bb8c59971f9978e437743">,
 #<Block:0x1eec838
  @index = 2,
  @timestamp = 1637-09-15 21:12:38,
  @data = "Transaction Data......",
  @previous_hash = "eb8ecbf6d5870763ae246e37539d82e37052cb32f88bb8c59971f9978e437743",
  @hash = "be50017ee4bbcb33844b3dc2b7c4e476d46569b5df5762d14ceba9355f0a85f4">,
  ...

What’s a Hash? What’s a Crypto(graphic) Hash Digest Checksum?
A hash e.g. eb8ecbf6d5870763ae246e37539d82e37052cb32f88bb8c59971f9978e437743 is a small digest checksum calculated with a one-way crypto(graphic) hash digest checksum function e.g. SHA256 (Secure Hash Algorithm 256 Bits) from the data

sha = Digest::SHA256.new
sha.update( @index + @timestamp + @data + @previous_hash )

A blockchain uses

  • the block index (e.g. 1,2,3,4, etc.)

  • the block timestamp (e.g. 10-OKT-2017 16:16:59,216566 +02:00)

  • the block data (e.g. Transaction Data…)

  • the hash from the previous block (e.g. edbd4e11e69bc399a9ccd8faaea44fb27410fe8e3023bb9462450a0a9c4caa1b)

  • the hash (e.g. be50017ee4bbcb33844b3dc2b7c4e476d46569b5df5762d14ceba9355f0a85f4): calculated by the hash digest checksum of (@index + @timestamp + @data + @previous_hash)

By design, blockchains are inherently resistant to modification of the data. You can not modify one block without modifying all other blocks in the chain too.
If the blockchain is shared and distributed with others it is even more resistant to modification…

Now, after knowing some basic concepts about blockchains the next step would be: How can we integrate such technology into the Oracle Database? And of course a basic blockchain is kind of simple to implement.

Our blockchain would be nothing more than a simple table with the columns we already saw in the example above: index, timestamp, data, previous hash and hash.

One table row would then be one block of the blockchain.

So lets get finally into what we all like, PL/SQL stuff to explain it a bit better:

1. Our blockchain table

-- Create table
create table BLOCKCHAIN
(
  bc_index         number not null,
  bc_timestamp     timestamp with time zone not null,
  bc_data          clob not null,
  bc_previous_hash varchar2(500) not null,
  bc_hash          varchar2(500) not null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table BLOCKCHAIN
  add constraint BLOCKCHAIN_PK primary key (BC_INDEX);

2. A sequence that increments our index column (Primary Key)

-- Create sequence 
create sequence BLOCKCHAIN_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache;

3. An before insert trigger that set our index column to a new incremented value on each insert

-- Create Before Insert Trigger
CREATE OR REPLACE TRIGGER blockchain_bi_trg
  BEFORE INSERT ON blockchain
  FOR EACH ROW
DECLARE
BEGIN
  IF :new.bc_index IS NULL THEN
    :new.bc_index := blockchain_seq.nextval;
  END IF;
END;

After that, our blockchain is in place and waits to get filled with data. Before we can do this we will need some kind of API to do that.

So lets create a PL/SQL package that manages some basic functionality to interact with our blockchain.

That’s what it should be able to do in the end:

  • calculate hashes
  • get a block
  • add a block
  • check blockchain health (valid or not)
  • get complete blockchain (starting with specified index)

And guess what? The Oracle Database has all features to do this! Thus we need cryptographic functionality (hashing) we will use DBMS_CRYPTO for this, so we will need a grant to our schema:

-- Grant to DBMS_CRYPTO as SYS
GRANT EXECUTE ON sys.dbms_crypto TO blockchain_schema;

If you´re not able or allowed to grant this package, then there´s an alternative open source PL/SQL utility for that: OOS Utils

Now we can start with the PL/SQL package, here are only some code snippets to get a understanding, the whole source code with an APEX demo app is available on GitHub: https://github.com/Dani3lSun/oracle-blockchain

Get a block (basically get an table row record)

FUNCTION get_block(p_bc_index IN blockchain.bc_index%TYPE)
  RETURN blockchain%ROWTYPE IS
  --
  l_blockchain_row blockchain%ROWTYPE;
  --
BEGIN
  --
  BEGIN
    SELECT *
      INTO l_blockchain_row
      FROM blockchain
     WHERE blockchain.bc_index = p_bc_index;
  EXCEPTION
    WHEN no_data_found THEN
      l_blockchain_row.bc_index         := 0;
      l_blockchain_row.bc_timestamp     := to_timestamp('01-01-1970 00:00:00',
                                                        'DD-MM-YYYY HH24:MI:SS');
      l_blockchain_row.bc_previous_hash := 0;
      l_blockchain_row.bc_hash          := 0;
  END;
  --
  RETURN l_blockchain_row;
  --
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END get_block;

Calculate a SHA256 hash of a block

FUNCTION calculate_hash(p_bc_index       IN blockchain.bc_index%TYPE,
                        p_bc_timestamp   IN blockchain.bc_timestamp%TYPE,
                        p_bc_data        IN blockchain.bc_data%TYPE,
                        p_hash_algorithm IN PLS_INTEGER := dbms_crypto.hash_sh256)
  RETURN VARCHAR2 IS
  --
  l_prev_blockchain_row blockchain%ROWTYPE;
  l_prev_hash           VARCHAR2(500);
  l_hash                VARCHAR2(500);
  l_hash_src            CLOB;
  --
BEGIN
  --
  l_prev_blockchain_row := blockchain_pkg.get_block(p_bc_index => blockchain_pkg.get_previous_block_index(p_current_index => p_bc_index));
  l_prev_hash           := l_prev_blockchain_row.bc_hash;
  --
  l_hash_src := p_bc_index ||
                to_char(p_bc_timestamp,
                        'DD.MM.RRRR HH24:MI:SSXFF TZH:TZM',
                        'nls_numeric_characters = ''. ''') || p_bc_data ||
                l_prev_hash;
  --
  l_hash := dbms_crypto.hash(l_hash_src,
                             p_hash_algorithm);
  --
  RETURN l_hash;
  --
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END calculate_hash;

Add a new block to our blockchain

FUNCTION add_block(p_bc_timestamp IN blockchain.bc_timestamp%TYPE := systimestamp,
                   p_bc_data      IN blockchain.bc_data%TYPE)
  RETURN blockchain.bc_index%TYPE IS
  --
  PRAGMA AUTONOMOUS_TRANSACTION;
  --
  l_prev_blockchain_row blockchain%ROWTYPE;
  l_prev_hash           VARCHAR2(500);
  l_hash                VARCHAR2(500);
  l_bc_index            blockchain.bc_index%TYPE;
  --
BEGIN
  --
  LOCK TABLE blockchain IN EXCLUSIVE MODE;
  --
  l_bc_index            := blockchain_seq.nextval;
  l_prev_blockchain_row := blockchain_pkg.get_latest_block;
  l_prev_hash           := l_prev_blockchain_row.bc_hash;
  l_hash                := blockchain_pkg.calculate_hash(p_bc_index     => l_bc_index,
                                                         p_bc_timestamp => p_bc_timestamp,
                                                         p_bc_data      => p_bc_data);
  --
  INSERT INTO blockchain
    (bc_index,
     bc_timestamp,
     bc_data,
     bc_previous_hash,
     bc_hash)
  VALUES
    (l_bc_index,
     p_bc_timestamp,
     p_bc_data,
     l_prev_hash,
     l_hash);
  --
  COMMIT;
  --
  RETURN l_bc_index;
  --
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END add_block;

Check health of blockchain (valid or not)

FUNCTION is_blockchain_valid RETURN BOOLEAN IS
  --
  l_current_block blockchain%ROWTYPE;
  l_prev_block    blockchain%ROWTYPE;
  l_current_hash  VARCHAR2(500);
  --
  CURSOR l_cur_blockchain IS
    SELECT blockchain.bc_index
      FROM blockchain
     ORDER BY blockchain.bc_index;
  --
BEGIN
  --
  FOR l_rec_blockchain IN l_cur_blockchain LOOP
    l_current_block := blockchain_pkg.get_block(p_bc_index => l_rec_blockchain.bc_index);
    l_prev_block    := blockchain_pkg.get_block(p_bc_index => blockchain_pkg.get_previous_block_index(p_current_index => l_rec_blockchain.bc_index));
    l_current_hash  := blockchain_pkg.get_block_hash(p_blockchain_block => l_current_block);
    --
    IF l_current_block.bc_hash != l_current_hash THEN
      RETURN FALSE;
    END IF;
    --
    IF l_current_block.bc_previous_hash != l_prev_block.bc_hash THEN
      RETURN FALSE;
    END IF;
    --    
  END LOOP;
  --
  RETURN TRUE;
  --
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END is_blockchain_valid;

But nothings better than see it in action, here´s a APEX live demo: https://apexcloud.de/ords/f?p=BC
As mentioned above, an export of this demo is available on GitHub

Hope this blog post is useful for some people, as I do think that this technology is very powerful and enables many use cases to save data in an secure and unmodifiable way, e.g transactional data or bookings etc…

Happy coding! 🙂

APEX Badges for your GitHub projects

As you might already have seen in this tweet:

You are now able to simply add an APEX badge to your APEX GitHub projects.
This is done by nothing more than adding some markdown to your Readme.md file or any other markdown file in your repository.

Example markdown:

[![APEX Community](https://cdn.rawgit.com/Dani3lSun/apex-github-badges/78c5adbe/badges/apex-community-badge.svg)](https://github.com/Dani3lSun/apex-github-badges)

You will find more badges and examples on the GitHub project page.

Contributions and ideas, maybe new badges are highly welcome!
Let´s show the open source world how great the APEX community is! 🙂

Awesome Oracle APEX List on GitHub

Maybe you have already seen my recent tweet about it:

So I´m calling now for community power to help extending that list with great articles, blogs, tips & resources.
Goal should be to make this awesome list even more awesome and a helpful guide / reference for everyone using Oracle APEX!

Here are some steps to contribute to this GitHub project:

  • Create a GitHub Account here

  • Fork the main GitHub repository. After that, you will find the forked repository under your GitHub user ( #github-user#/awesome-orclapex )

  • Edit the README.md file and add a APEX blog, link, article or resource. Here´s a contribution guide. You can do it either in the GitHub web interface or via git command line tools

  • Create a Pull Request to pull your changes to the main repository. I will review the changes and merge it into the main Readme file.

That´s it!
Thanks for your contributions, together we can make this even more awesome! 🙂

Ps: Maybe you´re interested in the complete GitHub awesome stuff, then you will find many awesome projects here.

Deploy APEX Docker Image on Oracle Container Cloud Service

As I really like Docker and use it pretty much in my daily life as a developer, it became a inherent part in my workflow.
I use it for my development instances or for having test servers pretty fast available. Even sharing the images with other devs or have some demo environment within minutes, Docker sets the bar pretty high.

Usually I use my own made Docker Image which contains the following software:

  • Oracle Linux 7.3
  • Oracle Database 12.2.0.1 Enterprise Edition with non-CDB architecture
  • Oracle APEX 5.1.2
  • Oracle ORDS 3.0.11
  • Oracle SQLcl 17.2.0.184.1230
  • Apache Tomcat 8.0.45
  • Java JDK 8u144
  • OraOpenSource Logger 3.1.1
  • OraOpenSource OOS Utils 1.0.0
  • APEX Office Print 3.x (Cloud Package)

The build scripts are available on GitHub and open source: Oracle Database & APEX Developer Docker Image

With this software shipped in one Container I have everything in place to get my work done. (Of course I know that this is usually not the way Docker works, normally you have only one software component in one Docker container. But for me it works quite well, getting all the software I need without having the overhead of a real VM)

Oracle Cloud Services

So in the past I only used Docker on my local machines or servers but I was interested if my image works in the Oracle Cloud too. So I decided to give it a try, and lets have a look what Oracle have in petto.

(Anticipation: After getting used to the Oracle Cloud UI, which took only an hour or two, I really liked how the Database Cloud Service and Container Cloud Service worked)

First step was to get into the Oracle Cloud Services, and for that purpose Oracle offers a free try period, which enables you to test it either for 30 days or until you consumed services worth $300. Here you get started: https://cloud.oracle.com/en_US/tryit

After my registration was successful, Oracle had my cloud account ready within 4 hours. (Which is OK, but of course it could be a bit faster if you have a look at other big cloud companies…). In the welcome mail, I got all the information I needed to get into the Oracle Cloud.

Thus we are only interested in the Container Cloud Service in this blog post, I will not write about the other possibilities within the Oracle Cloud. Having that said, I really liked the Database Service, too 🙂

Main focus is to get our own built Docker image running in the Oracle Container Cloud Service so we can use it and can connect to the services like APEX, Database or SSH.

Create an Oracle Container Cloud Service

This was really easy to accomplish, after login I navigated to the Container Service Section and created a new Service. The following screen shots will show the different steps and the create wizard.

About 30 minutes later I received an mail from Oracle, that my newly created Container Service is now up and running.

So I navigated to the detail page of my Container Service to get some information about it. The most important information is the Public IP address, this is the IP we use to connect to the admin panel of the Container Service:

https://<public-ip> e.g https://123.123.123.123

Oracle Container Cloud Service
Having this information we can login to our Oracle Container Cloud Service entering https + Public IP into our browser. After we have successfully authenticated we will land on a dashboard page of the service.

For now we are finished within the Oracle Cloud web interface, we successfully got an cloud account, created a Container Service and logged into that service. This procedure was indeed straight forward and not that complicated. It took me about 5-10 minutes to get this done. Really nice!
Next is preparing our Docker image so we can use it in the Oracle Container Cloud Service.

Build Docker Image

I am not going into much detail here, because the whole procedure creating a Docker image is described on GitHub. Just to see the steps:

  • Clone or download the GitHub repository to your local machine
    git clone https://github.com/Dani3lSun/docker-db-apex-dev.git
  • Download missing software components (like Oracle DB or Oracle APEX …)
  • Build the Docker image
    docker build -t db-apex-dev-image .

Upload Docker image to Docker Hub

Thus it is needed that the image is uploaded to some location our Oracle Container Cloud Service has access to, I used Docker Hub to get this done. But of course you can use every other or maybe your own Docker registry server. Important is that the Oracle Cloud server can access your registry server.

(What is a Docker registry server? Basically it is a server which stores and distributes Docker images, more details here)

The benefit of using Docker Hub is that we only have to create an account there and the Oracle Container Cloud Service has per default access to that registry server, which means we don´t have to configure anything.

After creating an account on Docker Hub we have to login on our client using the Docker command line tools

docker login

Entering username and password of the Docker Hub account and we are done.

Next, we upload our Docker image to Docker Hub using again the command line tools:

docker tag db-apex-dev-image <docker-hub-username>/db-apex-dev-image
docker push <docker-hub-username>/db-apex-dev-image

(More details about Docker push can be found here)

Once the upload process finished, we can go back to our Oracle Container Cloud Service admin panel. (This process can take some time, dependent on your networking bandwidth. The image has about 15-16GB)
Note: Make sure that this image is publicly available, this can be configured in the Docker Hub web interface. If you want to have it private than you have to configure your Docker credentials in the Oracle Container Cloud Service admin panel.

Create a new Service

Within the Oracle Container Cloud Service admin panel we can now create a new service, which basically is a Docker run command. So we define how our Docker image would be configured to run. (e.g port mappings, temp file size, …)

The most simple and fastest way is to use the Docker Run Command editor and paste in a docker run config like this:

docker run \
--name=db-apex-dev-container \
-p=2222:22/tcp \
-p=8080:8080/tcp \
-p=1521:1521/tcp \
-v=/dev/shm \
-e="occs:description=Oracle Database and APEX Developer Docker Service" \
"<docker-hub-username>/db-apex-dev-image" \
--tmpfs /dev/shm:rw,nosuid,nodev,exec,size=2g

This defines:

  • HTTP port mapping: 8080 tcp
  • DB port mapping: 1521 tcp
  • SSH port mapping: 2222 tcp
  • Volume: /dev/shm
  • Volume used as tmpfs with the size of 2GB

After we have done that, all the necessary input fields of the create window are automatically filled. Only the service name input have to be set by hand. Here are some screen shots which describe the procedure.

Finally we have our Container Service defined and now we will see it up and running. The only thing we have to do now is to press the green Deploy button.

Now Oracle fetches our Docker image from Docker Hub and runs it with our configured run parameters. This step takes a few minutes to complete.

Connect to Services

To get the public address of our running services we have to go to the hosts detail page, this IP address is used for all containers/services running in this particular Container Cloud Service. This means all network ports which are defined, can only be applied once.

Lets say our Public IP address is 123.123.123.124 then the services would be accessible like:
APEX:

http://123.123.123.124:8080/ords

DB:

sqlplus system/oracle@123.123.123.124/db12c

SSH:

ssh root@123.123.123.124 -p 2222

Note: These are the default port mappings as we defined in the docker run section above. The users and passwords are defined before building the docker image, read more about it on GitHub.
For security reasons the DB port should not be available publicly (maybe over SSH tunneling) and Tomcat should run over SSL

That´s it! 🙂 We are done and have a up and running Docker image in the Oracle Cloud packed with all the software components we like…