CODEWITHSUNDEEP
databaseoracle-databasevb.net
Jason Shoulders
Jason Shoulders

Reputation: 659

ORA-32796: cannot update a generated always identity column

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:

ORA-32796: cannot update a generated always identity column

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.

Upvotes: 0

Views: 2577

Answers (2)

mijaved
mijaved

Reputation: 791

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);

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142958

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.

Upvotes: 1

Related Questions