📋Steps in this guide1/13

Blockchain Table Enhancements in Oracle Database 23ai/26ai

Blockchain tables were first introduced in Oracle 21c, and backported to Oracle 19c. This article demonstrates the enhancements to blockchain tables in Oracle 23ai/26ai.

oracle 23configurationintermediate

by OracleDba

31 views

Create a Blockchain Table

In addition to adding the keyword to the command, there are three blockchain clauses. The clause determines how long the table is protected from being dropped. If the table has no rows it can still be dropped. Unlike the initial releases of blockchain tables, in (19.11 and 21.3) the clause also prevents the table being dropped via a command. - : The table can't be dropped. Be careful about using this setting during testing. - : The table can't dropped until there have been no new rows inserted for the specified number of days. You may prefer to use 0 or 1 as the number of days during testing this functionality. The clause determines the retention period. How long each row will be protected from deletion. - : Each row is retained forever. The absence of the keyword implies the setting can be changed with the command, but it can't. Retention periods can only be increased. - : Same as . - : Each row is protected from deletion for the specified number of days, but this setting can be increased using the command. Minimum 16 days. - : Each row is protected from deletion for the specified number of days, and this setting can't be changed using the command. Minimum 16 days. In the initial release the blockchain hash and data format clause was fixed. In Oracle 23ai/26ai we have the option of using the original "V1" version, or the new "V2" version, which supports additional functionality. The following example creates two tables. One of each version. Checking the view shows us several invisible columns have been added to our column list. The hidden columns are described here . Notice the "V2" table has twice the number of hidden columns compared to the "V1" table. The views display information about blockchain tables. It's a view over the table.

Code/Command (click line numbers to comment):

NO DROP [ UNTIL number DAYS IDLE ]

NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }

HASHING USING sha2_512 VERSION v2

drop table if exists bct_t1 purge;

create blockchain table bct_t1 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_t1_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v1";


drop table if exists bct_t2 purge;

create blockchain table bct_t2 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_t2_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v2";

set linesize 120 pagesize 50
column column_name format a45
column data_type format a27
column hidden_column format a13

select internal_column_id,
       column_name,
       data_type,
       data_length,
       hidden_column
from   user_tab_cols       
where  table_name = 'BCT_T1'
order by internal_column_id;

INTERNAL_COLUMN_ID COLUMN_NAME                                   DATA_TYPE                   DATA_LENGTH HIDDEN_COLUMN
------------------ --------------------------------------------- --------------------------- ----------- -------------
                 1 ID                                            NUMBER                               22 NO
                 2 FRUIT                                         VARCHAR2                             20 NO
                 3 QUANTITY                                      NUMBER                               22 NO
                 4 CREATED_DATE                                  DATE                                  7 NO
                 5 ORABCTAB_INST_ID$                             NUMBER                               22 YES
                 6 ORABCTAB_CHAIN_ID$                            NUMBER                               22 YES
                 7 ORABCTAB_SEQ_NUM$                             NUMBER                               22 YES
                 8 ORABCTAB_CREATION_TIME$                       TIMESTAMP(6) WITH TIME ZONE          13 YES
                 9 ORABCTAB_USER_NUMBER$                         NUMBER                               22 YES
                10 ORABCTAB_HASH$                                RAW                                2000 YES
                11 ORABCTAB_SIGNATURE$                           RAW                                2000 YES
                12 ORABCTAB_SIGNATURE_ALG$                       NUMBER                               22 YES
                13 ORABCTAB_SIGNATURE_CERT$                      RAW                                  16 YES
                14 ORABCTAB_SPARE$                               RAW                                2000 YES

14 rows selected.

SQL>


select internal_column_id,
       column_name,
       data_type,
       data_length,
       hidden_column
from   user_tab_cols       
where  table_name = 'BCT_T2'
order by internal_column_id;

INTERNAL_COLUMN_ID COLUMN_NAME                                   DATA_TYPE                   DATA_LENGTH HIDDEN_COLUMN
------------------ --------------------------------------------- --------------------------- ----------- -------------
                 1 ID                                            NUMBER                               22 NO
                 2 FRUIT                                         VARCHAR2                             20 NO
                 3 QUANTITY                                      NUMBER                               22 NO
                 4 CREATED_DATE                                  DATE                                  7 NO
                 5 ORABCTAB_INST_ID$                             NUMBER                               22 YES
                 6 ORABCTAB_CHAIN_ID$                            NUMBER                               22 YES
                 7 ORABCTAB_SEQ_NUM$                             NUMBER                               22 YES
                 8 ORABCTAB_CREATION_TIME$                       TIMESTAMP(6) WITH TIME ZONE          13 YES
                 9 ORABCTAB_USER_NUMBER$                         NUMBER                               22 YES
                10 ORABCTAB_HASH$                                RAW                                2000 YES
                11 ORABCTAB_SIGNATURE$                           RAW                                2000 YES
                12 ORABCTAB_SIGNATURE_ALG$                       NUMBER                               22 YES
                13 ORABCTAB_SIGNATURE_CERT$                      RAW                                1000 YES
                14 ORABCTAB_SPARE$                               RAW                                2000 YES
                15 ORABCTAB_PDB_GUID$                            RAW                                2000 YES
                16 ORABCTAB_ROW_VERSION$                         NUMBER                               22 YES
                17 ORABCTAB_LAST_ROW_VERSION_NUMBER$             RAW                                   1 YES
                18 ORABCTAB_USER_CHAIN_HASH$                     RAW                                2000 YES
                19 ORABCTAB_DELEGATE_SIGNATURE$                  RAW                                2000 YES
                20 ORABCTAB_DELEGATE_SIGNATURE_ALG$              NUMBER                               22 YES
                21 ORABCTAB_DELEGATE_SIGNATURE_CERT$             RAW                                1000 YES
                22 ORABCTAB_DELEGATE_USER_NUMBER$                NUMBER                               22 YES
                23 ORABCTAB_COUNTERSIGNATURE$                    RAW                                2000 YES
                24 ORABCTAB_COUNTERSIGNATURE_ALG$                NUMBER                               22 YES
                25 ORABCTAB_COUNTERSIGNATURE_CERT$               RAW                                1000 YES
                26 ORABCTAB_COUNTERSIGNATURE_ROW_FORMAT_VERSION$ VARCHAR2                           4000 YES
                27 ORABCTAB_COUNTERSIGNATURE_ROW_FORMAT_FLAG$    NUMBER                               22 YES
                28 ORABCTAB_TS$                                  TIMESTAMP(6)                         13 YES

28 rows selected.

SQL>

column table_name format a10
column row_retention format 9999999999999
column row_retention_locked format a20
column table_inactivity_retention format 9999999999999999999999999
column hash_algorithm format a14
column table_version format a14

select table_name,
       row_retention,
       row_retention_locked, 
       table_inactivity_retention,
       hash_algorithm,
       table_version  
from   user_blockchain_tables 
where  table_name like 'BCT_T%'
order by 1;

TABLE_NAME  ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION HASH_ALGORITHM TABLE_VERSION
---------- -------------- -------------------- -------------------------- -------------- --------------
BCT_T1                 16 NO                                            0 SHA2_512       V1
BCT_T2                 16 NO                                            0 SHA2_512       V2

SQL>

Alter a Blockchain Table

The following operations perform the same on "V1" and "V2" blockchain tables. The clause can be altered using the command, as long as the retention period is not reduced. Be careful not to set it too large when testing. Regardless of the current drop delay setting, an attempt to switch to the maximum value of causes an error. This is because of the parameter discussed below. Assuming it was not defined as locked, the clause can be modified using the command, as long as the retention period is not reduced. We currently have a row retention period of 16 days. In the example below we increase that value to 32. When we subsequently attempt to lower the value to 16 it gives an error. We can set the row retention to , which means rows will be kept forever. Remember, we can't reduce this value once is it set, so take care using such a long retention period. We'll drop and recreate the "V1" blockchain table. We've not inserted any rows into it yet, so despite the retention times the following command works.

Code/Command (click line numbers to comment):

alter table bct_t1 no drop until 1 days idle;

alter table bct_t1 no drop;
*
ERROR at line 1:
ORA-05807: Blockchain or immutable table "TESTUSER1"."BCT_T1" cannot have idle retention greater than 16 days.

SQL>

-- Increase to 32 days.
alter table bct_t1 no delete until 32 days after insert;

Table BCT_T1 altered.

SQL>


-- Decrease to 16 days (fail).
alter table bct_t1 no delete until 16 days after insert;

Error report -
ORA-05732: retention value cannot be lowered

SQL>

alter table bct_t1 no delete;

drop table if exists bct_t1 purge;

create blockchain table bct_t1 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_t1_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v1";

Blocked DML and DDL Operations

As you would expect for an insert-only table, all DML and DDL operations that would result in row data being amended or deleted are prevented for a blockchain table. The following example shows a successful insert, then some unsuccessful DML statements. Some DDL statements that could alter the contents of the data are also prevented. Here is an example of the statement. Extending existing columns is fine.

Code/Command (click line numbers to comment):

-- INSERT
insert into bct_t2 (id, fruit, quantity, created_date) values (1, 'apple', 20, sysdate);

1 row inserted.

SQL> commit;

Commit complete.

SQL>


-- UPDATE
update bct_t2 set quantity = 10 where id = 1;
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>


-- DELETE
delete from bct_t2 where id = 1;
            *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>

truncate table bct_t2;
               *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>

alter table bct_t1 modify (fruit varchar2(25));

Table altered.

SQL>

Add/Drop Columns

In a "V1" blockchain table adding new columns or dropping existing columns is not allowed. In a "V2" blockchain table we can add new columns and drop existing columns. The dropped columns are marked as hidden, rather than actually being dropped.

Code/Command (click line numbers to comment):

-- Add column
alter table bct_t1 add (additional_info varchar2(50));
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>


-- Drop column.
alter table bct_t1 drop column quantity;
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>

-- Add column
alter table bct_t2 add (additional_info varchar2(50));

Table altered.

SQL>


-- Drop column.
alter table bct_t2 drop column additional_info;

Table altered.

SQL>

User Chains

In the previous releases blockchain tables only supported up to 32 system generated chains per instance, with rows being assigned to chains at random. In Oracle 23ai/26ai we can create user chains using up to three columns to define the chains. Each unique combination of values represents a separate chain. In the following example we use the clause, resulting in a separate chain for each unique value of the column. The view allows us to display information about user chains associated with a table. A chain is not created until rows are inserted. We insert some data and check the view.

Code/Command (click line numbers to comment):

drop table if exists bct_uc purge;

create blockchain table bct_uc (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_uc_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512"
with user chain fruit_chain (fruit)
version "v2";

insert into bct_uc (id, fruit, quantity, created_date)
values (1, 'apple', 20, sysdate),
       (2, 'orange', 10, sysdate),
       (3, 'apple', 30, sysdate),
       (4, 'orange', 35, sysdate);
commit;


select table_name, chain_id
from   user_blockchain_table_chains;

TABLE_NAME             CHAIN_ID
-------------------- ----------
BCT_UC                        9

SQL>

Row Versions

We can't update rows in a blockchain table, so we have to make changes by inserting new rows. Blockchain table row versions allow us to track the sequence of inserts for related rows over time, so we can check the latest version of a specific row. This is done by adding the clause to the blockchain table, specifying a list of up to three columns that are used to determine if rows are related. In the example below we use the clause based on the column. We insert some data. We can see all the entries in the blockchain table. When we create a blockchain table with a user chain, we automatically get a view created with " " appended to the table name. When we query this view we see only the latest row version for each unique value in the chain.

Code/Command (click line numbers to comment):

drop table if exists bct_rv purge;

create blockchain table bct_rv (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_rv_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512"
with row version and user chain fruit_chain (fruit)
version "v2";

insert into bct_rv (id, fruit, quantity, created_date)
values (1, 'apple', 20, sysdate),
       (2, 'orange', 10, sysdate),
       (3, 'apple', 30, sysdate),
       (4, 'orange', 35, sysdate);
commit;

select * from bct_rv;

        ID FRUIT                  QUANTITY CREATED_D
---------- -------------------- ---------- ---------
         1 apple                        20 07-JUL-23
         2 orange                       10 07-JUL-23
         3 apple                        30 07-JUL-23
         4 orange                       35 07-JUL-23

SQL>

select * from bct_rv_last$;

        ID FRUIT                  QUANTITY CREATED_D
---------- -------------------- ---------- ---------
         3 apple                        30 07-JUL-23
         4 orange                       35 07-JUL-23

SQL>

Control Long Idle Retention Times

In Oracle 23ai/26ai the maximum idle retention time for the table is controlled by the parameter. This means we can't set the clause to a value longer than 16 days. We can either increase this setting, or grant the user the privilege, which allows the user to ignore this limit. Now we can ignore the limit.

Code/Command (click line numbers to comment):

show parameter blockchain_table_retention_threshold

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
blockchain_table_retention_threshold integer     16
SQL>

drop table if exists bct_t3 purge;

create blockchain table bct_t3 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_t3_pk primary key (id)
)
no drop until 32 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v2";
*
ERROR at line 1:
ORA-05807: Blockchain or immutable table "TESTUSER1"."BCT_T3" cannot have idle retention greater than 16 days.

SQL>

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

grant table retention to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

drop table if exists bct_t3 purge;

create blockchain table bct_t3 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_t3_pk primary key (id)
)
no drop until 32 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v2";

Table created.

SQL>

DBMS_BLOCKCHAIN_TABLE Package

The package is used for maintenance of blockchain tables. The procedure removes any rows that are beyond the retention period. They can't be removed using a normal statement. Alternatively, we can limit the deletion by date. The rows will only be deleted if they are outside the retention period, and match the date criteria. The procedure checks the rows in the table have a consistent hash, and signature if used. In Oracle 23ai/26ai we can verify rows for a specific chain.

Code/Command (click line numbers to comment):

set serveroutput on
declare
  l_rows  number;
begin
  dbms_blockchain_table.delete_expired_rows(
    schema_name            => 'testuser1',
    table_name             => 'bct_t1',
    before_timestamp       => null,
    number_of_rows_deleted => l_rows);

  dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0

PL/SQL procedure successfully completed.

SQL>

set serveroutput on
declare
  l_rows  number;
begin
  dbms_blockchain_table.delete_expired_rows(
    schema_name            => 'testuser1',
    table_name             => 'bct_t1',
    before_timestamp       => systimestamp - 60,
    number_of_rows_deleted => l_rows);

  dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0

PL/SQL procedure successfully completed.

SQL>

set serveroutput on
declare
  l_rows      number;
  l_verified  number;
begin
  select count(*)
  into   l_rows
  from   admin.bct_t1;

  dbms_blockchain_table.verify_rows(
    schema_name             => 'testuser1',
    table_name              => 'bct_t1',
    number_of_rows_verified => l_verified);

  dbms_output.put_line('Rows=' || l_rows || '  Verified Rows=' || l_verified);
end;
/
Rows=1  Verified Rows=1

PL/SQL procedure successfully completed.

SQL>

Flashback Data Archives (FDA) using Blockchain Tables

In Oracle 23ai/26ai we can create flashback data archives (FDA) as block blockchain tables by adding the keyword to the clause. This gives additional assurance the flashback data archive has not been tampered with. First we set up a new test user. We create a new tablespace and a flashback data archive using the tablespace. We apply some additional grants to the test user. We can now connect to the test user and create a table with a blockchain flashback data archive. Notice the addition of the keyword.

Code/Command (click line numbers to comment):

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser2 cascade;

create user testuser2 identified by testuser2;
grant db_developer_role to testuser2;

create tablespace fda_ts datafile size 1m autoextend on next 1m;
alter user testuser2 quota unlimited on fda_ts;

create flashback archive default fda_1year tablespace fda_ts
  quota 10g retention 1 year;

grant flashback archive on fda_1year to testuser2;
grant flashback archive administer to testuser2;
grant execute on dbms_flashback_archive to testuser2;
grant create any context to testuser2;

conn testuser2/testuser2@//localhost:1521/freepdb1

drop table if exists t1 purge;

create table t1 (
  id           number,
  description  varchar2(50),
  constraint t1_pk primary key (id)
)
blockchain flashback archive fda_1year;

Manage Certificates

The package allows us to manage certificates for use with row signing. We generated a new self-signed certificate using the following command on a Linux server. We create an oracle directory object pointing to the location holding the certificate. We use the contents of the "my-bct-test-cert.der" file with the procedure to create a new certificate in the database. The certificate is be visible in the views. The procedure removes certificates from the database. The column from the views identifies the certificate to be removed. Don't run this is you want to continue to sign rows.

Code/Command (click line numbers to comment):

mkdir /home/oracle/my_wallet

openssl req \
  -newkey rsa:2048 -nodes -sha512 \
  -x509 -days 3650 \
  -outform der \
  -keyout /home/oracle/my_wallet/my-bct-test-key.der \
  -out /home/oracle/my_wallet/my-bct-test-cert.der \
  -subj "/C=GB/ST=West Midlands/L=Birmingham/O=Example Company/OU=Devs/CN=Tim Hall/[email protected]"

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create or replace directory cert_dir as '/home/oracle/my_wallet/';
grant read, write on directory cert_dir to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

set serveroutput on
declare
  l_dir            varchar2(20) := 'CERT_DIR';
  l_file_name      varchar2(20) := 'my-bct-test-cert.der';

  l_cert           blob;
  l_bfile          bfile;
  l_destoffset     integer := 1;
  l_srcoffset      integer := 1;
  l_cert_id        raw(16);
begin
  dbms_lob.createtemporary(l_cert, false);
  l_bfile := bfilename(l_dir, l_file_name);

  if (dbms_lob.fileexists( l_bfile ) = 1) then
    dbms_lob.fileopen( l_bfile );
    dbms_lob.loadblobfromfile(
      dest_lob    => l_cert,
      src_bfile   => l_bfile,
      amount      => dbms_lob.getlength(l_bfile),
      dest_offset => l_destoffset,
      src_offset  => l_srcoffset
    );
    dbms_lob.fileclose( l_bfile );

    dbms_user_certs.add_certificate(l_cert, l_cert_id);
    dbms_output.put_line('certificate ID: ' || l_cert_id);
  else
    raise_application_error(-20001, 'must create the user certificates first');
  end if;
end;
/
certificate ID: 006EA851A3FE2E89E065000000000001

PL/SQL procedure successfully completed.

SQL>

column certificate_id format a35
column user_name format a10
column distinguished_name format a30

select certificate_id,
       user_name,
       distinguished_name
from   user_certificates;

CERTIFICATE_ID                      USER_NAME  DISTINGUISHED_NAME
----------------------------------- ---------- ------------------------------
006EA851A3FE2E89E065000000000001    TESTUSER1  [email protected],CN=Tim Ha
                                               ll,OU=Devs,O=Example Company,L
                                               =Birmingham,ST=West Midlands,C
                                               =GB

SQL>

begin
  dbms_user_certs.drop_certificate(cert_id => '006EA851A3FE2E89E065000000000001');
end;
/

Sign Rows

Once a certificate is loaded into the database, we can use it to sign rows. We create a test blockchain table and insert a row. We query some of the hidden column values for the row we want to sign, and pass them to the procedure to identify the data that is necessary to sign the row. We then write this data out to a file. We sign the file using the private key for our certificate, and create the "/home/oracle/my_wallet/id_1_signature.dat.sha512" file. The resulting file is our signature. We can now use the signature to sign the row. The following example gets the information about the row, loads the signature into a blob, and uses it to sign the rows using the procedure in the package. We can see the row has been signed using the following query. Thanks to Martin Bach and Ulrike Schwinn for helping with the solution for signing rows.

Code/Command (click line numbers to comment):

drop table if exists bct_sign purge;

create blockchain table bct_sign (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_sign_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v2";

insert into bct_sign (id, fruit, quantity, created_date) values (1, 'apple', 20, sysdate);
commit;

declare
  l_dir         varchar2(20) := 'CERT_DIR';
  l_file_name   varchar2(20) := 'id_1_signature.dat';

  l_row_data    blob;
  l_buffer      raw(4000);
  l_inst_id     binary_integer;
  l_chain_id    binary_integer;
  l_seq_num     binary_integer;
  l_row_len     binary_integer;
  l_file        utl_file.file_type; 
begin
  select orabctab_inst_id$, orabctab_chain_id$, orabctab_seq_num$
  into   l_inst_id, l_chain_id, l_seq_num
  from   bct_sign 
  where  id = 1;
  
  dbms_blockchain_table.get_bytes_for_row_signature(
    schema_name => 'testuser1',
    table_name  => 'bct_sign',
    instance_id => l_inst_id,
    chain_id    => l_chain_id,
    sequence_id => l_seq_num,
    data_format => 1,
    row_data    => l_row_data);
    
  l_row_len := dbms_lob.getlength(l_row_data);
  dbms_lob.read(l_row_data, l_row_len, 1, l_buffer);
  l_file := utl_file.fopen(l_dir,l_file_name,'wb', 32767);
  utl_file.put_raw(l_file, l_buffer, true);
  utl_file.fclose(l_file);
end;
/

cd /home/oracle/my_wallet/

openssl dgst -sha512 \
        -sign my-bct-test-key.der \
        -out id_1_signature.dat.sha512 \
        id_1_signature.dat

declare
  l_dir          varchar2(30) := 'CERT_DIR';
  l_file_name    varchar2(30) := 'id_1_signature.dat.sha512';
  l_cert_id      raw (16) := hextoraw('006EA851A3FE2E89E065000000000001');

  l_inst_id      binary_integer;
  l_chain_id     binary_integer;
  l_sequence_no  binary_integer;
  l_hash         raw(2000);
  l_signature    blob;
  l_bfile        bfile;
  l_destoffset     integer := 1;
  l_srcoffset      integer := 1;
begin
  select orabctab_inst_id$, orabctab_chain_id$, orabctab_seq_num$, orabctab_hash$, orabctab_signature$
  into   l_inst_id, l_chain_id, l_sequence_no, l_hash, l_signature
  from   bct_sign
  where  id = 1;

  if l_signature is not null then
    raise_application_error(-20000, 'The row has already been signed.');
  end if;

  l_bfile := bfilename(l_dir, l_file_name);
  if (dbms_lob.fileexists(l_bfile ) = 1) then
    dbms_lob.createtemporary(l_signature, false);
    dbms_lob.fileopen(l_bfile);
    dbms_lob.loadblobfromfile(
      dest_lob    => l_signature,
      src_bfile   => l_bfile,
      amount      => dbms_lob.getlength(l_bfile),
      dest_offset => l_destoffset,
      src_offset  => l_srcoffset
    );
    dbms_lob.fileclose(l_bfile);

    dbms_blockchain_table.sign_row(
      schema_name      => 'testuser1',
      table_name       => 'bct_sign',
      instance_id      => l_inst_id,
      chain_id         => l_chain_id,
      sequence_id      => l_sequence_no,
      hash             => l_hash,
      signature        => l_signature,
      certificate_guid => l_cert_id,
      signature_algo   => dbms_blockchain_table.sign_algo_rsa_sha2_512);
  else
    raise_application_error(-20001, 'must create the signature first');
  end if;
end;
/

set linesize 120
column signature format a30
column cert_id format a35

select id,
       fruit,
       orabctab_signature$ as signature,
       orabctab_signature_cert$ as cert_id
from   bct_sign
where  id = 1;

        ID FRUIT                SIGNATURE                      CERT_ID
---------- -------------------- ------------------------------ -----------------------------------
         1 apple                240FB4F558B14DD035DBDD5E71012E 006EA851A3FE2E89E065000000000001
                                AB28775A47C7CC2720D6660B4F9F1B
                                DFE8F944FCD55AD7BE67400A82B0A8
                                C00281F405CD53401D85DF6272AE3E
                                1294A02B6901B6693515348482F7D5
                                5F861860FBF66ACB6DFB7C977DC87C
                                3F2C1A9C1ABFE79F30DD6F7AAA155F
                                4F4DCD3B37F6BDD4AEDED784B18E3A
                                0EAB72B23F53403

SQL>

Countersignature and Delegate Signer

Oracle 23ai/26ai includes the ability to request a countersignature at the time of signing a row. It also allows a delegate signer, who can sign the rows on behalf of the primary user. Here are the links to the documentation on these features. - Countersigning Blockchain Table Rows - Allowing a Delegate to Sign Blockchain Table Rows

Considerations

There are a number of things to consider when using blockchain tables. - Many of the issues associated with blockchain tables in the previous releases have been resolved in Oracle 23ai/26ai. - Blockchain tables are slower than conventional tables, due to the extra work associated with them. - Blockchain tables can be indexed and partitioned in the normal manner. - There are some restrictions associated with data pump against blockchain tables, described here . - There are a number of general restrictions associated with blockchain tables, described here . - Oracle recommend saving the current hash and the corresponding sequence number for each chain in the instance somewhere outside of the database. This allows you to compare your recorded values to those in the table for extra assurance. - In data guard environments, Oracle recommend maximum protection mode or maximum availability mode when working with blockchain tables. - User certificates can be added to the database using the procedure in the package, and applied to existing rows using the procedure in the package. I guess the main question should be, why would you use a blockchain table? - If you need an insert-only tamper proof table in your application generally, this could be the solution. - If you want to take advantage of the trust associated with blockchain in a centralized manner, rather than having multiple client applications having to manage blockchains individually, using a blockchain table allows you to centralise that trust. - You can add the trust associated with blockchain to existing applications without having to worry about recoding them. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!