ORA-32795: cannot be inserted into generated identity column

Question

Guys, I'm trying to execute the insert statement below, and I keep getting the error:

cannot be inserted into always generated identity column

statement:

INSERT INTO leaves_approval SELECT * FROM requests_temp r WHERE r.civil_number = 33322 AND r.request_id = (SELECT Max(s.request_id) FROM requests_temp s) 

Answer 1

What do you not understand about the error? You have an identity column where the value is generated as a sequence. You cannot insert into it. So list all the other columns:

 INSERT INTO LEAVES_APPROVAL(col1, col2, col3, . . .) SELECT col1, col2, col3, . . . FROM REQUESTS_TEMP r WHERE r.CIVIL_NUMBER = 33322 AND r.REQUEST_ID = (SELECT MAX(s.REQUEST_ID) FROM REQUESTS_TEMP s); 

In general, it is recommended that you list all columns in INSERT anyway. This prevents unforeseen errors because the columns are in the wrong order or the tables have different numbers of columns.

Answer 2

One of the columns in the target table (leaf_approval) contains an identifier column that was defined as Always Generated .
Identity columns can be created in two modes - Always generated , < can be assigned.

---

If you want, you can change the column mode, and then make your insert as-is.
Keep in mind that this may create duplicates in the identity column or failed due to limitations.

 ALTER TABLE leaves_approval MODIFY **my_identity_column** GENERATED BY DEFAULT AS IDENTITY; 

Or you can exclude the identifier column from the INSERT list (but you need to specify the full list of columns, except for the identity column), for example. -

 INSERT INTO leaves_approval (c1,c2,c3,c4,...) SELECT c1,c2,c3,c4 ... FROM requests_temp r WHERE r.civil_number = 33322 AND r.request_id = (SELECT Max(s.request_id) FROM requests_temp s) 

---

SQL Database Reference - CREATE TABLE

ALWAYS If you specify ALWAYS, then the Oracle database always uses to assign a value to a column. If you try to explicitly assign a value to a column using INSERT or UPDATE, then an error will be returned. This is the default value.

DEFAULT If you specify BY DEFAULT, then Oracle Database uses the sequence generator to assign a value to the default column, but you can also explicitly assign the specified value to the column. if you specify ON NULL, then Oracle Database uses a sequence generator to assign a value to the column when the subsequent INSERT statement tries to assign a value that is NULL.