Home » Developer & Programmer » Forms » Multiple Block Navigation Problem (oracle 10g, Forms 6i)
Multiple Block Navigation Problem [message #434702] Thu, 10 December 2009 05:12 Go to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
Dear Members,

I have a form which contains three different blocks

1- master
2- detail (items to be produced)
3- sub-detail (contents to be used for production)

but the problem is whenever pointer move to next record in detail block form default behaviour ask for data saving therefore I am using POST keyword but the problem is, multiple users can not used this form simultaneously because due to internal locking.

Now please tell me what should I do? is there any other solution for this type of forms or any other thing which I am doing wrong.

All of your urgent reply would be highly appreciated.
Re: Multiple Block Navigation Problem [message #434731 is a reply to message #434702] Thu, 10 December 2009 07:29 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well if the users are changing records in the form then oracle needs to lock those records. If multiple users are changing the same records at the same time you will get locking issues - no real way to avoid it.

Or is this happening without the users actively changing anything?
Re: Multiple Block Navigation Problem [message #434853 is a reply to message #434731] Fri, 11 December 2009 04:43 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
Whenever users try to insert any record pointer changes into busy style till then I disconnect the already connected users session.

Re: Multiple Block Navigation Problem [message #434863 is a reply to message #434702] Fri, 11 December 2009 05:58 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you using standard forms functionality or custom code to insert the records?
Do have any pre/post/on-insert triggers on that block in the form?
Are there any database triggers on the table you are inserting into?
Re: Multiple Block Navigation Problem [message #434950 is a reply to message #434863] Fri, 11 December 2009 21:58 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
Yes, I am using PRE-INSERT trigger in all blocks due to updation of STOCK and record insertion in my LEDGER table.
Re: Multiple Block Navigation Problem [message #434967 is a reply to message #434702] Sat, 12 December 2009 03:59 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post the code.
Re: Multiple Block Navigation Problem [message #435047 is a reply to message #434967] Mon, 14 December 2009 00:31 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
Declare
	vqty				number;
	vrate 			number;
	v_comp			comm_users.cu_cc_compc%type;
	v_bran			comm_users.cu_cb_branc%type;
	v_dept			comm_users.cu_cd_dcode%type;
	v_dept_descr comm_dept.cd_descr%type;
	v_cgdescr		comm_godowns.cg_descr%type;
Begin
		Begin
			Select cu_cc_compc, cu_cb_branc, cu_cd_dcode, cd_descr
				Into v_comp, v_bran, v_dept, v_dept_descr
			 From comm_users, comm_dept
			 Where cu_cd_dcode = cd_dcode
			 And cu_usrid = :parameter.p_userid;
		Exception
			when no_data_found Then
			Message('User Details Not Exists in Setup...!');
			Message('User Details Not Exists in Setup...!');
			Raise form_trigger_failure;
		end;
	 Begin
		Select cim_qty, cim_rate
			Into vqty, vrate
		 From comm_item_detail
		  Where cim_icode = :iptr_cim_icode
		  And cim_cid_dcode = v_dept
		  And cim_cg_godwnc = :iptm_cg_godwnc;
	
			If nvl(vqty,0) >= nvl(:iptr_qty,0) Then
	
		 		Update comm_item_detail
		 		Set cim_qty = nvl(cim_qty,0) - nvl(:iptr_qty,0)
		 		Where cim_icode = :iptr_cim_icode
		 		AND cim_cid_dcode = v_dept
		 		AND cim_cg_godwnc = :iptm_cg_godwnc;
	
		 		Update comm_item_mast
		 		Set cim_qty = nvl(cim_qty,0) - nvl(:iptr_qty,0)
		 		Where cim_icode = :iptr_cim_icode;
			
				Begin
					Select	nvl(max(to_number(iptr_id)),0)+1
						Into :iptr_id
					 From int_prod_trans_raw;
				End;
			
		 		Begin
		 			Select cg_descr
		 				Into v_cgdescr
		 			 From comm_godowns
		 			 Where cg_godwnc = :iptm_cg_godwnc;
		 		Exception
		 			When no_data_found Then
		 			Null;
		 		End;
										 		
				Insert Into item_trans_detl
				(
				tid_id,
				tid_cc_compc,
				tid_cb_branc,
				tid_f_cd_dcode,
				tid_f_cddescr,
				tid_f_cg_godwnc,
				tid_f_cgdescr,
				tid_doc_type,
				tid_t_cd_dcode,
				tid_t_descr,
				tid_t_cg_godwnc,
				tid_t_cgdescr,
				tid_trans#,
				tid_trn_dt,
				tid_cim_icode,
				tid_descr,
				tid_cuom_ucode,
				tid_qty,
				tid_rate,
				tid_amt,
				tid_cfy_code
				)
				Select nvl(max(tid_id),0)+1,
				v_comp,
				v_bran,
				v_dept,
				v_dept_descr,
				:iptm_cg_godwnc,
				v_cgdescr,
				'INT/PROD-CON',
				v_dept,
				v_dept_descr,
				:iptm_cg_godwnc,
				v_cgdescr,
				:iptm_no,
				:iptm_dt,
				:iptr_cim_icode,
				:int_prod_trans_raw.disp_item,
				:iptr_cuom_ucode,
				:iptr_qty,
				vrate,	
				nvl(:iptr_qty,0) * nvl(vrate,0),
				:parameter.p_season
				From item_trans_detl;
	
			Else
	
				Message(:iptr_cim_icode||' Stock is '||vqty||' issuing '||:iptr_qty);	
				Message(:iptr_cim_icode||' Stock is '||vqty||' issuing '||:iptr_qty);
				Raise form_trigger_failure;
				
			End if;	
	Exception
	When no_data_found Then
	Message(:iptr_cim_icode||' Not available in stock...!');				
	Message(:iptr_cim_icode||' Not available in stock...!');				
	Raise form_trigger_failure;
	When too_many_rows Then
	Message(:iptr_cim_icode||' Multiple items...!');
	Message(:iptr_cim_icode||' Multiple items...!');
	Raise form_trigger_failure;
	end;
End;

[EDITED by LF: applied [code] tags]

[Updated on: Mon, 14 December 2009 03:57] by Moderator

Report message to a moderator

Re: Multiple Block Navigation Problem [message #435106 is a reply to message #434702] Mon, 14 December 2009 06:02 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suspect the updates are the root cause here.
Have you got mulitple users using the same cim_icode at the same time?
Re: Multiple Block Navigation Problem [message #435378 is a reply to message #435106] Tue, 15 December 2009 22:36 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
Yes, it is a posiblity but I need a solution to avoid this type of errors and also I need to update my stock therefore I am using update in this trigger.

If you have any other idea then please...

Re: Multiple Block Navigation Problem [message #435471 is a reply to message #434702] Wed, 16 December 2009 06:36 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sounds like a standard locking problem.
User 1 adds a record using cim_icode = 'A'. Your code updates the corresponding records in comm_item_detail and comm_item_mast.

User 2 adds a record using the same cim_icode, your code tries to update the same records in comm_item_detail and comm_item_mast and gets blocked by user 1.

User 2's session will hang untill user 1 issues a commit.

The only way around this is to not update those tables.
Re: Multiple Block Navigation Problem [message #435577 is a reply to message #435471] Wed, 16 December 2009 22:22 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
But cim_icode is a UNIQUE code, it couldn't repeat and its generate automatically at the time of record saving.
Re: Multiple Block Navigation Problem [message #435610 is a reply to message #434702] Thu, 17 December 2009 02:09 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
I was assuming it was unique.

Are comm_item_detail and comm_item_mast records created by this form? Are they the master and detail block? And is this pre-insert trigger from the sub-detail block?
Re: Multiple Block Navigation Problem [message #435802 is a reply to message #435610] Fri, 18 December 2009 01:50 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
No, comm_item_mast and comm_item_detail records are not created by this form.
Yes, comm_item_mast and comm_item_detail are the master and detail block.
Yes, this is a pre-insert trigger of sub-detail block.
Re: Multiple Block Navigation Problem [message #435820 is a reply to message #435471] Fri, 18 December 2009 03:22 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then why do you think that this isn't what is happening?

cookiemonster wrote on Wed, 16 December 2009 12:36
Sounds like a standard locking problem.
User 1 adds a record using cim_icode = 'A'. Your code updates the corresponding records in comm_item_detail and comm_item_mast.

User 2 adds a record using the same cim_icode, your code tries to update the same records in comm_item_detail and comm_item_mast and gets blocked by user 1.

User 2's session will hang untill user 1 issues a commit.

The only way around this is to not update those tables.

Re: Multiple Block Navigation Problem [message #435832 is a reply to message #435820] Fri, 18 December 2009 04:57 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
let suppose it is true then what should I do? becuase as I told you earlier I am facing problem in my real enviorment.
Re: Multiple Block Navigation Problem [message #435835 is a reply to message #434702] Fri, 18 December 2009 05:29 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't do the updates.
From the code it looks like you're doing the updates to ensure users can't assign a greater quantity of an item than is specified in those two tables. Which would a variant on storing totals in a master table.
You need to either:
a) Find a completely different way of implementing this check - querying the total current amount in the sub-table and comparing it against the totals on comm_item_detail and comm_item_mast would be the best bet.
b) Remove the check entirely
c) live with the locking problem - presuading your users to commit more often will help but it won't go away unless you do a or b.


Whenever you've got a situation where you update a parent table based on changes to a child table you've got a potential locking problem.
Re: Multiple Block Navigation Problem [message #435941 is a reply to message #435835] Sat, 19 December 2009 05:07 Go to previous message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
Thanks COOKIEMONSTER for being with me in all these communication and I will try to implement these checks by any other way.
Previous Topic: Getting around the restricted built-ins
Next Topic: failed to open file ifrun60_dump_1224
Goto Forum:
  


Current Time: Thu Dec 08 03:51:23 CST 2016

Total time taken to generate the page: 0.11295 seconds