Oracle generating hash_val from a clob

Question

I have the following table, where I would like the value of hash_val to be generated from clob_val whether clob_val is inserted or updated.

Can this be done perhaps by making the column hash_val a virtual column? Or does this have to be done via a function and trigger.

I want to use something like the CRYPTO call below to generate the value for hash_val. In addition, I would like hash_val to be the primary KEY

Any examples setting up the virtual column if possible would be greatly appreciated. Thanks to all who answer.

CREATE table table_z(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
val NUMBER, 
hash_val VARCHAR2(1000), 
clob_val CLOB);

DBMS_CRYPTO.HASH (clob_val, 6                          /*DBMS_CRYPTO.HASH_SH512*/                                              )

Answer 1

There is no syntax for CREATE TABLE that will allow you to automatically update a value of a column due to a DML operation.

If the function is deterministic, you might be able to define that column as a Virtual Column instead. (I don't know if VC supports LOBs).

Alternatively, you'll need to do the update in a Trigger. (Again, I'm concerned with limitations due to LOBs).

If all else fails, you'll need to restrict DML operations to a Table API (TAPI); the collection of procedures are stored in a Package.