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:
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">, ...
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 )
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:
-- 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);
-- Create sequence create sequence BLOCKCHAIN_SEQ minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 nocache;
-- 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 let's 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:
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.
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;
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;
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;
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…
Blogpost by Daniel Hochleitner
Originally published on https://blog.danielhochleitner.de/2017/10/10/blockchain-with-oracle-and-...