insert into derived column with in same table

Question

i created this test table db and added 3 columns col,col2 and cal3 . I want the cal 3 value to be derived from col + col2 . Doesn't let be to insert data using insert satament. what is the sql statement to insert values.

The table created statement is as follows :-

create table db
 (
 col number(10),col2 number(20), cal3 number(10) generated always as (col+col2)
 );

Answer 1

Since you have a computed column you have to specify the columns you insert into:

insert into db (col, col2) values (10, 20);

a select * from db after the insert above would give you:

| COL | COL2 | CAL3 |
|-----|------|------|
|  10 |   20 |   30 |

Answer 2

Virtual Column values are always generated dynamically with the logic defined.

The INSERT statement should be having values for the columns used for CAL3. Otherwise, the value will be NULL for CAL3.

Scenario 1: (Value for COL field only)

INSERT INTO DB (COL) VALUES(100);

Output of the table DB will be as follows.

SELECT * FROM DB; 

COL | COL2 | CAL3
------------------
100  (null) (null)

Scenario 2: (Value for both COL and COL2 fields)

INSERT INTO DB (COL,COL2) VALUES(100,200);

Output of the table DB will be as follows.

SELECT * FROM DB; 

COL | COL2 | CAL3
------------------
100   200    300