REST API inserting into a table with 'GENERATED ALWAYS' column

Question

I'm using Oracle's Autonomous Database service, with ORDS providing the REST functionality.

When making updates to a table (docs here), when I have an identity column id that is GENERATED ALWAYS, it seems the POST request even when not supplying a id value in the request body, gets parsed by the REST service as id: null.

This then gives me Error Message: ORA-32795: cannot insert into a generated always identity column ORA-06512: at line 4.

Using a SQL statement to insert into the table without specifying the id column works as expected.

Is there a way to keep the identity column always generated (so the ID of a new row cannot be specified), while allowing for POST updates?

Answer 1

Auto Rest functionality will always generated all columns, so there is no other solution rather than

• Develop your own POST method and omit on it the IDENTITY column
• Change the IDENTITY TYPE, for example from GENERATED ALWAYS to GENERATED BY DEFAULT ON NULL, thereby Oracle will create a value when you set it to null.

I would go for the second.

ALTER TABLE IDENTITY_TABLE MODIFY ( ID GENERATED BY DEFAULT ON NULL AS IDENTITY );

You have a great post from Jeff Smith explaining this situation

AUTO POST and IDENTITY COLUMNS