Oracle APEX - Person Details Tutorial 02 (Delete functionality)

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.

Set as favorite

Bookmark

Email This

Comments (16)

Subscribe to this comment's feed

...
written by abdul , April 17, 2009

Hi RK,

How are you doing..?

One moer general question here, is Oracle Application Express an advanced version of OAF, is Oracle applciation Express going to replace OAF by oracle?
I think its really easier and quick to develop pages here than we do it by OAF.

Thanks
Abdul

report abuse

vote down

vote up

Votes: +0

Re: Abdul
written by Kishore Ryali , April 17, 2009

Abdul,
I'm going well. Thanks

APEX and OAF are totally different. ADF not APEX going replacing OAF in Fusion Middleware or new versions of EBS. APEX is evolving as a superior RAD tool, which has integration with EBS and wen services. Most Oracle developers are embracing APEX because it is easy to learn and yet powerful.

Kishore

report abuse

vote down

vote up

Votes: +2

Display of item on forms region on the basis of some operation
written by Hassan Shoaib , April 26, 2009

HI Kishore,

First of all thanks for APEX kickstart articles they have definitely help me alot as I am new to APEX.
I have one question for which I want your suggestion.
I have requirement to capture where if I would be coming from some page say 'page23' then page 2 items should be displayed as uneditable.

Can you please mention how I can cater this case in Apex.

Would be obliged for your help !!!
Thanks

Regards,
Hassan Shoaib

report abuse

vote down

vote up

Votes: +0

Re: Display of item on forms region on the basis of some operation
written by Kishore Ryali , April 27, 2009

Hi Shoaib,

Your requirement is to make page items in a page say '2' as Read-Only depending upon the page it is navigated from. Suppose parent page is '23', page 2 items are read-only. Otherwise page 2 items are editable.

The following is one way to achieving it. This applies only to page items not Tabular form columns.

1. Define Application Item say 'PARENT PAGE ID' in Shared Components > Application Items.

2. Go to page 23, in column link to page 2. Add a new item 'PARENT PAGE ID' with value as '&APP_PAGE_ID.'
Here you are setting current page id i.e. 23 to application item. So when you go to page 2, you can refer application item to get parent page id.

3. Go to page 2, in page item say 'P_STUDENT_FLAG' Read Only section, set the below
Read Only Condition Type: Value of Item in Expression 1 = Expression 2
Expression 1: PARENT_PAGE_ID
Expression 2: 23

So if you navigate to page '2' from another page say '4', Read-Only condition is evaluated to false and the page item is editable.

Hope this answers your requirement.

Kishore

report abuse

vote down

vote up

Votes: +1

Re: Display of items
written by Hassan Shoaib , April 29, 2009

Hi Kishore,

Thanks.. I have implemented your suggested solution and it worked.

Thanks again for the correspondence.

Regards,
Hassan Shoaib

report abuse

vote down

vote up

Votes: +0

Some problems
written by John J , December 17, 2009

I was able to get this to work using a test page. However, in my application I have a page with multiple regions. I've made the same changes in my application but for a specific region on this page. The application doesn't work anymore. I've noticed that the parameter passing now shows: http:// .... : smilies/tongue.gif

32_LINE_ID:#LINE_ID#. For some odd reason it's not translating or replacing the #LINE_ID# with the actual value. Do you know what's going on? thanks

report abuse

vote down

vote up

Votes: +0

Re: Some problems
written by Kishore Ryali , December 17, 2009

John,

Please check if LINE_ID column name exists or not?

Kishore

report abuse

vote down

vote up

Votes: +0

...
written by John J , December 17, 2009

Yes, the line_id exists. It's the same query from the test page which works.

report abuse

vote down

vote up

Votes: +0

...
written by John J , December 17, 2009

What it's doing is displaying in the URL: #LINE_ID.

report abuse

vote down

vote up

Votes: +0

...
written by John J , December 17, 2009

This must be an issue because there are multiple regions and multiple page processes. There's got to be something that is clearing the cache or values during the "After Header Processes" or "After Submit" process. I'm just not sure how to fix it at this point.

report abuse

vote down

vote up

Votes: +0

Figured out an answer - need another solution
written by John J , December 17, 2009

OK. Here's the solution. I had the line_id in the query but in the application I had the column set not to display ever. If I change it to display the application works just fine. However, I really don't want to display the column in the report output. How do I "hide" it?

report abuse

vote down

vote up

Votes: +0

Answer
written by John J , December 17, 2009

Ok, here's the other answer. Instead of setting the condition type to "NEVER" keep the default and set the "Display Text As" to Hidden. Now it works ...

report abuse

vote down

vote up

Votes: +0

Re: Answer
written by Kishore Ryali , December 17, 2009

John,
Thanks for sharing your solution.
Kishore

report abuse

vote down

vote up

Votes: +0

edit
written by Tasos , June 24, 2010

Hi,
one thing I would like to ask is how you implemented the edit action to update records. How can I do this
without using standard reports?

report abuse

vote down

vote up

Votes: +0

Re: edit
written by Kishore Ryali , June 24, 2010

Tasos,

I used wizard to create a form as shown in this article http://apps2fusion.com/at/64-k...te-person. 'Automated Row Fetch' process in Form page fetches data based on populated person_id field. 'Process Row' process will insert/update the record based on whether person_id is null or not.

How you navigate to Form page for insert/update drives your implementation. In my case, I navigated from standard report. If user clicks on update icon, I pass person_id to form page.

Kishore

report abuse

vote down

vote up

Votes: +0

checkbox
written by fouzia , December 10, 2010

hi,

first thanks for this site and tutorials, i'm using apex for 1 months and i want to use checkbox in order to delete rows in report instead delete icon , but i dont knew how, should i use the same procedure with delete icon .

report abuse

vote down

vote up

Votes: +0

Write comment

Name

Title

Comment