Porting flat file into Oracle database

Porting blockchain data into Oracle database.

Example of data 

0x01000000010000000000000000000000000000000000000000030A000000000000 : 0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0

0x01000000010000000000000000000000000000000000000000100A000000000000 : 0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0

0x010000000100000000000000000000000000000000000000001203000000000000 : 0x00000000000000000000000000000000000000000000000735BEF46E4566A000

0x010000000100000000000000000000000000000000000000001604000000000000 : 0x00000000000000000000000000000000000000000000000796E3F357D922A000

0x010000000100000000000000000000000000000000000000001605000000000000 : 0x00000000000000000000000000000000000000000000000796E3F357D922A7D0

0x010000000100000000000000000000000000000000000000001705000000000000 : 0x00000000000000000000000000000000000000000000000796E3F357D922A7D0

0x010000000100000000000000000000000000000000000000001B01000000000000 : 0x000000000000000000000000000000000000000000000006F05B59D3B2000000

0x010000000100000000000000000000000000000000000000002A03000000000000 : 0x000000000000000000000000000000000000000000000007439FAB21ECCAA000

Step 1: Create tablespace, datafile and user

CREATE SMALLFILE TABLESPACE "BLOCKCHAIN" DATAFILE '/data/blockchain.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER "BLOCKCHAIN" PROFILE "DEFAULT" IDENTIFIED BY "oracle" DEFAULT TABLESPACE "BLOCKCHAIN" ACCOUNT UNLOCK;

grant connect to blockchain;

grant resource to blockchain;

grant create table to blockchain;

grant unlimited tablespace to blockchain;

Step 2: ETL (Extract, Transform, Load) process setup on Oracle12C database. Note: Oracle11G and below works different. 

SQL> create directory user_dir as '/home/oracle/Downloads';

Directory created.

SQL> grant read, write on directory user_dir to blockchain;

Grant succeeded.

SQL> grant read, write on directory user_dir to blockchain;

Grant succeeded.

SQL> conn blockchain/oracle

Connected.

The flat file resides here and making sure it chmod 777 on Downloads directory. Oracle has tendency to fail without providing any error. 

 [oracle@centosoracle12 Downloads]$ pwd ; ls -las data_output.txt

/home/oracle/Downloads

1748 -rwxrwxrwx. 1 oracle oracle 1787548 Mar 26 16:59 data_output.txt

Creating the a table to store blockchain data. For simplicity, I am using varchar2(200) and table have absolutely no constraints just as proof of concept. I do not know if the data will be larger, if it does, we may need to use BLOB (Large Binary Object) data type which will have adverse impact to the data as it grows. Once the following completed, it would created a table with data.

create table block_load(akey varchar2(200), bvalue varchar2(200))

organization external

(default directory user_dir

access parameters

(fields terminated by ':')

location('data_output.txt')

);

Table created.

The table above it created based on external data link. I would create a real physical table out of it.

SQL> alter table block_load reject limit unlimited;

Table altered.

SQL> create table real_block_load as select* from block_load;

Table created.

Formatting a report

SQL> set lines 130;

SQL> col bvalue format a50;

SQL> col akey format a50;

Sample of the data in real block load table

SQL> select * from real_block_load where rownum < 5;

AKEY

--------------------------------------------------------------------------------

BVALUE

--------------------------------------------------------------------------------

0x01000000010000000000000000000000000000000000000000030A000000000000

 0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0

0x01000000010000000000000000000000000000000000000000100A000000000000

 0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0

0x010000000100000000000000000000000000000000000000001203000000000000

 0x00000000000000000000000000000000000000000000000735BEF46E4566A000

Number of total data in real block load table.

SQL> select count(*) from real_block_load;

  COUNT(*)

----------

      6960