3 Replies Latest reply on Jun 28, 2018 5:50 PM by Steve Cosner

    Forms 12c bug:  Commit_Form vs Commit pl/sql statement

    Steve Cosner

      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

        Raise Form_Trigger_Failure;

      End if;

      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.

       

      If anyone cares, you can download form COMMIT_12C.fmb (88k) or Commit_12c.zip (17k) and try out the issue yourself.

       

      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.]

      Writeup posted here

       

      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:

      Re: Forms 11gR2 PL/SQL?

       

      Message was edited by: Steve Cosner

        • 1. Re: Forms 12c bug:  Commit_Form vs Commit pl/sql statement
          Zlatko Sirotic

          What value does the (new) Forms parameter FORMS_PLSQL_BHVR_COMMON_SQL have?

          https://docs.oracle.com/middleware/1221/formsandreports/deploy-forms/configure.htm#FSDEP164

           

          I heard about this parameter for the first time in this thread:

          Forms 11gR2 PL/SQL?

           

          Michael said this:

          Set FORMS_PLSQL_BHVR_COMMON_SQL = 1 in environment (Builder and Runtime). You may find it necessary to replace "COMMIT"s with COMMIT_FORM and "ROLLBACK"s with CLEAR_FORM.

           

          Regards,

          Zlatko

          • 2. Re: Forms 12c bug:  Commit_Form vs Commit pl/sql statement
            Felipe Zorzo

            Hi Steve.

             

            I tried to reproduce your issues, but I had no sucess.

             

            "1. Change a base-table block, status becomes "CHANGED".  Try a Commit; and nothing is updated to the database, status remains CHANGED."

            commit.gif

            "2c.  After 2b, try a New_Form, Forms issues the "Do you want to save..." question, even though all changes are committed."

            post_and_commit.gif

            "3c.  3a and 3b are correct behavior.  But issuing multiple Commit statements always return Form_Success, with NO error messages."

            multiple_commit.gif

            1 person found this helpful
            • 3. Re: Forms 12c bug:  Commit_Form vs Commit pl/sql statement
              Steve Cosner

              Thank you Zlatko and Felipe!  Apparently I am tangled up between two problems -- one solved by setting the parameter

                 FORMS_PLSQL_BHVR_COMMON_SQL=1

              ...and one caused by setting it.

               

              In both forms, I could correct the problem by coding a workaround:

              For the Commit, change it to Commit_Form

              For the   other one, I had a package specification within the form, and a cursor defined within the package spec.  The error was caused when the Package.Cursor%rowtype was referenced in several procedures in the form.  I could have abandoned the package spec cursor and duplicated the cursor in each procedure.

               

              Oracle's response to my Service Request is that this problem is the same as:

              Bug 282256299 : 12C : COMMIT DOESN'T WORK BUT COMMIT_FORM WORKS

               

              What really gnaws at me is this:   Setting FORMS_PLSQL_BHVR_COMMON_SQL=1 is like putting your foot in a trap.  It fixes a compiler problem that was never a problem before (the SQL cursor in a package spec worked in earlier versions).  Yet it breaks code that has always worked before:   Commit = Commit_Form.

              I wish someone could explain this.