Home » Developer & Programmer » Forms » Unable to delete a record from a block based on a View
Unable to delete a record from a block based on a View [message #627769] Sun, 16 November 2014 01:32 Go to next message
Asfakul
Messages: 43
Registered: July 2014
Member
HI all,
I must say I am quite frustrated and disappointed at the same time with this problem which I have been trying to resolve for a long time. Here is what I have done.

1. Made a block based on a view.
2. at run time changing the where clause to get results based on two text field employee_id and department which I let the user to enter.
3. Wrote this procedure P_DEL_REC which is "trying" to delete with no success. Embarassed

I will attach the .fmb file for reference. the form can be run from local machines after executing the below statements

create table emp as 
select * from employees;


create or replace view v_emp_data
as
select e.employee_id, 
       e.first_name||','||e.last_name names,
	   e.salary,
	   d.department_name
from emp e, departments d 
where e.department_id=d.department_id;


P_DEL_REC


PROCEDURE P_DEL_REC IS
BEGIN
  go_block('b_search_result');
  first_record;
  loop
  	 if :b_search_result.cb_select_single='Y' then
  	 	   delete from emp
  	 	   where employee_id=:b_search_result.employee_id;
  	 end if;
  	 next_record;
     exit when :system.last_record='TRUE';
  end loop;
  commit_form;
END;


but each time I am getting "NO CHANGES TO SAVE" error which is very annoying after all my endeavor. Please help . I am using oracle forms 10G

[Updated on: Sun, 16 November 2014 01:33]

Report message to a moderator

Re: Unable to delete a record from a block based on a View [message #627771 is a reply to message #627769] Sun, 16 November 2014 02:39 Go to previous messageGo to next message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's expected; Forms didn't detect any changes as far as its data block is concerned, so there were no changes to be saved. If you wanted a different message, you could have wrote it yourself (using the MESSAGE built-in).

However, what did your procedure really do? If you go to SQL*Plus and check the EMP table, are those records missing (i.e. they are successfully deleted), or are they still there? If the latter, your procedure code is wrong as it deletes nothing.

Why did you create a block based on a view (which seems to be exact copy of the table)? If you based it on the table, built-in Forms' functionalities (including DELETE toolbar button) would work just fine.

If this is just a simplified example of what you really did (such as: a view joins several tables), then consider a different approach: base the data block on the view and utilize database INSTEAD OF trigger(s) which would take care about data manipulation over the view. Basically, you'd use the same button and the same loop, but you'd have DELETE_RECORD call instead of DELETE FROM ... statement.
Re: Unable to delete a record from a block based on a View [message #627782 is a reply to message #627771] Sun, 16 November 2014 09:16 Go to previous messageGo to next message
Asfakul
Messages: 43
Registered: July 2014
Member
Hi LittleFoot,Thanks for your reply. Actually I have the requirement to have a DELETE button as I will not be using forms built in tool bar. The view selects data from emp and department table. so yes it selects from multiple table.

The above delete query works fine outside. I am not very experienced with forms and dont know how to solve this problem. Please suggest a solution. Thanks.
Re: Unable to delete a record from a block based on a View [message #627783 is a reply to message #627771] Sun, 16 November 2014 12:59 Go to previous messageGo to next message
Asfakul
Messages: 43
Registered: July 2014
Member
HI littlefoot, Can I suppress this message ? If I suppress will the delete statement work?
Re: Unable to delete a record from a block based on a View [message #627784 is a reply to message #627783] Sun, 16 November 2014 13:04 Go to previous message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A view & INSTEAD OF trigger doesn't mean that you can't use your own DELETE button.

Suppress the message with specifying :SYSTEM.MESSAGE_LEVEL to a higher level, such as
:system.message_level := 25;
commit_form;
:system.message_level := 0;


Will DELETE work? Of course, why not? You 'll just "hide" the message, not stop DELETE from working.

[Updated on: Sun, 16 November 2014 13:06]

Report message to a moderator

Previous Topic: Oracle forms Check box Manipulation
Next Topic: How to Dynamically Populate a Poplist in the List item
Goto Forum:
  


Current Time: Mon Dec 18 01:24:18 CST 2017

Total time taken to generate the page: 0.02580 seconds