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:

29 Comments

Rory Browne
September 19, 2020 / 3:04 pm
HI Vinish
I am new to APEX (using 19.2) and I find your blog to be a mine of amazing information. I love the way you describe in detail the steps to follow (for a new guy this is exactly what I want)
I wonder if you can help? I can see that you can set row highlighting on an IG and IR based on the value of a column in a row. I wonder if it is possible to show the row with strikethrough on each column when I delete the row?
What I mean is, if I use your example about deleting a row, say I just update an ACTIVE_IND column to be 'N'? I'd like to see the row still showing in the grid, just with each column value with a strikethrough - something similar to what you get out of the box with IG (but IG removes the row from the grid)
Any help is much appreciated.
Regards
Rory
- Vinish Kapoor
  September 19, 2020 / 11:33 pm
  Hi Rory,
  Thank you so much.
  I saw your question on orclqa.com, please find the reply there. Thanks.
  https://orclqa.com/question/add-strikethrough-to-deleted-row-in-interactive-grid-or-interactive-report/
- Rory Browne
  September 24, 2020 / 12:34 pm
  Hi Vinish
  Many thanks for the quick response. This worked just as I requested.
  One final step now is to disable the DEL button when ACTIVE_IND column = 'N'
  Any suggestions?
  Rory
- Vinish Kapoor
  September 24, 2020 / 12:48 pm
  Thanks Rory.
  Take two more columns in your query delclass and delhref as following:
```
Decode(nvl(t.ACTIVE_IND, 'X'), 'Y', ' t-Button t-Button--hot t-Button--small t-Button--simple delete-irrow', 'N', ' t-Button t-Button--hot t-Button--small t-Button--simple delete-irrow'
       , 'disablelnk') delclass,
Decode(nvl(t.ACTIVE_IND, 'X'), 'Y', 'javascript:void(null);', '#') delhref,
```
  Then specify the Link Target property as URL and specify the value as #DELHREF#
  Specify the link text as <span class="#DELCLASS#" aria-hidden="true">Delete</span>
  Add the following CSS in the CSS inline section of the page:
```
.disablelnk{text-decoration: none;cursor: default;}
li.a-IRR-controls-item.a-IRR-controls-item--highlight {
  display: none;
}
```
- Rory Browne
  September 24, 2020 / 2:45 pm
  That was quick! 🙂
  Many thank. I'll give it a go
  Rory
ZAKRIA
October 2, 2020 / 12:43 pm
HI Vinish
please completed as almost as this copy but got error:
Ajax call returned server error ORA-01722: invalid number for Execute PL/SQL Code.
- Vinish Kapoor
  October 2, 2020 / 1:05 pm
  Is your key field is alphanumeric?
  If yes then use data-id="#YOURFIELD#" for the 2nd step.
Gabriel Soler
October 21, 2020 / 7:23 pm
Hi Vinish
I did all the steps, but at the moment of executing the process nothing happens, the alert "Are you sure to delete this" jumps in screen but it does not make the delete and neither it refreshes the page
- Vinish Kapoor
  October 21, 2020 / 7:40 pm
  The above method is tested. You must be missing something that is why you are getting the error.
  Please check the steps carefully.
ZAKRIA
November 7, 2020 / 11:14 am
HI Vinish
Can we pass multiple page item in link attribute. if yes then how please help..
- Vinish Kapoor
  November 7, 2020 / 11:39 am
  You can pass it but then the target item will have two field values, you have to split it. For example:
```
"#CUSTOMER_ID#:#CUSTOMER_NAME#"
```
- ZAKRIA
  November 7, 2020 / 11:49 am
  many thanx 🙂
- ZAKRIA
  November 7, 2020 / 12:37 pm
  THIS IS WHAT I WANT TO DO...
- ZAKRIA
  November 7, 2020 / 12:39 pm
  I WANT VALUE AGAINST EACH PAGE ITEM NOT IN A ROW HOW TO SPLIT THEM PLEASE HELP MAN
- Vinish Kapoor
  November 8, 2020 / 7:47 am
  Use the following SQL query to split:
```
SELECT t.Column_Value AS ids
      FROM TABLE(Apex_String.Split(RTRIM(LTRIM(:P26_CUSTOMER_ID, ':'), ':'),
                                   ':')) t
```
suvankar roy
November 23, 2020 / 8:55 pm
How to delete row from interactive grid to follow the same as you define for interactive report.
NATH RANASINGHE
November 25, 2020 / 7:38 am
Great Post.
- najeeb alikhel
  December 31, 2020 / 5:47 pm
  it does not work on classic report for rowid what should we submit and plsql query
  delete from table where rowid=rowid
  how
Gabriel Gordo
January 5, 2021 / 8:00 pm
Hi Vinish,
First of all thank you for the posts of APEX tecnology, I have followed some of them and thry helped me a lot while I was creting some of my applications.
This time I have a problem showing some notifications to the user. I followed this tutorial and I could delete without any problem the row I selected. The problem is I am trying to show a success or error message to let the user know the result of the process but when I tried to delete the row, my concurrent request was executed and ended correctly, but there was no result message.
So how could I show a message depending on the result of the concurrent request? For exmple, when the concurent request ends in COMPLETE status show success message and when it ends with WARNING or ERROR status show an error message.
I am now executing the concurrent request and trying to show the success message via the PL/SQL code in step 5.3.
Thank you very much.
Regards,
Gabriel.
- Vinish Kapoor
  January 6, 2021 / 8:43 am
  You can do the following in step 5.3:
  Create a hidden page item, for example, P1_STATUS.
  Now in PL/SQL code (5.3) add the following line:
```
apex_util.set_session_state('P1_STATUS', 'COMPLETED');
```
  Set the above value according to your processing result (completed/error, etc).
  Specify P1_STATUS for the Items to Return field.
  Now create a dynamic action on P1_STATUS. Create a true action as Execute JavaScript code. Then add the following code to it:
```
If (apex.item('P1_STATUS').getValue() = 'COMPLETED') {
   apex.message.alert('Successfull.');
} else {
   apex.message.alert('Error.');
}
```
Krish
January 16, 2021 / 12:10 am
Hi Vinish,
I need to create add row and Delete row button for each row in tabular form like your IR example. can you suggest. Thanks in Advance
Krish
January 20, 2021 / 1:36 am
How to add row in interactive report. Can you please suggest.
Thanks very much
vlli
June 18, 2021 / 12:45 am
Thank you very much for this article, and at all for your blog! Very helpful
Arlei Lissoni
July 1, 2021 / 12:56 am
Your tutorial helped me. Thank you.
Sharra
July 3, 2021 / 10:57 pm
Hello - I followed your steps, completely, but for some reason, when you click the little trash can, nothing happens - no pop ups, no nothing. H
OPE - never mind, please delete. I figured it out. 😀
saumya rajpoot
October 6, 2023 / 12:45 pm
Hi Vinish,
How we can handle primary key in link_attributes when we have pk like (customer_id, date_of_birth)
- Vinish Kapoor
  October 6, 2023 / 1:06 pm
  If there are multiple, columns for PK, then it is better to use ROWID in your SQL query, and use that for link attributes as below:
  #ROWID#
Zaza
December 18, 2023 / 6:40 pm
hiiii
I've done all steps and dosen't work
i use rowid and have this error
Ajax call returned server error ORA-01410: invalid ROWID for Execute PL/SQL Code
i quess there is something wrong with set value
- Vinish Kapoor
  December 19, 2023 / 8:26 am
  Try specifying rowid in the single quotes for Link Attributes, for example: data-id='#ROWID#' and make the hidden page item visible to debug for which you are setting the value so that you can see the page item value after execution of the PL/SQL code.