Home » Developer & Programmer » Forms » Update Field if the Checkbox is Checked (Oracle Forms 6i)
Update Field if the Checkbox is Checked [message #662575] Fri, 05 May 2017 14:06 Go to next message
rkhatiwala
Messages: 177
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 #662617 is a reply to message #662575] Mon, 08 May 2017 02:59 Go to previous messageGo to next message
cookiemonster
Messages: 13787
Registered: September 2008
Location: Rainy Manchester
Senior Member
The on-insert/update/delete triggers exist for interacting with non-oracle DBs. Don't use them for anything else.
For what you want the simplest solution would be a button.
Re: Update Field if the Checkbox is Checked [message #662645 is a reply to message #662617] Mon, 08 May 2017 13:30 Go to previous messageGo to next message
rkhatiwala
Messages: 177
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 #662646 is a reply to message #662645] Mon, 08 May 2017 13:59 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If code you posted works as you expected, why wouldn't you - instead of creating a complete WHERE clause - perform UPDATE? Here:
if checkbox_checked('sub.select') then 
   -- p_where := p_where ||','|| :sub.vehicle_id; 
   UPDATE m_vehicles SET ag_user = :sub.ag_user WHERE veh_id = :sub.vehicle_id;
end if; 
Re: Update Field if the Checkbox is Checked [message #662648 is a reply to message #662646] Mon, 08 May 2017 14:14 Go to previous messageGo to next message
rkhatiwala
Messages: 177
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 #662651 is a reply to message #662648] Mon, 08 May 2017 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/WHEN_OTHERS
Re: Update Field if the Checkbox is Checked [message #662652 is a reply to message #662651] Mon, 08 May 2017 15:33 Go to previous messageGo to next message
rkhatiwala
Messages: 177
Registered: April 2007
Senior Member
Thanks. But it its not going to exception part. I do get the message of 'success', so update statement is not erred out
Re: Update Field if the Checkbox is Checked [message #662653 is a reply to message #662652] Mon, 08 May 2017 16:05 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you test the following code (a more complete one, as it seems you didn't get my point):
begin 
  first_record; 

  loop 
    if checkbox_checked('sub.select') then 
       update m_vehicles set 
         ag_user = :sub.ag_user 
         where veh_id = :sub.vehicle_id;
    end if; 

    exit when :system.last_record = 'TRUE'; 
    next_record; 
  end loop; 

  standard.commit;
end; 
Any improvement?
Re: Update Field if the Checkbox is Checked [message #662654 is a reply to message #662653] Mon, 08 May 2017 16:10 Go to previous messageGo to next message
rkhatiwala
Messages: 177
Registered: April 2007
Senior Member
Thanks.
Yes that individual UPDATE works, but in the loop if we create a list of veh_ids ( instead of UPDATEing each row), and then just do one UPDATE, it doesnt work as EXECUTE IMMEDIATE doesnt work in Forms 6i.
Re: Update Field if the Checkbox is Checked [message #662661 is a reply to message #662654] Tue, 09 May 2017 03:32 Go to previous messageGo to next message
cookiemonster
Messages: 13787
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are we writing any updates? Why aren't you just modified the data-block values and letting forms do the update?
Re: Update Field if the Checkbox is Checked [message #662663 is a reply to message #662661] Tue, 09 May 2017 06:28 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps UPDATE affects a table which is different from data block's table?
Re: Update Field if the Checkbox is Checked [message #662670 is a reply to message #662661] Tue, 09 May 2017 08:12 Go to previous messageGo to next message
rkhatiwala
Messages: 177
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 Go to previous messageGo to next message
cookiemonster
Messages: 13787
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 Go to previous messageGo to next message
rkhatiwala
Messages: 177
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 #662689 is a reply to message #662686] Tue, 09 May 2017 10:38 Go to previous messageGo to next message
cookiemonster
Messages: 13787
Registered: September 2008
Location: Rainy Manchester
Senior Member
No, you still don't need update.
You're supposed to write code in the button that does what I described above. I'm not expecting the users to do it manually.
Re: Update Field if the Checkbox is Checked [message #662696 is a reply to message #662670] Tue, 09 May 2017 14:09 Go to previous message
Littlefoot
Messages: 21530
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".
Previous Topic: How to create sequence.
Next Topic: prevent duplication on the form
Goto Forum:
  


Current Time: Sun Jan 26 09:09:45 CST 2020