Tuesday, October 11, 2016

Return Page Item From an AJAX Callback Process

Recently, I was working on a form that had multiple items retrieved when a select list item changed. The select list triggered a dynamic action that executed some PL/SQL code and returned item values.

It was looking like that:

At some point, the process became "too big" for the "Execute PL/SQL Code" action (it can only hold up to 4000 characters).

So, I decided to move everything to a page "AJAX Callback" process which can hold up to 32767 characters.

So I replaced the dynamic action "Execute PL/SQL Code" action with an "Execute JavaScript" action as follow:
/* Show a processing image */
var lSpinner$ = apex.util.showSpinner();

apex.server.process("SOME_PROCESS",
                    {pageItems: "#P2300_LOV"
                     },
                    {success: function(pData) {
                        /* If the AJAX is successful set the value or the returned items */
                        if (pData.success === true){
                            /* Loop through the array and set the value of each item */
                            for (var i=0; i < pData.items.length; i++){
                                apex.item(pData.items[i].id).setValue(pData.items[i].value);
                            }
                        }
                        
                        /* Remove the processing image */
                        lSpinner$.remove();
                      },
                     error: function(request, status, error) {
                        alert(request.responseText);
                         
                        /* Remove the processing image */
                        lSpinner$.remove();
                      }
                     }
                    );

And I created the AJAX Callback as follow:
declare
    /* Local variables */

    /* Utility function to output item's id and value */
    procedure output_json_item(p_item_name  in varchar2,
                               p_item_value in varchar2
                               )
    as
    begin
        apex_json.open_object;
        apex_json.write('id', p_item_name);
        apex_json.write('value', p_item_value, true); /* true so that null values are written as well */
        apex_json.close_object;
    end output_json_item;
begin
    /* ********************** *
     * PL/SQL Process Content *
     * ********************** */
    if :P2300_LOV = '1' then
        :P2300_ITEM1 := 1;
        :P2300_ITEM2 := 1;
        :P2300_ITEM3 := 1;
        :P2300_ITEM4 := 1;
    elsif :P2300_LOV = '2' then
        :P2300_ITEM1 := 2;
        :P2300_ITEM2 := 2;
        :P2300_ITEM3 := 2;
        :P2300_ITEM4 := 2;
    elsif :P2300_LOV = '3' then
        :P2300_ITEM1 := 3;
        :P2300_ITEM2 := 3;
        :P2300_ITEM3 := 3;
        :P2300_ITEM4 := 3;
    end if;
        
    apex_json.open_object;
    apex_json.write('success', true);
    
    apex_json.open_array('items');

    /* Call the utility procedure for every item you want to return */
    output_json_item('P2300_ITEM1', :P2300_ITEM1);
    output_json_item('P2300_ITEM2', :P2300_ITEM2);
    output_json_item('P2300_ITEM3', :P2300_ITEM3);
    output_json_item('P2300_ITEM4', :P2300_ITEM4);
    
    apex_json.close_array;
    
    apex_json.close_object;
exception
    when others then
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', sqlerrm);
        apex_json.close_object;
end;

The idea here is to have PL/SQL AJAX Callback execute and if it successfully completes, it will be returning the list of items along with their values as JSON on which we are going to loop through and set the items' value using the JavaScript item API.

You can have a look at my Demo Application

9 comments:

  1. Hi Maxime,

    you could simply use Execute PL/SQL Code DA and put your PL/SQL code into DB procedure and use apex_util.set_session_state to set item values.

    Br,
    Marko

    ReplyDelete
    Replies
    1. Hello Marko

      I should have thought about that :)

      Thanks
      Maxime

      Delete
    2. Generally speaking, I don't recommend putting calls to apex_util.set_session_state in compiled code. The reason is that the APEX Advisor can't scan the code and find, for example, if an item name has been changed and is thus no long valid.

      I would recommend using IN/OUT params with the procedure and calling it from the DA like:
      my_custom_package.some_proc(
      p_some_param => :P1_ITEM_NAME_HERE
      );

      After the process runs, if the value of the item is different than it was at the start, the APEX engine will update the item's value in persisted session state (doing the work of apex_util.set_session_state). Also, the Advisor can let you know if the item name changes and the code isn't updated.

      Delete
    3. Thanks Dan for you input.

      Keeps getting better :)

      Delete
  2. Hi!
    I agree with Marko, but I am very interested in your solution. If you have several select list items and needed several callbacks, how do you correlate each select list with its corresponding callback?

    Regards,

    Luis

    ReplyDelete
  3. hi Maxime Tremblay,

    i am facing Collapse issue handling navigation menu.
    i have one parent under 5 child pages in list menu when i click any child page the master menu is Collapsed.

    i want to display 5 child until exit form the parent menu.

    i have checked your demo application and there when i clicked child its not Collapsed. please share me how to archive that



    Collapse issue handling navigation menu
    This question is Not Answered.
    myluism
    Rookie
    myluism Oct 11, 2016 7:49 PM
    Hi All.

    I'm using Apex 5.0 and have a collapse issue using a navigation menu based on universal theme.

    I'll try to describe it:

    This is the hierarchy:


    As you can see; "Cuadro de Mando" has "Home" as parent and "Parámetros Globales", "Perspectiva" and "Detalle Perspectiva" as children.

    When clicking on "Parámetros Globales" is works as expected: the menu remains "open" or not collapse:



    However, if a select any other children, it actually goes to the expected page, BUT, it collapse the menu:



    Here you can see "Cuadro de Mando" menu has collapsed. Now i know this has something to do with these propecties:



    But, even if i put the same values for all pages under this menu, it does not behave consistenly.

    What am i missing????

    Thanks in advance!!!

    ReplyDelete
    Replies
    1. I replied on the thread you had over on the oracle forum
      https://community.oracle.com/thread/3980200

      Delete
  4. Thank you for the knowledge, it has been very useful

    ReplyDelete