In our Oracle Forms application, we have several blocks where the Query Data Source Name is a view that queries from multiple tables, and the DML Data Target Name is one of the tables in that view. These blocks allow all DML (insert, update, delete), and it works fine.
We recently added a database policy on one of the tables in these views to restrict a small subset of records to a particular group of people until those records can be reviewed for personally identifiable information and redacted, if necessary. The policy works fine on all blocks directly based on that table, and in TOAD, SQL*Plus, etc.
However, those blocks based on a view that includes this table now give the user a "FRM-40602: Cannot insert into or update data in a view" error as soon as they try to add, modify, or delete an existing record. Note that the DML table for these blocks is NOT the table with the policy.
Through experimentation, we have discovered that it is not the policy function that is an issue, because I tried stripping it down to nothing but a RETURN statement returning the predicate as "1=1". The error persists until we disable the policy.
Note that this is happening in both 11g Forms running on an Oracle 11g database, and 12c Forms on a 12c database.
Any thoughts on what causes this, and if there's a way around it without entirely rebuilding those form blocks?
Thanks, Dan
A coworker eventually came up with the solution: for the block in question, change the "Key Mode" property from "Automatic" (the default value) to "Non-Updateable", and ensure that the "Primary Key" property for the table's primary key field is set to "Yes".