| Unable to delete a record from a block based on a View [message #627769] |
Sun, 16 November 2014 01:32  |
 |
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. 
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  |
 |
Littlefoot
Messages: 21823
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 #627784 is a reply to message #627783] |
Sun, 16 November 2014 13:04 |
|
Littlefoot
Messages: 21823
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
