I have stumbled upon what appears to be a new bug in Forms 12c. I cannot find any reference in the Forms 12c New Features document discussing commit processing. I don't have the Forms 12 Builder installed, but in the Online Help of the Forms 11 Builder (Index, Commit_Form Built-in, "Restrictions") it states:
"If you use a PL/SQL COMMIT statement in an anonymous block or a form-level procedure, Oracle Forms interprets that statement as a call to the COMMIT_FORM Built-in."
Unfortunately, this is no longer true. The pl/sql Commit now works like Forms_DDL('commit') in 12c.
Nearly all of our hundreds of forms use the Commit_Form statement, but one was giving users trouble, popping up the message, "Do you want to save the changes you have made" when they were transferring to another form via the New_Form built-in. It has taken many hours to track down the source of the problem, but I finally managed to zero-in on the Commit; statement it was issuing.
The difficulty is that following every Commit or Commit_Form, we use the recommended check for success:
If not form_success or :System.form_Status <> 'QUERY' then
Unfortunately, no message was coded, because in all cases, some other trigger would issue an error message, so all that was needed was to terminate the commit process-flow. But in THIS case, Commit; just leaves Form_Status = "CHANGED", so the offending form stopped processing, without ever issuing a message that a problem occurred. Users noted a problem when they got to the "Do you want to save..." message.
Here are the symptoms I've found:
1. Change a base-table block, status becomes "CHANGED". Try a Commit; and nothing is updated to the database, status remains CHANGED.
2. After making a change, issue a POST;. Status becomes "QUERY"
2a. Issue a Commit_Form, and all is good.
2b. Instead of 2a, issue a Commit, the posted changes are committed in the database. Status remains QUERY.
2c. After 2b, try a New_Form, Forms issues the "Do you want to save..." question, even though all changes are committed.
3a. After making a change, the first Post works. Subsequent Post commands without new changes get FRM-40405: No changes to apply.
3b. After making a change, the first Commit_Form works. Subsequent ones get FRM-40401: No changes to save.
3c. 3a and 3b are correct behavior. But issuing multiple Commit statements always return Form_Success, with NO error messages.
I'll be opening a Service Request with Oracle for this bug.
Edit: Thank you, Zlatko Sirotic.
We have set: export FORMS_PLSQL_BHVR_COMMON_SQL=1
in our linux compiler script due to receiving this compiler error in another form:
PL/SQL ERROR 801 at line 0, column 0
internal error [Unexpected fragile external reference.]
So with that compiler parameter set, it disables the documented processing for Commit; and Rollback; statements.
For all who might consider setting FORMS_PLSQL_BHVR_COMMON_SQL = 1, (It has serious negative effects), please read this:
Message was edited by: Steve Cosner