Create Delete Row Button for Interactive Report in Oracle Apex

In this tutorial, I am giving a step-by-step example to create a delete row button for the interactive report in Oracle Apex. The functionality of delete operation would be, a delete button will display for every row in interactive report and when the user will click on the delete button, it will ask for the confirmation and if the user chooses Yes/Ok then it will delete the record from the database table and also it will delete the row from the interactive report and will refresh the report without submitting the whole page. To perform this task, follow these steps:

Create Delete Row Button for Interactive Report in Oracle Apex

In the below example, I have a report based on the customers and it has a primary key CUSTOMER_ID on that table and on the behalf of this column I will delete the row from the table. To test this example, you can use any table and primary key column or you can use also the ROWID.

1. Add One More Dummy Column to Interactive Report

Add one more column in your Interactive Report to use with the Delete button. I have added the column DEL as 'Delete'. Below is the example query:

select CUSTOMER_ID,
       CUST_FIRST_NAME,
       CUST_LAST_NAME,
       CUST_STREET_ADDRESS1,
       CUST_STREET_ADDRESS2,
       CUST_CITY,
       CUST_STATE,
       CUST_POSTAL_CODE,
       CUST_EMAIL,
       PHONE_NUMBER1,
       PHONE_NUMBER2,
       URL,
       CREDIT_LIMIT,
       TAGS,
       ACTIVE_CUSTOMER,
       'Delete' Del
  from DEMO_CUSTOMERS

2. Set the Following Properties for the `DEL` Column

Type: Link
Heading: Delete
Target > Type: URL
URL: javascript:void(null);
Link Text: <span class="t-Icon fa fa-trash delete-irrow" aria-hidden="true"></span>
Link Attributes: data-id=#CUSTOMER_ID#

Note: Change the CUSTOMER_ID with your report Primary Key column or the ROWID column, for example, data-id=#ROWID#.

Below is the screenshot of the above settings for your reference:

3. Create a Page Item to Hold the Primary Key Column Value

Now create a hidden page item to hold the primary key column CUSTOMER_ID value. Do the right-click on the interactive report region and select Create Page Item option and set the following properties:

Name: P26_CUSTOMER_ID (set the name according to your page)
Type: Hidden
Value Protected: No

4. Create a Dynamic Action for the Interactive Report's Delete Row Button

In Oracle Apex page designer, click on the Dynamic Actions Tab and do the right-click on the Click node and select Create Dynamic Action option and set the following properties:

Name: DA_DELETEROW
Event: Click
Selection Type: jQuery Selector
jQuery Selector: .delete-irrow
Event Scope: Dynamic

The jQuery Selector .delete-irrow is the class, which we have defined for the Delete button in the 2nd step. Below is the screenshot of the above setting:

5. Create 4 True Actions for the Above Dynamic Action `DA_DELETEROW`

Do the right-click on the Dynamic Action DA_DELETEROW and select Create True Action option and set the following properties:

Action: Confirm
Text: Are you sure to delete this customer?

Create another True action Set Value below the Confirm action and set the following properties:

Action: Set Value
Set Type: JavaScript Expression
JavaScript Expression: $(this.triggeringElement).parent().data('id')
Selection Type: Item(s)
Item(s): P26_CUSTOMER_ID

Create another True action Execute PL/SQL Code below the Set Value action and set the following properties:

Action: Execute PL/SQL Code
PL/SQL Code: Delete from demo_customers where customer_id = :P26_CUSTOMER_ID;
Items to Submit: P26_CUSTOMER_ID

Create the last True action Refresh below the Execute PL/SQL Code action and set the following properties:

Action: Refresh
Selection Type: Region
Region: Customers (this is the interactive report region on my page)

The task is complete now, you have created the delete row button for the interactive report. Save the changes and run the page to test. You will have the output as shown below: