error frm 40352 last record of query retrieved

i am working in oracle forms, and have written a function inside a form which searches a particular value in the set of records.
the function is working fine but if  i enter a value which is not there in the record, it pops up a message box with error " FRM-40352 : last record of query retrieved." though it is not effecting the form, i dont want this message box to pop up.
attached is the code of the function.

FUNCTION find_matcode(block_name varchar2,searchstr varchar2, total_rec number) RETURN number IS
r_count integer;
found boolean := false;
BEGIN
  
  	r_count:=1;
 
 
  go_block(block_name);
	first_record;
		
	while r_count <= total_rec
	loop
		if  :system.cursor_value=searchstr then
		  	message('found equal');
		  	found := true;
		  	go_record(r_count);
				exit;
		else
	   		r_count:=r_count+1;
	   		next_record;
		end if;	
		
	
		
	end loop;
	if found = false then
			message (' no match found ');
			first_record;
		end if;
	return 0;
 
	exception 
		when others
	then	message('ERROR1 '||sqlerrm);
		
 
 	
END;

Open in new window

shruti27Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fekrat El WehediInfrastructure Architect / Oracle DBACommented:
Try removing the equal from the "r_count <= total_rec".
What does it give you then?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shruti27Author Commented:
yup its working...
but i have one more error if you can help!
i have a code written in trigger of a button....have attached the code.
on executing it gives following error message pop up
FRM-40400: Transaction complete:1 records updated
FRM-40352: Last record of query retrieved
FRM-40102: Record must be entered or deleted first
the last error pop up goes on an infinite loop and i have to close the whole window!
declare
	msg integer;
	rec_count number(5) ;
 
begin
	
    set_alert_property('alt_msg',alert_message_text,'Do you want the save the changes now?');
    msg:=show_alert('alt_msg');
    
	  if msg = ALERT_BUTTON1 then
	  begin
	  	commit_form;
	  	
	  	rec_count := 1;
	  	go_block('stock_master');
	  	first_record;
	  	
	  	while rec_count < :total_rec.rec+1
	  	loop
	  		
	  				if (:stock_master.ann_req!=:copy_stock_master.ann_req) then
	        	begin
	        	 	insert into stock_upd_hist values 
	        	    (sysdate,:stock_master.matcode,'AN',:copy_stock_master.ann_req,:stock_master.ann_req,:parameter.logid);
	        	  standard.commit;
		   	   
		   	      exception 
 	            when others then message(sqlerrm);
		   	   	end;
	  				end if;
	  				
	  				if (:stock_master.min_stock!=:copy_stock_master.min_stock) then
	        	begin
	        	  insert into stock_upd_hist values 
	        	    (sysdate,:stock_master.matcode,'MI',:copy_stock_master.min_stock,:stock_master.min_stock,:parameter.logid);
	        	  standard.commit;
		   	   
		   	      exception 
 	            when others then message(sqlerrm);
		   	   	end;
       			end if;
	  	
	  				if (:stock_master.max_stock!=:copy_stock_master.max_stock) then
	        	begin
	        	  insert into stock_upd_hist values 
	        	    (sysdate,:stock_master.matcode,'MA',:copy_stock_master.max_stock,:stock_master.max_stock,:parameter.logid);
	        	  standard.commit;
		   	   
		   	      exception 
 	            when others then message(sqlerrm);
		   	   	end;
       			end if;
	  	
	  					  				
	  next_record;	
	  	
	  end loop;	
	  	
	  	
	  
	  end;	
	  else
	  	raise form_trigger_failure;
	  end if;
	  
end;
 
			

Open in new window

0
Fekrat El WehediInfrastructure Architect / Oracle DBACommented:
In the code you have i don't seem to be able to see where are you updating the rec_count. So i think before or after the following code "next_record;" you have to add "rec_count = rec_count + 1;"  I hope this will solve your problem.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

shruti27Author Commented:
hey thanks done that but still getting the other two errors:
FRM-40400: Transaction complete:1 records updated
FRM-40352: Last record of query retrieved
any way to get rid of them?
0
Fekrat El WehediInfrastructure Architect / Oracle DBACommented:
Try removing the "+1" from the ":total_rec.rec+1"
0
shruti27Author Commented:
still the error FRM-40400: Transaction complete:1 records updated is there
moreover if i remove the +1 my purpose is not solved...i want to check all the records and by removing +1 my last record gets ommitted.
0
Fekrat El WehediInfrastructure Architect / Oracle DBACommented:
0
Fekrat El WehediInfrastructure Architect / Oracle DBACommented:
If you still want to hide it then i would suggest to substitute the code(s) "when others then message(sqlerrm);" with "when others then null;". But i don't think that would be considered good practice.
0
Jinesh KamdarCommented:
Use a FOR loop as below. Also, the Transaction Complete is a Note and not an error message that I believe appears because of the commit_form call.
declare
	msg integer;
	rec_count number(5) ;
 
begin
	
    set_alert_property('alt_msg',alert_message_text,'Do you want the save the changes now?');
    msg:=show_alert('alt_msg');
    
	  if msg = ALERT_BUTTON1 then
	  begin
	  	commit_form;
	  	
--	  	rec_count := 1;
	  	go_block('stock_master');
	  	first_record;
	  	
--	  	while rec_count < :total_rec.rec+1
	  	for rec_count in 1 .. total_rec.rec
                  loop
	  		
	  				if (:stock_master.ann_req!=:copy_stock_master.ann_req) then
	        	begin
	        	 	insert into stock_upd_hist values 
	        	    (sysdate,:stock_master.matcode,'AN',:copy_stock_master.ann_req,:stock_master.ann_req,:parameter.logid);
	        	  standard.commit;
		   	   
		   	      exception 
 	            when others then message(sqlerrm);
		   	   	end;
	  				end if;
	  				
	  				if (:stock_master.min_stock!=:copy_stock_master.min_stock) then
	        	begin
	        	  insert into stock_upd_hist values 
	        	    (sysdate,:stock_master.matcode,'MI',:copy_stock_master.min_stock,:stock_master.min_stock,:parameter.logid);
	        	  standard.commit;
		   	   
		   	      exception 
 	            when others then message(sqlerrm);
		   	   	end;
       			end if;
	  	
	  				if (:stock_master.max_stock!=:copy_stock_master.max_stock) then
	        	begin
	        	  insert into stock_upd_hist values 
	        	    (sysdate,:stock_master.matcode,'MA',:copy_stock_master.max_stock,:stock_master.max_stock,:parameter.logid);
	        	  standard.commit;
		   	   
		   	      exception 
 	            when others then message(sqlerrm);
		   	   	end;
       			end if;
	  	
	  					  				
	  next_record;	
	  	
	  end loop;	
	  	
	  	
	  
	  end;	
	  else
	  	raise form_trigger_failure;
	  end if;
	  
end;

Open in new window

0
Jinesh KamdarCommented:
Sorry, FOR loop will also not work directly, re-posting corrected code. Use this.
declare
	msg integer;
	rec_count number(5) ;
 
begin
	
    set_alert_property('alt_msg',alert_message_text,'Do you want the save the changes now?');
    msg:=show_alert('alt_msg');
    
	  if msg = ALERT_BUTTON1 then
	  begin
	  	commit_form;
	  	
	  	rec_count := 1;
	  	go_block('stock_master');
	  	first_record;
	  	
	  	while rec_count < :total_rec.rec+1
                  loop
	  		
	  				if (:stock_master.ann_req!=:copy_stock_master.ann_req) then
	        	begin
	        	 	insert into stock_upd_hist values 
	        	    (sysdate,:stock_master.matcode,'AN',:copy_stock_master.ann_req,:stock_master.ann_req,:parameter.logid);
	        	  standard.commit;
		   	   
		   	      exception 
 	            when others then message(sqlerrm);
		   	   	end;
	  				end if;
	  				
	  				if (:stock_master.min_stock!=:copy_stock_master.min_stock) then
	        	begin
	        	  insert into stock_upd_hist values 
	        	    (sysdate,:stock_master.matcode,'MI',:copy_stock_master.min_stock,:stock_master.min_stock,:parameter.logid);
	        	  standard.commit;
		   	   
		   	      exception 
 	            when others then message(sqlerrm);
		   	   	end;
       			end if;
	  	
	  				if (:stock_master.max_stock!=:copy_stock_master.max_stock) then
	        	begin
	        	  insert into stock_upd_hist values 
	        	    (sysdate,:stock_master.matcode,'MA',:copy_stock_master.max_stock,:stock_master.max_stock,:parameter.logid);
	        	  standard.commit;
		   	   
		   	      exception 
 	            when others then message(sqlerrm);
		   	   	end;
       			end if;
	  	
	  					  				
	 if rec_count <= :total_rec.rec then  -- Added this
          next_record;
          end if; -- Added this
	  	
	  end loop;	
	  	
	  	
	  
	  end;	
	  else
	  	raise form_trigger_failure;
	  end if;
	  
end;

Open in new window

0
shruti27Author Commented:
Thanks jinesh, the code is working well with the for loop.
but is there any way i can get rid of the "transaction complete" note?
since i am using database block, i have to use the command commit_form!
0
Jinesh KamdarCommented:
Well, for my app, most of the times, the Transaction Completed message appears on the status bar and not as a separate dialog, so i don't know to control it. Unfortunately, I don't think COMMIT_FORM provides for any parameters to suppress that message :(
0
shruti27Author Commented:
ok i guess i will work with the present scenario...thanks a lot for help. m accepting ur soln
0
Jinesh KamdarCommented:
Glad to be of help :)
0
shruti27Author Commented:
Hi Vee_Mod, i got your point but how do i make changes in the present scenario cause i have already accepted on soln and given the points too!
0
shruti27Author Commented:
thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.