Tech

Oracle APEX: Sending big texts to modal popups

Jun 8, 2020 Written by Rene Mikami

In my experience, when working with Interactive Grid, most of the times, we need to open a modal popup to interact with selected records (single or multi selection). For example, let say we have an Interactive Grid listing some records and you want a button which bulk updates all selected rows with the same value. So, you would perform the following steps:

  1. Select lots of rows

  2. Clicks on the button which will open a modal popup

  3. In the modal popup, select a new value for all selected rows in a combo box

  4. Press the button update

  5. The app will update all selected rows with the selected value in the combo box

So let's say you have an Interactive Grid component with multi selection enabled. You create another page as a modal popup to handle all selected rows from that Interactive Grid. You then send a list of the selected row id’s (or any key that identifies these rows) to the modal popup.

There are a couple of methods to send parameters to modal popups in Oracle APEX. Some of them are:

  • using APEX_UTIL.SET_SESSION_STATE store procedure

  • using button Target menu and set items

  • using APEX_UTIL.PREPARE_URL stored procedure

Using any of the method above, concatenate the ids with some separator such as “|”, as a text format (eg. '3213|12312|1213|123123'). Be careful, if you select a large amount of rows, you will ending up getting an error, or the page simply will refresh without opening the modal popup.

This approach works perfectly fine if you don’t exceed the limit. You can send only page items variables which is strictly a varchar2 limited to 4000 characters. Furthermore, the APEX_UTIL.PREPARE_URL method uses HTTP requests with URL parameters to send them which means you also may face other issues.

 

Thanks to the JavaScript function apex.server.process and Apex Collection mechanism provided by Apex, we were able to send to the modal popup as many records as we needed.

A bit of context:

  • apex.server.process: is an out-of-the-box Apex API JavaScript function. It is used to call any PL/SQL process (ajax callback). This ajax callback function will be creating a collection with the selected record ids and will also provide an URL which opens the modal popup.

  • Apex Collection: is a temporary table. It is created in the user’s session which means, if you create it, you will have access to that collection anywhere in the user’s session. This collection stores all row’s id, keeps them in the session, so you can extract data from it in the modal popup.

 

This would be the flow:

APEX log

In this example, all starts in the component (eg. buttons) that will open the modal popup. It should have at least one JavaScript in its Dynamic action. This is the code with comments:

//your IG's static id
    var regionStaticId = 'ig-static-id';
    //the column name to be added in the collection (eg. primary key id)
    var fieldName = 'pkid';
    //array of the selected items
    var itemsSelected = new Array();    
    //json string to be passed to the ajaxcallback
    var array = '{"array":[';
    //getting the selected records
    var ig$ = apex.region(regionStaticId).widget();
    var gridView = ig$.interactiveGrid("getViews").grid;
    var selectedRecords = gridView.getSelectedRecords();
    
    //execute this code only if there is at least one row selected in the IG
    if(selectedRecords.length > 0 ) {        
        //get all selected column values and push in the itemsSelected array
        selectedRecords.forEach(function(record,index) {
            itemsSelected.push( gridView.model.getValue(selectedRecords[index],fieldName));
        });        
        //create the json
        array = array + itemsSelected + ']}';
      
        //if you want to send page items or parameter to the modal popup (assuming the modal popup is the page with ID 2). We need to send the fields comma separated and the values, also comma separated, respectively
        var fields = 'P2_ITEM1,P2_ITEM2,P3_ITEM3';
        var values = 'value1,value2,value3';
        //this is the jquery selector of the button (or any component) which opens the modal popup. eg. if the button's static id is button-id1, the selector should be #button-id1. Since it is jquery selector, you can use css, name, etc
        var dialogCloseJquerySelector = 'buttons-id-jquery'
        
        apex.server.process("OPEN_MODAL_WITH_COLLECTION", {p_clob_01 : array, x01: '5', x02: fields, x03: values, x04: dialogCloseJquerySelector}, {dataType: "text",
            success: function (data) {
                //the ajax callback should return the modal popup url. The command below opens the modal popup
                apex.navigation.redirect(data);
            },
            error: function (data) {
                console.log('an error has occurred when trying to call ajax callback');
            }
        }); 
    }    

 

Basically this JavaScript is calling the OPEN_MODAL_WITH_COLLECTION ajax callback function sending the following parameters:

  • p_clob_01: this is the big JSON array with all id’s build in the beginning of this code. This is the main reason this solution is possible, as there is no limitation of size when sending this parameter

  • x0[number]: this is generic parameters you can send to the ajax callback function. In this example:

    • x01: the page Id we want to open (modal popup)

    • x02: any extra page item name (if more than one, they should be comma separated) eg. “P2_PAGE_ITEM1,P2_PAGE_ITEM2,P2_PAGE_ITEM3”

    • x03: the values of the page item defined in x02 (comma separated, respectively with x02) eg. “p2_page_item_value1,p2_page_item_value2,p2_page_item_value3“

    • x04: the jquery selector of the component which opens the modal popup. Eg. if the button static id is “button-id”, then the value should be “#button-id”

 

The OPEN_MODAL_WITH_COLLECTION will return the modal popup URL, which will be opened using the JavaScript apex.navigation.redirect function:

declare
    v_collection_name varchar2(50) := 'SELECTED_ITEMS_COLLECTION'; --the collection name
    l_values apex_json.t_values; --the json values
    icount number; 
begin
    --parsing the parameter g_clob_01 from apex.server.process
    apex_json.parse (l_values, apex_application.g_clob_01);

    --getting a count of how many ids
    icount := APEX_JSON.get_count(p_path => 'array', p_values => l_values);
    
    --create a new collection, or truncate if it already exists
    apex_collection.create_or_truncate_collection( p_collection_name => v_collection_name );
    
    --loop through all values in the json
    for i in 1 .. icount loop
        --insert each row into the collection
        apex_collection.add_member(p_collection_name => v_collection_name,p_c001 => APEX_JSON.get_varchar2(p_path => 'array[' || trim(to_char(i)) ||']', p_values => l_values));
    end loop;
    --****collection created!****

    --this will output the url created by apex_util.prepare_url function
    htp.prn(  
                apex_util.prepare_url(
                    p_url => 'f?p=&APP_ID.:' ||  APEX_APPLICATION.G_X01 || ':&SESSION.::YES:RP,4:' ||  APEX_APPLICATION.G_X02 || ':' ||  APEX_APPLICATION.G_X03 ,
                        p_triggering_element =>  'apex.jQuery('''||APEX_APPLICATION.G_X04||''')'              
                )    
    );  
    
end;

 

Note that we are parsing the JSON with all selected ids in the g_clob_01 parameter sent via JavaScript. After that, the code will push all ids into the collection named “SELECTED_ITEMS_COLLECTION” and finally returns the modal popup URL using the function apex_util.prepare_url (note that the x01, x02, etc, are being used here as a parameter from the JavaScript).

Now, in the modal popup page, in any PL/SQL code, (or even in the query of an Interactive Grid, for example), you will have access to the collection “SELECTED_ITEMS_COLLECTION” and its data by running the query:

select * from apex_collections where collection_name = 'SELECTED_ITEMS_COLLECTION';

 

We’d love to hear your opinion on this post