2 Replies Latest reply on Apr 16, 2014 5:50 PM by klanchman

    PL/SQL in Dynamic Action seems to return incorrect value

    klanchman

      Hi all,

      I'm very new to Apex and PL/SQL, so I'm not sure if I've done something wrong here.

      I have a form with a Shuttle (P4_ITEMS) and a Display Only field (P4_ORDER_TOTAL). I have a dynamic action set up with the following PL/SQL:

      declare
          tab apex_application_global.vc_arr2;
          total number := 0;
          itemPrice number;
      begin
          tab := apex_util.string_to_table (:P4_ITEMS);
          for i in 1..tab.count loop
              select PRICE into itemPrice from PRODUCTS where PRODUCT_ID = tab(i);
              total := total + itemPrice;
          end loop;
          return total;
      end;
      

       

      As I understand it, tab should contain all items that are in the "selected" side of the shuttle. I am trying to sum up the prices of the selected items and return the total. Pretty simple, right?

       

      Here's some info about the Dynamic Action itself:

      Event: Change ; Selection Type: Item(s) ; Item(s): P4_ITEMS (the shuttle)

      The event (running the PL/SQL function) is also set to fire on page load. It is set up not to wait for results, so during calculation the display field disappears briefly.

       

      Here's the issue: P4_ORDER_TOTAL only updates correctly when I first load the page. I turned on debugging and I changed my shuttle, then checked the output.

      It saves the item P4_ORDER_TOTAL. In this case, we'll say it was 363.08. I added an item to the order, and the PL/SQL is executed. The result, however, is 363.08, even though it should have gone up. If I leave the page, and then click on its tab again, the order is still there, except this time it runs the PL/SQL again and it updates the P4_ORDER_TOTAL field to the correct value.

       

      Can anyone explain what's happening here? I can't figure out why it's not working as I intend.

      Feel free to ask me more questions if I did not adequately explain.

       

      Thanks in advance!

        • 1. Re: PL/SQL in Dynamic Action seems to return incorrect value
          fac586

          klanchman wrote:

           

          I'm very new to Apex and PL/SQL, so I'm not sure if I've done something wrong here.

          Always include the following information in your question:


          • Full APEX version
          • Full database version/edition/host OS
          • Web server architecture (EPG, OHS or APEX listener/host OS and versions)
          • Browser(s) and version(s) used


          For layout and visual formatting questions. also include:


          • Theme
          • Region type
          • Templates used

          Here's some info about the Dynamic Action itself:

          Event: Change ; Selection Type: Item(s) ; Item(s): P4_ITEMS (the shuttle)

          The event (running the PL/SQL function) is also set to fire on page load. It is set up not to wait for results, so during calculation the display field disappears briefly.

           

          Here's the issue: P4_ORDER_TOTAL only updates correctly when I first load the page. I turned on debugging and I changed my shuttle, then checked the output.

          It saves the item P4_ORDER_TOTAL. In this case, we'll say it was 363.08. I added an item to the order, and the PL/SQL is executed. The result, however, is 363.08, even though it should have gone up. If I leave the page, and then click on its tab again, the order is still there, except this time it runs the PL/SQL again and it updates the P4_ORDER_TOTAL field to the correct value.

           

          Can anyone explain what's happening here? I can't figure out why it's not working as I intend.

          At the time the dynamic action is executed, the changed value of P4_ITEMS is not available to the PL/SQL code in session state, it is only held in the browser. Include P4_ITEMS in the dynamic action's Page Items to Submit property so that the changed value is updated in session state before the PL/SQL code is executed.

          • 2. Re: PL/SQL in Dynamic Action seems to return incorrect value
            klanchman

            Always include the following information in your question:


            • Full APEX version
            • Full database version/edition/host OS
            • Web server architecture (EPG, OHS or APEX listener/host OS and versions)
            • Browser(s) and version(s) used

            For layout and visual formatting questions. also include:

            • Theme
            • Region type
            • Templates used

            Thanks, I'll keep that in mind for future posts!

             

            At the time the dynamic action is executed, the changed value of P4_ITEMS is not available to the PL/SQL code in session state, it is only held in the browser. Include P4_ITEMS in the dynamic action's Page Items to Submit property so that the changed value is updated in session state before the PL/SQL code is executed.

            That makes a lot more sense now. Thanks for explaining why it was happening. I updated the dynamic action as you suggested and it works as I expect now.

             

            Thanks fac586!