Sunday, March 18, 2018

Delete a Row of a Report with a Dynamic Action

I was asked recently how I implemented the deleting of a row in my demo application, so I thought I would post the answer here in case anyone else was curious.

To accomplish the deletion of a row with a prompt to the user for confirmation you will need a hidden page item and a dynamic action.

On the page that has your report, add a hidden page item - mine is called P20_DELETE_ID. This item will be used by our dynamic action to hold the primary key of the row on which the user clicked the delete icon.

You need to add a link column to your report for the delete icon with the following attributes:

Link Target: URL
URL: javascript:void(null);
Link Text: <span class="t-Icon fa fa-trash delete-note" aria-hidden="true"></span>
Link Attributes: data-id=#REMOVE#
Link Column

Notice in the Link Text that I added a class called delete-note. This is the jQuery selector we will use to trigger our dynamic action. Also, notice the Link Attributes - the column referenced here, in my case #REMOVE#, should hold the primary key for the row. This value will be used to identify the row that needs to be deleted in your delete PL/SQL statement.

Next, we need a dynamic action that fires on click of our jQuery selector, .delete-note. This action will have four true actions:
Delete Row Dynamic Action

True Action #1: Confirm
Text: Are you sure?
Confirm

True Action #2: Set Value
Settings:
Set Type: JavaScript Expression
JavaScript Expression: $(this.triggeringElement).parent().data('id')
Affected elements:
Selection Type: Item(s)
Items(s): P20_DELETE_ID <---- your hidden page item
Set Value

True Action #3: Execute PL/SQL Code
PL/SQL Code: delete from jsd_notes where id = :P20_DELETE_ID;
Items to Submit: P20_DELETE_ID <---- your hidden page item
Execute PL/SQL Code

True Action #4: Refresh
Selection Type: Region
Region: Notifications <---- your report region
Refresh

And that should do it :)


- Jackie -

35 comments:

  1. Nice work, thanks for posting a VERY clear example.

    ReplyDelete
  2. Hi Jackie, Thanks for sharing this.

    The only issue with this is that any user can easily modifiy the "id" in the html (using developer tools) and delete rows that they should not....

    So, I would add more control in the PL/SQL section to check if the current user can delete the selected record and maybe also use the "ROWID" instead of the "ID", it will be hard for the user to find an existing ROWID...

    ReplyDelete
  3. Good point, but no doubt that would be part of the server validation performed when the DELETE statement is migrated to a PL/SQL package ;p
    This would improve performance, increase security, encourage re-use.

    ReplyDelete
  4. Simple + Perfect.

    ReplyDelete
  5. Hi,

    It is nice article. I tried like above steps, Its working for the first time, but its not working for second time.

    please check below link

    https://apex.oracle.com/pls/apex/f?p=26664:2:103876840860910:::::

    Please help ,

    Thank you in advance.

    ReplyDelete
    Replies
    1. Hi Santhosh -

      In the Delete Row dynamic action, the Event Scope should be set to 'Dynamic' instead of 'Static'

      Hope that helps,

      Jackie

      Delete
    2. Thanks, that saved my time :-)

      Delete
  6. Yes Jackie, it's working fine now....Thank you very much ....

    ReplyDelete
  7. This is really neat!
    I have been using the Ajax approach from Nick Buytaert:
    https://apexplained.wordpress.com/2012/02/12/instant-row-deletion-from-report/

    which works perfectly fine, even on 5.1.4 but your idea is pretty clever too, and yours because it does a region refresh, the count is always correct!

    Good job!
    Next time I'll use your approach
    Gaspar

    ReplyDelete
  8. Really nice. I'm pretty new to Apex and I got the delete to work on my IR but what if you had 3 rows and only the second row was able to "delete"??

    In my sql I can tell what rows can be deleted(or edited) based upon a condition. I would like the "delete"(or "edit") capability only if that condition is true.

    So can we dynamically display the "Link Text" like below if the correct condition exists based upon each row in the sql statement??

    "Link Text"
    --Edit
    --Delete

    or is there a totally different way to handle dynamic "edit/delete" functionality??

    Thanks

    ReplyDelete
  9. Hi Jackie, thanks for this post, I am really close to having it work. However the value for the page item returned is literally #REMOVE# instead of the relevant id value which is causing the PL/SQL code to fail. Thanks in advance, Frank

    ReplyDelete
  10. Sorry please disregard my last post. Restarting my browser fixed the issue.

    Thank you
    Frank

    ReplyDelete
  11. Hi Jackie, can you provide a download link for the demo application, I cant make this work, please

    ReplyDelete
  12. I think this undoes all the hard work the PEX dev team have done on making APEX secure unless I am missing the point?

    ReplyDelete
  13. Jackie post a demo application in https://apex.oracle.com/pls/apex/f?p=118836 and Im a newby in this thing and I tried to undestand more the code. This demos application is simple and for learning purpose, I dont think this can undoes all the hard work of the PEX dev team. That, my friend, is the point. Jackie help me please

    ReplyDelete
  14. Excelente articulo realmente me salvo pude hacer una funcionalidad que no me funcionaba por nada

    Muchas gracias me ayudo un monton

    ReplyDelete
  15. I just have tried and it works smothly.... Jackie, thanks for sharing !!!

    ReplyDelete
  16. This stopped working with Classic Report in Apex 19.

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. Hello, I did the step by step guide, but I'm using version 19.1, and when I click on delete the page it performs only the refresh. I changed pl / sql but it does not work. Someone help me?

    ReplyDelete
  19. it does not work
    $(this.triggeringElement).parent().data('id')
    should i change id to my column name?

    ReplyDelete
  20. For those having trouble getting the hidden item to populate, make sure you are selecting Set Value for the second true action, then JavaScript Expression for the Set Type, as opposed to selecting Execute JavaScript Code as the true action and putting your jQuery code there.

    ReplyDelete
  21. Thanks for sharing this.. Is there a way if we can add text to some field as a reason to delete?

    ReplyDelete
  22. Hello, I've used (with Apex 18.2) this article and the element is welled removed from the report (from an apex_collection) but after the refresh, the page is redirected to javascriptvoid:(null) in the browser ... :( I've maybe miss something. Does anyone can help me ? thx

    ReplyDelete
  23. After deleting gives me following error after return to application page refresh and showing record deleted. please guide me.

    Error processing request.

    Contact your application administrator.

    Return to application.

    ReplyDelete
  24. Hi Jackie, this worked exactly as you said it would, thank-you. I was trying to put in some relevant information in the confirm box so the user had some confidence they were deleting the correct row. I had used the ID from $(this.triggeringElement).parent().data('id') to populate another hidden item and then call it via substitution in the Confirm box, but it never seemed to update in time for it to be used correctly in the Confirm text. So I thought about using "javascript:apex.confirm("Are you sure you want to delete this " + $v("P21_DELETE_NOTES")) but I'm struggling to get that confirm box to operate. Any suggestions? Another tutorial like the above would be awesome if it is possible. Thx Sean

    ReplyDelete
  25. Ajax call returned server error ORA-01722: invalid number for Execute PL/SQL Code. i am getting this error

    ReplyDelete
    Replies
    1. make sure you create hidden page items like P20_DELETE_ID

      Delete
  26. Hi,
    In latest version of apex "Oracle APEX 21.2.0" missing "True Action #3: Execute PL/SQL Code" from step 3. Is any solution to do it in the latest version of apex?

    ReplyDelete
  27. Hi jackie, im from indonesia, sory for my bad english, can you create action for undo delete to?
    thank you very much

    ReplyDelete
  28. This is a fantastic post and solution which I have used many times and still using in 2022. Thanks!

    ReplyDelete