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#
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:
True Action #1: Confirm
Text: Are you sure?
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
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
True Action #4: Refresh
Selection Type: Region
Region: Notifications <---- your report region
And that should do it :)
- Jackie -
Nice work, thanks for posting a VERY clear example.
ReplyDeleteHi Jackie, Thanks for sharing this.
ReplyDeleteThe 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...
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
ReplyDeleteThis would improve performance, increase security, encourage re-use.
Simple + Perfect.
ReplyDeleteHi,
ReplyDeleteIt 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.
Hi Santhosh -
DeleteIn the Delete Row dynamic action, the Event Scope should be set to 'Dynamic' instead of 'Static'
Hope that helps,
Jackie
Thanks, that saved my time :-)
DeleteYes Jackie, it's working fine now....Thank you very much ....
ReplyDeleteThis is really neat!
ReplyDeleteI 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
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"??
ReplyDeleteIn 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
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
ReplyDeleteSorry please disregard my last post. Restarting my browser fixed the issue.
ReplyDeleteThank you
Frank
Thank you
ReplyDelete--Sameer--
Use this code on selection of Classic Report...
ReplyDeleteHi Jackie, can you provide a download link for the demo application, I cant make this work, please
ReplyDeleteI think this undoes all the hard work the PEX dev team have done on making APEX secure unless I am missing the point?
ReplyDeleteJackie 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
ReplyDeleteExcelente articulo realmente me salvo pude hacer una funcionalidad que no me funcionaba por nada
ReplyDeleteMuchas gracias me ayudo un monton
I just have tried and it works smothly.... Jackie, thanks for sharing !!!
ReplyDeleteThis stopped working with Classic Report in Apex 19.
ReplyDeleteThank you.. its working for me
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHello, 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?
ReplyDeleteit does not work
ReplyDelete$(this.triggeringElement).parent().data('id')
should i change id to my column name?
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.
ReplyDeleteThanks for sharing this.. Is there a way if we can add text to some field as a reason to delete?
ReplyDeleteHello, 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
ReplyDeleteAfter deleting gives me following error after return to application page refresh and showing record deleted. please guide me.
ReplyDeleteError processing request.
Contact your application administrator.
Return to application.
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
ReplyDeleteAjax call returned server error ORA-01722: invalid number for Execute PL/SQL Code. i am getting this error
ReplyDeletemake sure you create hidden page items like P20_DELETE_ID
DeleteHi,
ReplyDeleteIn 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?
Hi jackie, im from indonesia, sory for my bad english, can you create action for undo delete to?
ReplyDeletethank you very much
This is a fantastic post and solution which I have used many times and still using in 2022. Thanks!
ReplyDeleteThank you!
ReplyDelete