I have an interactive grid that was originally created as a report.
The SELECT statement selects from a main table, is joined to 2 other tables, and also selects data from many other tables via PL/SQL functions.
The users would now like the ability to update 2 columns in the main table.
I tried accomplishing this by making the Grid Editable with UPDATE only allowed and changing all columns except the 2 specified as Read Only Always, for each row.
This doesn't work, of course. I get the error: ORA-01779: cannot modify a column which maps to a non key-preserved table.
I am not looking for anyone to debug my code. Rather, I am hoping for advice on a general direction to try, before I head too far down a stoopid path.
A couple of thoughts I had:
Remove the calls to the PL/SQL functions from the SELECT and replace them with columns that have a Source of SQL Expression, with the call to the function in the SQL Expression field attribute instead of the SELECT. I was thinking that if I did that, it might convince APEX that it didn't need to try to update columns outside of the main table. It seems like I have retrieved data this way before (via the SQL Expression attribute) and it is slower than placing the call in the SELECT. I'm not sure if that is true or not...
Change the Interactive Grid to an Interactive Report with an associated form. I like the capabilities of the Grid better, however.
Remove the Editable attribute from the Grid, add an Edit icon to the grid with a link that displays a form based on the main table only, with the ability to update those 2 fields there.
If anyone has any comments or suggestions, I would greatly appreciate it.