DBMS_BLOCKCHAIN_TABLE package in Oracle 21c

You can use DBMS_BLOCKCHAIN_TABLE package for maintenance of blockchain table. Using this package you can perform the below tasks.

• Sing a row that is added into the table
• Verify the integrity of rows and hash column.
• Generate signature and signed digest
• Delete rows in a blockchain table that are beyond retention defined.

Here are the few subprograms (procedures and functions) used with DBMS_BLOCKCHAIN_TABLE package

• DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS By using this procedure you can verify the integrity of rows and hash column. You can also verify the Signatures as well

DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS(
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
low_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
high_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
instance_id IN NUMBER DEFAULT NULL,
chain_id IN NUMBER DEFAULT NULL,
number_of_rows_verified OUT NUMBER,
verify_signature IN BOOLEAN DEFAULT TRUE);

For ex:-

DECLARE

Rows_Verify NUMBER;
DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS(
schema_name => 'BCUSER',
table_name => 'BLOCKCHAIN_T1',
number_of_rows_verified => Rows_Verify);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(Rows_Verify) || ' rows deleted');
END;
/

• DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST – By using this procedure you can generate a signature and signed digest.

DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST(
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
signed_bytes IN OUT BLOB,
signed_rows_indexes OUT ORABCTAB_ROW_ARRAY_T,
schema_certificate_guid OUT RAW,
signature_algo IN NUMBER default SIGN_ALGO_DEFAULT)
RETURN RAW;

• DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN - By using this procedure you can verify the integrity of rows created between specified time period X1 and X2

DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN(

signed_bytes_latest IN BLOB,

signed_bytes_previous IN BLOB,

number_of_rows_verified OUT NUMBER);

• DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS - By using this procedure you can delete rows that are beyond retention period

For ex:-

DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS(

schema_name IN VARCHAR2,

table_name IN VARCHAR2,

before_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,

number_of_rows_deleted OUT NUMBER);

Ex:- The below example deletes the rows beyond the retention period, you can limit the number of rows deletion by using before_timestamp

DECLARE

Del_Rows NUMBER;

DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS(

schema_name => 'BCUSER',

table_name => 'BLOCKCHAIN_T1',

before_timestamp => NULL,

number_of_rows_deleted => Del_Rows);

DBMS_OUTPUT.PUT_LINE(TO_CHAR(Del_Rows) || ' rows deleted');

END;

/

• DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE - By using this procedure you can determine the data format for row content to compute the signature.

DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE(

schema_name IN VARCHAR2,

table_name IN VARCHAR2,

instance_id IN NUMBER,

chain_id IN NUMBER,

sequence_id IN NUMBER,

data_format IN NUMBER,

row_data IN OUT BLOB);

• DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_HASH - By using this procedure you can determine the data format for row content to compute hash value

DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_HASH(

schema_name IN VARCHAR2,

table_name IN VARCHAR2,

instance_id IN NUMBER,

chain_id IN NUMBER,

sequence_id IN NUMBER,

data_format IN NUMBER,

row_data IN OUT BLOB);

• DBMS_BLOCKCHAIN_TABLE.SIGN_ROW – By using this procedure you can add a signature to an existing row.

DBMS_BLOCKCHAIN_TABLE.SIGN_ROW(

schema_name IN VARCHAR2,

table_name IN VARCHAR2,

instance_id IN NUMBER,

chain_id IN NUMBER,

sequence_id IN NUMBER,

hash IN RAW DEFAULT NULL,

signature IN RAW,

certificate_guid IN RAW,

signature_algo IN NUMBER);

• DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_RSA_SHA2_512 - – By using this procedure you can validate a signature using specific digital certificate and signature algorithm. Note that Blockchain tables support only DER encoding or X.509 certificates

• Note that DBMS_CRYPTO.HASH function can be used to compute hash value

Blockchain Data byte values:

By using DBMS_TABLE_DATEA package you can get the byte values of row, column

• DBMS_TABLE_DATA.GET_BYTES_FOR_COLUMN - By using this procedure column data in bytes
• DBMS_TABLE_DATA.GET_BYTES_FOR_COLUMNS By using this procedure column data in bytes for set of columns
• DBMS_TABLE_DATA.GET_BYTES_FOR_ROW By using this procedure row data in bytes.

Thanks & Regards
https://oracleracexpert.com, Oracle ACE