| Update Field if the Checkbox is Checked [message #662575] |
Fri, 05 May 2017 14:06  |
rkhatiwala
Messages: 178
Registered: April 2007
|
Senior Member |
|
|
I have two blocks. Main (Control) block has a check-box, which when checked, checks all the check-boxes in the Sub block, so if there are 4 records in Sub block, all records are checked. If one of the user_name ( field) is changed in one of the records, we need to update all the four rows with the same user_name. If 3 out of 4 boxes are checked, then those 3 rows should be updated with the user_name that was changed.
How can i do this in block level trigger, I am trying to do this, but cannot have next_record in the on-update trigger at Sub block level
declare
p_where varchar2(500);
v_sql varchar2(5000);
begin
begin
first_record;
loop
if checkbox_checked('sub.select') then
p_where := p_where ||','|| :sub.vehicle_id;
end if;
exit when :system.last_record = 'TRUE';
next_record;
end loop;
message('P_WHERE = ' || ltrim(p_where, ','));
message('P_WHERE = ' || ltrim(p_where, ','));
end;
v_sql := 'UPDATE m_vehicles SET ag_user = :sub.ag_user WHERE veh_id in ('||p_where||')' ;-- :sub.vehicle_id ;
message('v_sql ='||v_sql);
exception
WHEN OTHERS THEN
message('error in update = '|| sqlerrm);
end ;
|
|
|
|
|
|
| Re: Update Field if the Checkbox is Checked [message #662645 is a reply to message #662617] |
Mon, 08 May 2017 13:30  |
rkhatiwala
Messages: 178
Registered: April 2007
|
Senior Member |
|
|
Thanks for the reply, I created button, and on button-pressed-trigger, i am getting all the id's to put in where clause. but since execute_immediate doesn't work in forms 6i, what can i use?
I created a procedure for update, and calling this from the trigger by passing the veh_id's in parameter, but veh_id field is numeric and when i pass it will be varchar2 because of comma in it (74201,74922,74923).
If I can find something like execute immediate, I'd prefer that, but for now I am trying to go by using proc.
Please advise.
|
|
|
|
|
|
| Re: Update Field if the Checkbox is Checked [message #662648 is a reply to message #662646] |
Mon, 08 May 2017 14:14 |
rkhatiwala
Messages: 178
Registered: April 2007
|
Senior Member |
|
|
There will be multiple veh_ids. so i m creating a list for where clause. so passing it in proc, from when-button-pressed trigger, as follows:
PROCEDURE Update_user(p_usr in varchar2,
p_where in varchar2
)
IS
v_upd varchar2(100);
BEGIN
message('proc p_usr='||p_usr||', p_where='||p_where);
v_upd := 'UPDATE main_vehicles
SET user = '||p_usr||
' WHERE veh_id in ('||p_where||');';
message('proc v_upd='||v_upd);
forms_ddl(v_upd );
commit;
if not form_success then
message('proc upd failed');
else
message('proc upd successful');
end if;
EXCEPTION
when others then
message('Err when updating user ='||sqlerrm);
END;
I am getting the message 'proc upd successful', but nothing is updated.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Update Field if the Checkbox is Checked [message #662670 is a reply to message #662661] |
Tue, 09 May 2017 08:12 |
rkhatiwala
Messages: 178
Registered: April 2007
|
Senior Member |
|
|
Based on the fields in the main (control) block, lets say, the sub block retrieves 10 rows. We click on the SELECT ALL checkbox in control block, which will select all 10 records in the sub block. If we change user (field) in one of the checked rows, all the checked 10 rows should be updated with the same user. If only 3 records are checked, then those three records should be updated.
I am creating a loop,and in loop check if the record is checked, if yes then get the veh_id, and keep appending the veh_id to the variable
p_where := p_where ||','|| :sub.veh_id;
After that i can use this p_where in
where veh_id in (p_where);
But veh_id is a numeric column, and p_where is varchar2 because of ','. that's where it is failing. That's why i was looking for dynamic sql, something similar to EXECUTE IMMEDIATE. As we cannot use EXECUTE IMMEDIATE in Forms 6i.
Thanks
|
|
|
|
| Re: Update Field if the Checkbox is Checked [message #662674 is a reply to message #662670] |
Tue, 09 May 2017 08:29 |
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you're updating the records that were queried by the form?
Then stop writing update statements.
Loop over the block, modified the appropriate datablock items and issue post (or commit if you don't want to give the users the option to discard the changes) to make forms issue the correct updates.
|
|
|
|
| Re: Update Field if the Checkbox is Checked [message #662686 is a reply to message #662674] |
Tue, 09 May 2017 10:04 |
rkhatiwala
Messages: 178
Registered: April 2007
|
Senior Member |
|
|
Users do not want to go into each record, and modify. So check-box is added.When one of the checked record is modified ( user field in this case), all the checked rows should be updated with the same user. That's the reason UPDATE is needed. Earlier users were doing one record at a time, and now they want to modify all.
Thanks.
|
|
|
|
|
|
| Re: Update Field if the Checkbox is Checked [message #662696 is a reply to message #662670] |
Tue, 09 May 2017 14:09 |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As of this part of your problem:
rkhatiwala
But veh_id is a numeric column, and p_where is varchar2 because of ','. that's where it is failing.
search the Internet for "Oracle varying elements in IN list".
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
