Oracle Apex: How to update a tabular form of a table with Primary Key set as Identity Always?

Question

I am struggling with this specific problem for hours. Hopefully someone can help me on this.

As the question described above, I have a tabular form which is built from a table, which has a primary key ('ID') set as "Identity Always". This became a problem for the tabular form, because whenever I made changes to the data and click on 'Save', Apex will display this error: ORA-32796: cannot update a generated always identity column

I tried several workaround such as ROWID, but this solution would not work because I need to join the tabular form with fields from other tables to display additional info - hence the ROWID would not be preserved and could not be used as primary key.

I also read in an Apex documentation that:

Utilizing the IDENTITY clause on a table can have an impact on Application Express applications. An error will be raised by the database if an IDENTITY column, with ALWAYS parameter specified, is included in an INSERT or UPDATE statement. The Application Express Builder has been enhanced to allow the specification of the identity clause within the Create Table Wizard. The Create Form wizard will generate a "Display Only" item for an identity column with the ALWAYS parameter.

Which I do not understand. Apex indeed generated an extra column ('ID_Display'), but it does not change the fact that my tabular form still throw the same error (ORA-32796 above). How is this "Display Only" item can save me from the "cannot update a generated always identity column"?

As a side note, I am developing in apex.oraclecorp.com, Apex as a service, where I cannot access the files or made SQL alteration to my table. Please let me know if you encountered this problem before and found a solution - thanks in advance!

P.S. the Apex version is 5.0.4.

EDIT:

Sorry, I just realized there is the SQL Command UI to perform SQL operation in Apex. Anyone knows how to ALTER the identity of a table from ALWAYS to DEFAULT?

Answer 1

After experimenting with this considerably it appears that the only option, if you want to use the Identity clause to generate a sequential numeric primary key, is to use
BY DEFAULT ON NULL AS IDENTITY
as Apex will attempt to insert a null value into the ID Field when doing automatic row processing. Neither of the 2 other options to work correctly with Apex in this circumstance.
The other options are:
1. Create a sequence and a trigger, which does not present any problems with Apex
2. Use a guid for the primary key

Answer 2

ALTER TABLE t MODIFY c GENERATED BY DEFAULT as IDENTITY; 

Answer 3

The following solved the ORA-32796 insert problem for me with Editable Interactive Grid. I suppose the same works for tabular forms.

1. Goto Page Designer for the page in question.
2. Click on "Processing", or click on "Component View" and then click on the process under "Page Processing". For Editable Interactive Grid, it is the process "Save interactive Grid Data".
3. Click on "Yes" in the "Return Primary Key(s) after insert" section in properties.
4. Click save/run.

Answer 4

So I'm sure this is more of a workaround than an actual understanding of the issue and solution, but in the wizard, I found that:

when you are in 'Form Attributes' in the wizard setting as follows:

Primarky Key Type: Select Primark Key Column(s) Primary Key Column: 'Your column' Primary Key Column: (I didn't have anything to enter here) Primary Key Source Type: 'Existing Trigger' (This seemed to allow the Identity column do it's thing on the table/form I was working on.). I had been running into the same issue with I would change this to 'Existing Sequence'.

If you ever figured this out, I would love to know the details.

More reading is required, but it's something...