ORA-32795: cannot insert into a generated always identity column

Question

Guys I am trying to execute below insert statement and I keep getting the error:

cannot insert into a generated always identity column

the statement is :

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)

Extraversion

Answer 1

What don't you 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 a good idea to list all the columns in an INSERT anyway. This prevents unexpected errors, because the columns are in the wrong order or the tables have different numbers of columns.

Gonad

Answer 2

One of the columns in your target table (leaves_approval) contains an identity column that was defined as Generated always.
Identity columns can be created in 2 modes - Generated always, that cannot be assigned and Generated by default that can be assigned.

---

If you wish you can change the column mode and then do your insert "as is".
Take in consideration that this might create duplicates in the identity column or failed due to constraints.

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

Or you can exclude the identity column from the INSERT list (but you'll have to indicate the full column list, except for the identity column), e.g. -

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)

---

Database SQL Language Reference - CREATE TABLE

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

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

Lagging

I believe this is the correct answer. in some cases we do want explicit IDs good to know distinction between DEFAULT and ALWAYS – Hanes

Yap, I prefer David's answer as well. When you are debugging, sometimes it helps to know the workaround solution. :) – Radford

Answer 3

Example: my_table_column NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY - if you have the column defined as, then it will get the value when it is NULL and will not interfere if you are to insert/update with values for that column. It worked for me.

Ruthenious