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(
val NUMBER, 
hash_val VARCHAR2(1000), 
clob_val CLOB);

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

  • Take a look at the Oracle documentation on virtual columns; and primary keys. It looks like you might be able to create a primary key on a virtual column. See "Notes" docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/… "Restrictions" docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/… Dec 9, 2021 at 21:34
  • Note that DBMS_CRYPTO.HASH returns a RAW datatype, not VARCHAR2. You need to store it as RAW: casting or converting to VARCHAR2 will invalidate the hash result.
    – pmdba
    Dec 10, 2021 at 1:49
  • @pmdba thanks for responding. I'm not seeing a problem. Can you provide a simple test CASE to prove your point.
    – Pugzly
    Dec 12, 2021 at 15:09
  • RAW output is binary data. Storing it as VARCHAR2 applies a character set conversion that is not valid for binary data. What you see after the varchar2 conversion will be missing content, even if it looks valid.
    – pmdba
    Dec 12, 2021 at 23:18

1 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.

  • thanks for responding. It's not the CLOB I want to change it's the hash_val that will change, which is based on the CLOB.
    – Pugzly
    Dec 8, 2021 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.