7 Replies Latest reply on Jan 12, 2012 2:17 AM by Scott Wesley

    Funny side effect using : to reference an application item in a page proc

    Luis Cabral
      Hi there,

      I am using Apex 4 and I have a strange situation here...

      The scenario:

      I have an on submit after computations page application process with this code:
      some_package.pr_remove_carts  
        (p_carts        => apex_application.g_f01
        ,p_last_cart_id => :F_USER_LAST_CART_ID
        ,p_acc_nbr      => :F_ACC_NBR
        ,p_username     => :APP_USER
        ,p_debug        => :F_DEBUG);
      That procedure changes the application item F_USER_LAST_CART_ID using apex_util.set_session_state to a different value.

      The problem:

      Although the value of F_USER_LAST_CART_ID is changed by the procedure, Apex by itself changes it back to its original value after the procedure call! I see this in the debug:
      ...Session State: Saved Item "F_USER_LAST_CART_ID" New Value="254"  <-- this is when I set the item to the new value
      ...
      Procedure call
      ...
      ...Session State: Saved Item "F_USER_LAST_CART_ID" New Value="456" <-- "something" is setting the item to its old value
      After a lot of testing and debugging, I found that using v() instead of : solves the issue.

      NOTE: No, p_last_cart_id is not an OUT parameter, it is a normal IN parameter.

      I hope this information helps someone out there with the same problem.

      And, just out of interest, is this a bug or something?

      Cheers,
      Luis
        • 1. Re: Funny side effect using : to reference an application item in a page proc
          Luis Cabral
          As I had no replies for this one, this is just an update on what I think is the reason behind this behavior.

          When Apex executes a PL/SQL block with bind variables, it cannot tell if the bind variables are going to be used as IN or OUT variables and it assumes they will be used as OUT variables.

          This means that the bind variables value may change and the corresponding items need to be updated with whatever value they happen to have after the call.

          If a page item is passed to some code using bind variable syntax and the session item value of that page item is changed by the code, when the code returns Apex "thinks" that it was the bind variable value that has changed (because the value in the session is now different from the value in the bind variable) and updates the session value with the old value!

          I consider this a bug, because Apex should be able to identify if a bind variable value changed without relying on the session state, for instance using an intermediate buffer to store the values of the bind variables before the call and use those temporary values to check if they changed.

          If the v() syntax is used, this problem obviously does not happen.
          • 2. Re: Funny side effect using : to reference an application item in a page proc
            partlycloudy
            That procedure changes the application item F_USER_LAST_CART_ID using apex_util.set_session_state to a different value
            It is not a bug, just a side-effect of how APEX executes anonymous PL/SQL blocks containing bind variables especially when the PL/SQL block calls a procedure that modifies session state using the set_session_state API.

            I recall having a in-depth discussion with Scott Spadafore about this exact topic many years back but I think the concepts still hold good.

            See Re: Session state not set until process completes?
            • 3. Re: Funny side effect using : to reference an application item in a page proc
              Luis Cabral
              Hi, thanks for the link, very interesting discussion.

              However I am not totally convinced that this is not a bug, or at least something that could be improved...

              In pseudo-code, this is how I would expect it to work:
              declare
                v_stmt = 'begin :1 := proc; end;';
                v_bind_variable_value := v('P1_X');
                v_bind_variable_value_orig := v_bind_variable_value;
              begin
                execute immediate v_stmt using v_bind_variable_value;
              
                if v_bind_variable_value != v_bind_variable_value_orig then
                  the value was changed by the dynamic call, 
                         so update_session_state('P1_X', v_bind_variable_value);
                else
                  the value was not changed by the dynamic call, 
                         so leave session state as it is even if it is different from the original value
                end if;
              end;
              • 4. Re: Funny side effect using : to reference an application item in a page proc
                Joel_C
                In defense of the APEX developers, I think the problem lies in your mixing two paradigms together. On the one hand, you are passing page/app items values as parameters; on the other hand, you are updating the session state "under the covers", so to speak, with the direct call.

                The problem would resolve itself if you chose on or the other method - either passing values via parameters in AND out of the procedure and setting the value in the page process OR referencing the page item directly via session state calls. Think about it: if you already know what application item you are updating in advance WITHIN the procedure, why do you need to pass the value as a parameter in the first place?
                • 5. Re: Funny side effect using : to reference an application item in a page proc
                  Luis Cabral
                  Hi Joel

                  I am a big Apex fan and I meant this as a constructive criticism, to help improve the tool.
                  The problem would resolve itself if you chose on or the other method - either passing values via parameters in AND out of the procedure and setting the value in the page process OR referencing the page item directly via session state calls. Think about it: if you already know what application item you are updating in advance WITHIN the procedure, why do you need to pass the value as a parameter in the first place?
                  Let's assume that the procedure I am calling is used in many places, not only in that page. It may not be possible to know in advance what will be passed to it as a parameter; it may be that item value, it may be something else. I don't see what is wrong about passing the value of the item itself if needed.

                  In any case, consider this code put in a page process:
                  if :p1_x is null then
                    someproc; -- this will update the session value of p1_x to something other than NULL
                  end if;
                  -- when this returns, if p1_x was null its value will be changed back to NULL by Apex
                  Although I am not assigning any value to P1_X or passing it as a parameter to a procedure, the same issue happens here! Surely you cannot say that it is not a good practice to test a page item that will be changed by a procedure call in the same block further down; that would be too restrictive in my opinion.

                  This may not be considered a bug, but it is not very intuitive either. I think most developers would expect the session state not to be affected by referencing an item in an IF condition.

                  Luis
                  • 6. Re: Funny side effect using : to reference an application item in a page proc
                    Joel_C
                    Luis Cabral wrote:
                    Hi Joel

                    I am a big Apex fan and I meant this as a constructive criticism, to help improve the tool.
                    No doubt, man! :-)
                    The problem would resolve itself if you chose on or the other method - either passing values via parameters in AND out of the procedure and setting the value in the page process OR referencing the page item directly via session state calls. Think about it: if you already know what application item you are updating in advance WITHIN the procedure, why do you need to pass the value as a parameter in the first place?
                    Let's assume that the procedure I am calling is used in many places, not only in that page. It may not be possible to know in advance what will be passed to it as a parameter; it may be that item value, it may be something else. I don't see what is wrong about passing the value of the item itself if needed.
                    Well, IMO passing the item value as a parameter in this case is unecessary - unless you are passing the name of the item into the procedure as a parameter (which you did not appear to be doing in your example), your procedure must know in advance (or have a means to derive internally) what page item you are going to be updating, regardless of how many pages the procedure may be called from. If so, you could reference the existing value already using that method without having it passed into the procedure as a parameter. It's like the value comes in one door and leaves out the window (mixing my metaphors here!).

                    As I say, it would be a different situation if you had total separation of concerns - namely that you didn't touch session state within your database's stored procedures and communicated with your APEX-based UI through parameters and function return values only. From Re: Developer's code reviews and version control, Morten (Mobra) favours a two-layered approach to database logic, where one layer of stored procedures deals with session state information, and the other layer is "pure".

                    Combining both approaches as you appear to be doing is, IMO "putting your hands under the covers" of the code - the APEX equivalent of the Object Orgy anti-pattern.
                    In any case, consider this code put in a page process:
                    if :p1_x is null then
                    someproc; -- this will update the session value of p1_x to something other than NULL
                    end if;
                    -- when this returns, if p1_x was null its value will be changed back to NULL by Apex
                    Although I am not assigning any value to P1_X or passing it as a parameter to a procedure, the same issue happens here! Surely you cannot say that it is not a good practice to test a page item that will be changed by a procedure call in the same block further down; that would be too restrictive in my opinion.
                    Hmmmm I'd need to test this myself to verify - does this really happen? In any case, I'd repeat what I said before, you could be doing these tests elsewhere in your code, or indeed move the test logic into the stored procedure itself.
                    This may not be considered a bug, but it is not very intuitive either. I think most developers would expect the session state not to be affected by referencing an item in an IF condition.
                    I do agree with you incidentally, however I do think the situation can be avoided by changing one's approach, namely enforcing encapulsation and separation of concerns.
                    • 7. Re: Funny side effect using : to reference an application item in a page proc
                      Scott Wesley
                      I remember working with Luis on this particular problem and the behaviour was really quite odd!

                      The comment regarding mixed paradigms hit home for me - we're passing a bind variable IN as an actual parameter, then attempting to modify the value of that variable within the procedure call. Regardless of Apex' behaviour, I think this logic needs adjusting.

                      Joel, your link to Morten's discussion on layered approaches actually points to this post - do you still have the link handy?