Instant row deletion from report

As the title states, we’re going to make it possible to remove a row from an interactive or regular SQL report. We’ll use jQuery and AJAX techniques to visually remove the row from the report and delete the record in the underlying database table. All this without the need for a page refresh. Have a look at the demo for the final result.

Let’s get started. The first thing you need is an interactive or SQL report. Make sure to include a primary key column in your source SQL query. The primary key will be passed as an argument to a JavaScript function called deleteEmp. After you created the report, edit the primary key report column and navigate to the Column Link section.

edit the PK report column link

• Link Text: I refer to a workspace image garbage.gif.
• Link Attributes: Assign the link an onclick event that executes the function deleteEmp with as arguments this and the primary key column name substitution string.
• URL: javascript:void(0); will prevent a page refresh.

The next step is to create an on demand application process that deletes a record from the well-known table EMP. Notice the global variable which will contain the primary key on the moment of execution. I named the application process delete_employee and entered the following PL/SQL anonymous block:

begin
  delete from emp
  where empno = apex_application.g_x01;

  commit;
end;

The last step exists of creating the JavaScript function deleteEmp which will be executed once the primary key link column is clicked. Edit the page attributes and paste the code snippet below in the Function and Global Variable Declaration under the JavaScript section.

function deleteEmp(p_this, p_empno) {
  // get the table row on which the user clicked
  var tr = $(p_this).closest('tr');

  // perform an asynchronous HTTP AJAX request using jQuery
  $.ajax({
    type: 'POST',
    url: 'wwv_flow.show',
    data: {
      p_flow_id: $('#pFlowId').val(),
      p_flow_step_id: $('#pFlowStepId').val(),
      p_instance: $('#pInstance').val(),
      x01: p_empno,  // assign p_empno to the g_x01 global variable
      p_request: 'APPLICATION_PROCESS=delete_employee'  // reference the application process
    },
    beforeSend:  // executes while the AJAX call is being processed
      function() {
        // delete following HTML classes from the table row element
        // could be possibly theme dependent
        tr.removeClass('even');
        tr.removeClass('odd');

        // use jQuery's animate function to give the table row, and its children, a red background
        tr.children().hover(function() {
          tr.children().animate({'backgroundColor': '#fbc7c7'}, 300);
        }, function() {
          tr.children().animate({'backgroundColor': '#fbc7c7'}, 300);
        });
        tr.children().animate({'backgroundColor': '#fbc7c7'}, 300);
      },
    success:  // to be called if the request succeeds
      function() {
        // jQuery has difficulties animating inline elements
        // that's why we wrap them in a div, which is a block element
        tr.children().wrapInner('<div>').children().fadeOut(400, function() {
          tr.remove();  // visually remove the row from the report
        });
      }
  });
}

That’s it. You can now delete an employee record by clicking the link in the primary key report column. The row gets a red background while the application process delete_employee is being executed. After successful execution, the row fades out and gets removed from the report. Don’t forget to check out the demo.