I spent some time helping an internal web developer yesterday with the AUTO REST feature for TABLES. He needed to be able to POST up new records to a table.
This TABLE was just fine, however the way it’s PRIMARY KEY was defined was causing some problems with the AUTO feature.
When you define a COLUMN using this ‘BY DEFAULT’ property, the database will ALWAYS generate a value for a new row.
The way AUTO REST works for POST on a TABLE, you need to submit a value for EVERY column.
This create a problem when…
It also creates a problem when…
Since I’m running my own ORDS, I can set it to DEBUG mode AND enable PRINT ERRORS TO SCREEN. You obviously want to be careful about this in a PROD environment. And since I know how AUTO works and what those error messages mean, I basically already know HOW to fix it. But let’s keep going.
So how do we ‘fix’ this?
Option One: Build your own POST.
Option Two: ALTER your TABLE
Here’s the original TABLE DDL, followed by the ALTER code I ran to ‘fix’ the table so it would work with the AUTO REST POST.
CREATE TABLE IDENTITY_TABLE ( ID INTEGER GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 START WITH 1 MINVALUE 1 CACHE 100 NOT NULL , WORDS VARCHAR2(256) , CONSTRAINT IDENTITY_TABLE_PK PRIMARY KEY ( ID ) ENABLE ); ALTER TABLE IDENTITY_TABLE MODIFY ( ID GENERATED BY DEFAULT ON NULL AS IDENTITY );