X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Extending interaction to Interactive Grids - Part II

Monica Godoy
Principal Product Manager

In my last blog post, Extending interaction to Interactive Grids, I showed you how to change the status of several employees by clicking an Activate or Inactivate button. But what if you need to execute stored procedure for selected employees in the Interactive Grid?

What's the best way to do it?

  • To Create a table trigger that executes when the user updates a column in the table.
  • To Execute a procedure for selected employees in the Interactive Grid.

The first option is pretty easy but you need to update at least one column in the table. The second option, on the other hand, lets you execute a specific procedure for one or more selected employees and still use the Interactive Grid to insert / update / delete employees as usual. So, let's go with the second option. 

The APEX version used in this example is version 19.2.0.00.18.

What Do You Need?

In this example, you will need the same data set used in the previous post: EMP / DEPT.
Since you're going to execute a procedure for selected rows when the user clicks a button, you will need to create a procedure and some logic. Let's create a simple table to save the favorite employees and the date they were added.

To create the table:

  1. In the main menu, select SQL Workshop.
  2. Select Object Browser.
  3. Click on the + button at the right side, select Table.
  4. For Table Name, enter FAV_EMP.
  5. Create three columns as follow:

    Click Next.
  6. For Primary Key, choose Populated from a new sequence.
  7. Enter Primary Key Constraint Name.
  8. For Primary Key, select ID (number).
  9. Enter Sequence Name.
  10. Click Next.
  11. Enter Foreign Key Name.
  12. Select EMPNO.
  13. For References Table, select EMP.
  14. For Referenced Column, select EMPNO.
  15. Click Next.
  16. Nothing to do in Constraints, click Next.
  17. Click Create Table.

Next, create a procedure to be executed for the selected employees. 
For parameters, you will only need the "list" of employees IDs selected in the Interactive Grid. You can work with this "list" as a JSON array, go through the array in a loop and finally insert the employee's id in the table FAV_EMP. 
I'll use FORALL to optimize the performance of the insert statement.

To create the procedure:

  1. Click on the + button at the right side, select Procedure.
  2. Enter the Procedure Name: PRD_FAV_EMP.
  3. Click Next.
  4. Include one argument:
    • Argument Name: p_selected_ids_json
    • In / Out: IN
    • Argument Type: NUMBER
  5. Click Next.
  6. For Procedure Body, enter:
  7. create or replace procedure PRD_FAV_EMP
    (p_selected_ids_json          IN varchar2)
    is 
        type numTab is table of number(4)  index by binary_integer;
        l_emp numTab;
        l_array     JSON_ARRAY_T;    
    begin
        l_array := json_array_t(p_selected_ids_json) ;
    
        for j in 0 .. l_array.get_size() - 1 loop
            l_emp(j+1) := l_array.get_number(j);
        end loop;
    
        forall j in 1..l_emp.count   
          insert into audit_employee(id, fav_date, empno) 
          values(audit_employee_seq.nextval, sysdate, l_emp(j));
    
    end PRD_FAV_EMP;
    
  8. Click Next.
  9. Click Create Procedure.

Now, let's use the same page that you created in the previous post in order to reuse the Interactive Grid.

Ajax Callback Process

The next step is to create an APEX process to call the PRD_FAV_EMP procedure. Complete following these steps:

  1. In the first panel, go to the Processing tab.
  2. Right click on Ajax Callback and select Create Process.
  3. For Name, enter PRD_FAV_EMP.
  4. For PL/SQL Code, enter:
PRD_FAV_EMP(p_selected_ids_json     => apex_application.g_x01);


JavaScript Initialization Code

The last thing is to change is the JavaScript function. For this example, you don't need to implement two buttons (Activate / Inactivate). What you need is to create one button to mark the employees as favorites and then invoke the Ajax Callback Process previously created.

  1. Click on Interactive Grid region.
  2. Go to Attributes.
  3. In the Advance region, replace the code in JavaScript Initialization Code attribute with the following:
function (config)
{
  //No selected any row when the page is rendered
  config.initialSelection = false;
  //Begin - Creating one button, Favorite
  var $ = apex.jQuery,
    toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(),
    toolbarGroup = toolbarData.toolbarFind( "actions3" );

  toolbarGroup.controls.push(
  {
    type: "BUTTON",
    action: "favorite",
    icon: "fa fa-heart fa-anim-flash fa-lg",
    iconBeforeLabel: true,
    hot: true,
  });

  config.toolbarData = toolbarData;
  //End - Creating one button, Favorite  
  config.initActions = function (actions)
  {
    // Defining the action for Favorite button
    actions.add(
    {
      name: "favorite",
      label: "Favorite",
      action: favorite
    });
  }

  function favorite(event, focusElement)
  {
    var i, records, model, record,
      view = apex.region("ig_emp").widget().interactiveGrid("getCurrentView");
    var selectedIds = [];
    if (view.supports.edit)
    {
      model = view.model;
      records = view.getSelectedRecords();
      if (records.length > 0)
      {
        for (i = 0; i < records.length; i++)
        {
          record = records[i];            
          selectedIds.push(records[i][0]);
        }
          // Invoke Ajax Callback Process
          apex.server.process ("PRD_FAV_EMP", 
                               {x01: JSON.stringify(selectedIds)},
                               {dataType: 'text', 
                                success: function( pData )
                                {        
                                    //Refresh IG - Favorite Employee (Optional)                                     
                                    apex.region("ig_fav").refresh();
                                }
                               }
                              );
        
      }
    }
  }
  return config;
}


Finally, click the Save and Run Page button and test your changes.

Now not only you can maintain the employees but you can also execute a stored procedure for those employees you selected.

Take a look of this example by executing this application (Part II):
https://apex.oracle.com/pls/apex/f?p=extending_ig