12 Replies Latest reply on Apr 19, 2019 9:16 AM by Frank Hoffmann-colognedata

    Trigger fires in wrong Module

    Frank Hoffmann-colognedata

      Hi,

       

      I am doing a migration from 10g to 12.2.1.3. and came to an issue which seems to me a bug. The bug is that triggers from inactiv modules are fired in 12.2.1.3 which should only fire in the active form.

       

      I have three forms x1, x2, x3.

       

      X1 calls via call_form X2 and then X2 calls via call_form X3. Every Form has a pre-form Trigger which does some logic.

      The strange thing is, that when X3 is called and displayed the "pre-form" Trigger from X2  fires instead of the pre-form Trigger in X3 and changes some parameters that do not exist in X3 which leads to an FRM error.

       

      Did anyone had a similar behavior and knows the reason for this? In 10g everything is running fine.

      Every hint is very welcome. I will try to make a test case on friday for this.

       

      Frank

        • 1. Re: Trigger fires in wrong Module
          Michael Ferrante-Oracle

          Frank,

           

          Do these modules have attached libraries (PLL)?  If so, can you simplify the test with three simple forms that have all their code self-contained (no attached libraries).

           

          If you have a simple test case, send it to me and I will review it.  However, if it requires db objects, it would be most helpful if based on SCOTT.  However, it sounds like what you are seeing likely isn't related to db activity.

           

          Mike

          • 2. Re: Trigger fires in wrong Module
            3394492

            If you have attached libraries, have you tried call_form(... , NO_SHARE_LIBRARY_DATA / SHARE_LIBRARY_DATA) settings ?

            Is there any difference ?

             

            /tenho

            1 person found this helpful
            • 3. Re: Trigger fires in wrong Module
              Frank Hoffmann-colognedata

              Tenho,

              thank you for this hint - I tried it out but it made no difference in my case.

              The migration I am doing has such a high complexity that it takes hours to run one time with debug through all the affected triggers of one module of the application.

               

              I try to break it down to a small testcase now and keep you up to date about the progress.

              Frank

              • 4. Re: Trigger fires in wrong Module
                Frank Hoffmann-colognedata

                Mike,

                 

                I was working yesterday 6 hours on customer site without success. I found a workaround duplicating the main library for every module with a different name - but not an acceptable one.We installed a patch according missing program units (6508) that might be relevant for that case too In the past.

                 

                So the problem is related to the libraries the customer uses. The logic duplicates all forms triggers in different libraries and depending which library is attached higher the code should fire or fire in the form a program unit with the same name. But it seems in level2 (call_form/call_form/call_form) the third form calls triggers that rise in the second form and bring up errors of missing objects that exist in the second form during startup.

                 

                The application came from Forms4 and was migrated all the years and now from 10g to 12c.

                I wonder because of this massive use of triggers if there are some major changes in 12c with the order of  the trigger that might cause a trigger to be started wrong synchronized

                 

                I try to build up a reproducable test case.

                Frank

                • 5. Re: Trigger fires in wrong Module
                  vansul

                  Call_form might be used without share library option and might be issue of overriding of the methods used in the library.

                  • 6. Re: Trigger fires in wrong Module
                    Frank Hoffmann-colognedata

                    Thank you for your help. Like tenho also mentioned the Option SHARE_LIBRARY_DATA but it does not change the behavior. Instead a correct call of the trigger in the third form the trigger fires in the second form.

                    Because the logic runs fine in 10g I guess it is a bug. Mike got a testcase and Oracle support too - so lets see if Oracle can reproduce this effect.

                     

                    Frank

                    • 7. Re: Trigger fires in wrong Module
                      Michael Ferrante-Oracle

                      Frank,

                       

                      I am still reviewing what you sent.  However, at first glance I'll start by saying this does not represent "best coding practices".  Having an application with Program Units that use the same names as those in a library is asking for trouble.  This may be contributing to the bad behavior.  The fact that it appeared to work in older versions does not make it right. 

                       

                      Also, I understand that your use of "root_window" in this example is the result of this form having been upgraded from a very old version.  However, I do not encourage the use of "root_window" as it is being retained only for limited backward compatibility.  It is essentially no longer supported.

                       

                      If I find a root cause, I will update you.

                      • 8. Re: Trigger fires in wrong Module
                        Michael Ferrante-Oracle

                        After some review what we are finding is that it isn't that the wrong "triggers" are firing.  It is the case that the PU in the library is being used when you are expecting the one in the form to be used.  Because both the form and library have PUs of the same name, it is difficult to follow the flow of the logic.  Using some messaging we were able to understand the flow better.  In short, where you expect the form PU to be used, the one from the library is being used.  This isn't entirely surprising. 

                         

                        So, what to do.  Well the right thing to do, at least based on the test case would be to rename the PUs in the form or library so they don't match each other.  Alternatively, you can do the following. That said, this is an oversimplification. More code will be needed to address the handling of arg values.  Likely globals would be the best way to handle those.

                         

                        In the forms (FMB), create a form level User Defined trigger (e.g. MYCUSTOMTRIGGER).  In that trigger make the call to PU LE_WHENMOUSEDOUBLECLICK.  This will result in the form using the PU in the form and not the library.  If the form does not include the LE_WHENMOUSEDOUBLECLICK PU, we will drop to the library to find the PU.

                         

                          -- Global variables likely will be needed to handle any arguments that need to be passed.
                            DECLARE
                                a  BOOLEAN;
                            BEGIN
                                a := LE_WhenMOUSEDoubleclick();
                            END;
                        

                         

                         

                        In the library, change LEY_EVENT from:

                         

                              IF( p_Event = 'WHEN-MOUSE-DOUBLECLICK' )THEN
                                  v_Ret :=  LE_WhenMOUSEDoubleclick( v_Block, v_Item ) ;
                              END if;
                        

                         

                            TO THIS:

                         

                            -- Global variables likely will be needed to handle any arguments that need to be passed.
                              IF( p_Event = 'WHEN-MOUSE-DOUBLECLICK' )THEN
                                  v_Ret :=  TRUE ;
                                  EXECUTE_TRIGGER ('MYCUSTOMTRIGGER');
                              END if;
                        
                        1 person found this helpful
                        • 9. Re: Trigger fires in wrong Module
                          db12345

                          There is an undocumented parameter about the program unit naming resolution that may have been set on your 10g environment in the .env file for when you have the same name used across the forms and libraries.

                           

                          FORMS_PLSQLV1_NAME_RESOLUTION=TRUE

                           

                          I encountered the same issue when going from Forms 11 to 12 and found it previously enabled. Note this might require the Forms Server to be restarted to pickup the change.

                           

                          Daniel

                          1 person found this helpful
                          • 10. Re: Trigger fires in wrong Module
                            Frank Hoffmann-colognedata

                            Daniel,

                             

                            all the BUGS are gone. No Triggers are fired in the old modules, no synchronization problems, no other errors. Call Forms work fine in every call .
                            Now 12c works as good as 10g. It is exaclty as you described. The 10g .env File had this setting already. It seems still to work under Forms 12.2.1.3. with this settings.
                            You saved my day!!! 100 Points to you and a beer and a sausage in Cologne if you come and visit me!

                             

                            Frank

                            • 11. Re: Trigger fires in wrong Module
                              Michael Ferrante-Oracle

                              Frank,

                               

                              Setting that variable may have corrected the behavior, but as I mentioned this is not good coding practice and should be avoided.  Development has confirmed that this variable was/is intended only for upgrade cases so that backward compatibility can be retained while processing through the upgrade.  In fact, the change in behavior was originally identified in early 1998 (in bug 612052) where again developers explained that this variable should only be used temporarily.  Here is the explanation from development (from 1998).

                               

                              ... we have supplied an environment variable, FORMSxx_PLSQLV1_NAME_RESOLUTION. 

                              When this environment variable is set, it will provide the same behavior as

                              in Forms 4.5.  Please note, however, there are a number of disadvantages to

                              setting this environment variable.  This is because the program unit

                              instantiations will not be shared across forms.  This means that package

                              state will not be capable of being shared across forms -

                              i.e. SHARE_LIBRARY_STATE will have no effect.  It also means that there

                              will be a decrease in performance and increase in memory usage as compared

                              to the default behavior of Forms 5.0 and above.

                               

                              In Forms 7.0, when Forms encounters a call to a program unit, it first

                              looks for the program unit in the current module, then searches the

                              attached libraries in the order of attachment.  This is different from

                              Forms 6.0 in that, if the program unit is in a library, it will search

                              the library first and not the form.  This will make name resolution the

                              same as PL/SQL on the database server, and the same as how the PL/SQL is

                              compiled.  However, to get the Forms 4.5 behavior, setting

                              FORMSxx_PLSQLV1_NAME_RESOLUTION will still be supported.

                               

                              For the next release after Forms 7.0, we will continue to have the Forms

                              7.0 behavior, but may not support the FORMSxx_PLSQLV1_NAME_RESOLUTION.

                               

                              As using FORMSxx_PLSQLV1_NAME_RESOLUTION has significant disadvantages

                              and will not always be supported, the recommended way of  accomplishing

                              dynamic name resolution is to use the execute_trigger built-in.  If

                              arguments are necessary, global variables can be used.

                              1 person found this helpful
                              • 12. Re: Trigger fires in wrong Module
                                Frank Hoffmann-colognedata

                                Mike,

                                 

                                thanks a again for your valued additional informations that brings light into this personal 10g/12c migration jungle experience.

                                 

                                I agree to you - this application needs a complete rewrite to be full compatible with 12.2.1.3. But this will be very expensive and would also need a very expensive complex testing. Like many Forms applications today there is very little documentation and "forms debug" is your only friend to understand the architecture. You would not believe what the developers 25 years ago did with good old Forms.

                                I have spended almost two days just debugging the code and I can tell you it is very complex. The problem does not only exist for one trigger (like in my testcase) but for every trigger Forms has to offer. This parameter "FORMSxx_PLSQLV1_NAME_RESOLUTION" mentioned by Daniel was already set in version 10g to keep the application alive like is was designed with 4.5.

                                 

                                This software uses buttons as tab canvases (in Forms 4.5? or earlier? tab canvases where done by buttons) and was migrated like this all the way up to 12.2.1.3. - only very little code change since the last 25 years - mostly to move it from C/S to 3tier.

                                 

                                For migration processes it might make sense to first set this parameter and also keep the runtime compatibilty with 4.5. to get it run bug free. I can set "runtime compatibilty 4.5" with API-Master but maybe there is another hidden Parameter for runtime compatibilty (4.5) since you can not change this with the current builder any more.

                                 

                                I hope for this customer that you keep this  parameter option alive with 12.2.1.4 so the customer gets more time to decide how to proceed with his code. Backwards compatibility is one of the highest values of Forms.

                                 

                                Frank