I've 445 points remaining -- all available to who ever supplies the best answer :o)
The environment: forms 45 on Oracle 8i.
The schemas:
User X inserts into A.TABLE (user A grants all on TABLE to X).
User A grants select on A.TABLE to B.
User B creates materialized view on A.TABLE called B.MVIEW.
User C references B.MVIEW.
The commit failure: user A did not grant to C (users B and C are legacy schemas and incrementally will migrate to new schema A). Committing transactions to A.TABLE will fail "Table or view does not exist" -- which is fine (to fix this, A must grant to C).
The Forms bug: what I notice is that a simple Forms 45 client with a based block on A.TABLE does not handle the commit failure in this instance. Misleadingly, the Forms client will return commit successful when actually the whole transaction fails (test by exiting form-- form prompts "do you want to save changes" etc.)
Possible Red Herring: it is as if Forms client will only test the transaction with respect to the based object, and not test the status of any other schema that indirectly references the updated object. In the example, the insert into A.TABLE is fine and consistent, however, C (which references indirectly via B's materialized view) will be inconsistent and although the RDBMS can trap the violation, the error appears not to pass back to the Forms client.
The question: has anyone come across this before? I'm probably not explaining myself well because I don't know the "visibility" of the Forms client and the error protocol between the RDBMS and client. On the server side, I correctly get this:
SQL> begin
2 insert into A.fund( fund_id, long_name, cta_id, portfolio_multiple, geared_allocation )
3 values( 'aaa', 'aaa', 'aaa', 1, 0 );
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> commit;
commit
*
ERROR at line 1:
ORA-00942: table or view does not exist
Which is understandable because there's a third user, called C who now references indirectly A.fund and connecting as A, granting to C will fix this.
BUT -- when attempting to insert into fund via a Forms client -- you 'suddenly' don't get the error being handled. On the contrary, the Forms client will report success yet closing the client shows that the transaction failed and there are uncommited changes present. Worse, PRE and POST commit triggers fire, form / block_status works consistently, form_success, form_fatal, et. al. all work consistently -- so the error trapping becomes increasingly limiting.
