ORA-32796: cannot update a generated always identity column

Question

I am converting some code from vb.net 2008 to vb.net 2019. I got some warnings in my code that the Oracle reference I was using would be outdated in future versions so I switched to a Oracle.ManagedDataAccess reference.

It seems I can read data but unfortunately when I try to write data, I am running into issues I didn't have before. When I try to update an existing record in my Oracle database, I am getting this error:

If I change my query to only include the fields I want to update then I get this error:

Dynamic SQL generation failed. No key information found

The second error makes sense but not the first. It seems like there's something about my ID column the new code doesn't like. I'm not a db expert or anything but I am not sure how to fix this.

score 1 · Answer 1 · answered Nov 08 '21 at 16:23

1

Looks like table's ID column was created as

id NUMBER GENERATED     ALWAYS AS IDENTITY
                        ------

If it was

id NUMBER GENERATED BY DEFAULT AS IDENTITY
                    ----------

then you, I presume, wouldn't get that error.

Anyway: it would help if you posted CREATE TABLE as well as DML you preformed against that table.

answered Nov 08 '21 at 16:23

Littlefoot

131,892
15
35
57

Why I am not getting that error in the other version of my code? – Jason Shoulders Nov 08 '21 at 16:27
I really wouldn't know. – Littlefoot Nov 08 '21 at 16:29
Here's a link to the table layout: https://i.imgur.com/KpwyqjL.png The ID field is just a number that gets created with a trigger. I don't see the Always as Identity you referred to. – Jason Shoulders Nov 08 '21 at 16:47
1

Please **do not post links** to tables structure or code. Post both as text. We cannot answer questions about table structure or code without seeing the structure or code. – Belayer Nov 08 '21 at 18:19

score 0 · Answer 2 · answered Aug 24 '22 at 05:32

The solution is the same as one of my previous answer

If you have generated/created the table with scripts like this:

ID NUMBER GENERATED ALWAYS AS IDENTITY

Then it will not allow you to update or insert the value of the ID column.

if you have generated like this, you could insert or update

ID NUMBER GENERATED BY DEFAULT AS IDENTITY

However, by altering the table you can achieve this

ALTER TABLE [TableName] MODIFY ID GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE);

and after updating or inserting you can go back to the previous state by running the following script

ALTER TABLE [TableName] MODIFY ID GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE);

ORA-32796: cannot update a generated always identity column

2 Answers2