Kishore Ryali

This is continuation from APEX Search Page Tutorial where I implemented OAF Person Details Tutorial 01 in Oracle Application Express.

Objective: Implement "Delete Person" functionality as in OA Framework Tutorial 02.

Implementation Steps:

A brief overview of steps followed:

• Add a NULL report column 'DEL_ICON' for "Delete Icon".
• Define a hidden page item 'P1_DEL_PERSON_ID' to capture selected person id.
• Define column link with image and parameters

Upload deleteicon_enabled.gif in Shared Components > Images (Under Files section).
Go to column attributes for DEL_ICON. Change column link attributes as shown in below screenshot.

Link Text: [img src="#WORKSPACE_IMAGES#deleteicon_enabled.gif" alt="Delete"/] . P.S: Replace '[' with '' before using the img tag. The website is treating it as image if I didnot doctor the img tag.
Target: Page in this Application
Page: 1
Item1: P1_DEL_PERSON_ID
Value: #PERSON_ID#

• Create 'On Load - Before Header' page process with below PL/SQL to delete selected person.

Declare
Cursor c_del is select person_id from xx_person_details
where person_id = :P1_DEL_PERSON_ID;
l_person_id NUMBER;
Begin
Open c_del;
Fetch c_del into l_person_id;
If c_del%FOUND Then
-- Delete Person
delete from xx_person_details where person_id = :p1_del_person_id;
commit;
apex_application.g_print_success_message := 'Person Deleted';
End If;
Close c_del;
End;

• Set Condition Type of page process to when P1_DEL_PERSON_ID is NOT NULL as shown below.

Video:

The video demonstrations follow above implementation steps. 

Video for adding Delete icon to report (6:55 min).

Video for implementing Delete procedure (9:35 min).

Screenshot of Person Details apex application after delete functionality is implemented. My application can be accessed using the url http://apex.oracle.com/pls/apex/f?p=a2fperson:1

Why did we add NULL column in report query? Can't we define computation column or a placeholder like in Oracle Reports or Discoverer?
As the report is generated of the SQL query, we cannot use computation column like in other reporting tools without modifying the query. We can also set same column link attributes to PERSON_ID rather than adding a new NULL column.

What is #WORKSPACE_IMAGES#?
It is a built-in substitution string. APEX engine replaces it with location where uploaded images, JavaScripts (JS) and CSS specific to a workspace are found.

How is #WORKSPACE_IMAGES# different from #APP_IMAGES#?
#WORKSPACE_IMAGES# is used to reference uploaded images, JS, and CSS that are shared over many applications within a workspace.

#APP_IMAGES# is used to reference uploaded images, JS, and CSS that are specific to given application.

Try it yourself .... upload the above delete icon by not selecting application in Shared Components > Images and you use [img src="#APP_IMAGES#deleteicon_enabled.gif" alt="Delete"/] for column link text. Run the page and see what happens. P.S: Replace '[' with '' before using the img tag. The website is treating it as image if I didnot doctor the img tag.


URL shows P1_DEL_PERSON_ID parameter. User can edit the URL and delete the persons without clicking on Delete Icon. Are there better ways to implement Delete?
There are definitely more sleek ways to do it. Just a few food for thoughts:

• Enable 'Session State Protection' for application and set page access protection to 'Arguments must have checksum' to prevent user for tampering URL.

• Use JavaScript to copy value of selected Person Id to P1_DEL_PERSON_ID rather setting parameters in Column Link which shows up in URL.
• Use AJAX request i.e. htmldb_get function to call application process which deletes person record. This does not repaint the whole page.

I save these for later articles.


Packaged Application for Delete functionality:

My Packaged applications are created using APEX 3.2 version, you can only import them into APEX with same version. This packaged application has supporting objects i.e. table and sample data, along with apex application. You can import and run it without going through the above steps.

Download Tutorial 02 Packaged Application

Video for deploying packaged application (2:41 min). This video is applicable for deploying packaged applications for my next articles as well.

The zip has sql files for application (apex_tut02_app.sql) and image (apex_tut02_img.sql). For deploying image, select file type as 'Image Export' as shown in below image.

The next article I will implement Create and Update Person functionality as in OA Framework Tutorial 03 and Tutorial 04.

Kishore Ryali

Add comment