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! 🙂

6 thoughts on “Blockchain with Oracle and APEX

  1. Hi Daniel,
    wenn das unser verehrter SF sehen müßte…:-)
    .. FUNCTION add_block(
    Das sollte als eine Proc implementiert werden, oder?
    Ich meine das insbesondere wegen der Vorbildwirkung.
    Viele Grüße
    Andre

    Like

    1. Hi Andre,
      Ich denke das so allgemein zu sagen ist schwer…
      Meiner Meinung nach sind Function und Procedure hier zulässig…Da ich nicht weis was Andere mit der API machen, habe ich mich für ne Function entschieden, da dort der neue Index (PK) zurück gegeben wird…Vielleicht benötigt das jemand für eine Weiterverarbeitung.
      VG
      Daniel

      Like

      1. Hi Daniel,
        …”Da ich nicht weis was Andere mit der API machen”…
        Genau das ist der Punkt – Stichwort Seiteneffekt in Functions.
        So würde ich das machen…
        a) als Proc mit out-Param (…Index (PK)…)
        b) als autonome Transaktion

        Auf jeden Fall Danke für die Idee und das Teilen!
        VG
        Andre

        Liked by 1 person

      2. Hi Andre,
        ich würde trotzdem sagen das ist Geschmacksache…Ich zB verwende bei nur einem Return Wert gerne Functions.
        Nichtsdestotrotz gebe ich dir bei der autonomen Transaktion recht, durch den expliziten Commit würde es sich sonst auf andere Programmteile auswirken. Ich habe es hier in der Beschreibung und auf GitHub ergänzt, danke für den Hinweis!
        https://github.com/Dani3lSun/oracle-blockchain/commit/9106676c90472384a835e8d196c4200d4c0be30f
        Ich habe auch eine zusätzliche add_block Procedure dazu gepackt, nun kann jeder verwenden was ihm besser liegt! 🙂

        VG
        Daniel

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s