0

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*/ )
Improve this question
0

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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. Commented Dec 8, 2021 at 18:00

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.