Delete row/record using dynamic action and jQuery in the Oracle Apex 5.x classic report

Deleting record from a classic report using AJAX request was a big challenge in the earlier version of Oracle Apex applications. One of the things I like the most with the new version of Apex 5.x is the dynamic action. It is now quite flexible to integrate PL/SQL, Javascript and AJAX actions quite easily. In this example, I will show you how to delete a record from a classic report by clicking on the delete button and refresh the report region once the record has been deleted. The report will look like this once you setup everything correctly. If you hit the delete icon (trash) it will delete the record from the database and refresh the report after execution complete. You can add new records and refresh the report once the record is created.

STEP ONE

Create a classic report to display columns in the report as you like. In my example, I am displaying an image along with other column names.

Once you created the classic report, expand the columns under the report region.

Set the following attributes for the link column.

Target: javacript:void(null);

Link Text: <span class=”t-Icon fa fa-trash delete-activity” aria-hidden=”true”></span>

Link Attributes: data-id=#ID#

STEP TWO

Now create a dynamic action to bind the event with the link column. In my case I have created “delete activity” dynamic action. Right-click on the dynamic action then click on “Create TRUE action”. First thing we want to do is alert user before deleting a record. If the user confirms then execute the delete operation. Under the Identification for the true action, select “Confirm” and under Settings type “Are you sure you want to delete this activity?“.  Leave the other options default.

Before we create a next True action, we need to create a hidden page item to hold the value of id when a user clicks on the delete icon. Make sure under the Source section “Type” is selected to NULL since by default it uses the Database Column.

Now create the next True action to grab the id from the clicked row and set into the hidden page field. In my case, the value will be set to P4_DEL_ACTIVITY item. Select the following attributes like the picture below.

Action: Set Value

Set Type: JavaScript Expression

Under the JavaScript Expression type the following –

$(this.triggeringElement).parent().data('id');

This will basically get the value of data-id attribute of the clicked item then set the value to the page hidden item.

Now the page item value is set, we have to create the third True action for the “delete activity” dynamic action. This action will execute the delete operation using PL/SQL block. For this select the following attributes –

Action: Execute PL/SQL

PL/SQL Code:

delete from rtt_site_activity where activity_id =:P4_DEL_ACTIVITY;

:P4_MSG := 'The activity has been deleted from the site';

Items to Sumit: P4_DEL_ACTIVITY

Please note I have another page item that I used to display a dynamic message based on the user operation selected. I will cover this in the next post since it is not within the scope of this example.

Ok, all set. Now let’s create the fourth True action to refresh the report region once the record has been deleted. Select the following attributes for the fourth action.

Action: Refresh

Selection Type: Region

Region: Activities (Name of the report region)

Leave the other attributes default.

We are all set now. Save the page and run the application. Try click on the delete button. This should prompt user with a warning whether to proceed with the delete operation or not.

Great it works and refreshes the list when an item has been deleted from the database. The only way I can see the record is deleted when I see the bottom list. It will be nice if it displays a message when the record is deleted. Now let’s implement a user-friendly message to display when a record has been deleted.

STEP THREE

To create a modal message, click on the edit page then click on the page. Add the following javascript function under the JavaScript “Function and Global Variable Declaration” section.

//custom message for the dynamic actions
function show_success_message(p_message){ 
  $("div#t_Alert_Success").remove();  //remove the item first 
 $('#APEX_SUCCESS_MESSAGE').append('<div class="t-Alert t-Alert--defaultIcons t-Alert--horizontal t-Alert--page t-Alert--colorBG is-visible t-Alert--warning" id="t_Alert_Success" role="alert">    <div class="t-Alert-wrap">      <div class="t-Alert-icon">        <span class="t-Icon"></span>      </div>     <div class="t-Alert-content">        <div class="t-Alert-header">          <h2 class="t-Alert-title">'+apex.util.escapeHTML(p_message)+'</h2>        </div>      </div>      <div class="t-Alert-buttons">        <button class="t-Button t-Button--noUI t-Button--icon t-Button--closeAlert" onclick="apex.jQuery(\'#t_Alert_Success\').remove();" type="button" title="Close Notification"><span class="t-Icon icon-close"></span></button>      </div>    </div>');
  
    $('#APEX_SUCCESS_MESSAGE').removeClass('u-hidden');
    
    $("div#t_Alert_Success").fadeOut(4000);
}

This javascript function will be injected into the page script block and can be accessed through the page items. Now, I want to remove any appended alert div first because if a user delete five records it will add five alert divs on top of each other. To make it nice and clean I am using –

$("div#t_Alert_Success").remove();

Once any previous div has been removed then append the APEX_SUCCESS_MESSAGE div and add the alert block. Remove the hidden attribute from this block to display to the user and fadeout nicely after 4 seconds. You can set the time as you like.

This will not work until we trigger the function using dynamic action. Now let’s extend the previous dynamic action to implement this. Create another true action “Execute JavaScript Code” and make sure it is just under the “Execute PL/SQL Code” action to keep the sequence in order. So, once a record has been deleted, display the deleted message then refresh the region. Under the settings of this section type the following javascript to call the previously created function.

show_success_message("The record has been deleted!");

Now you can see a nice message when a record has been deleted.

In the next post, I will try to extend this to dynamic message with insert operation. Maybe prevent from inserting a duplicate record.

Happy programming.