Link to home
Start Free TrialLog in
Avatar of shruti27
shruti27

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Fekrat El Wehedi
Fekrat El Wehedi
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of shruti27
shruti27

ASKER

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

SOLUTION
Avatar of Fekrat El Wehedi
Fekrat El Wehedi
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of shruti27
shruti27

ASKER

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?
SOLUTION
Avatar of Fekrat El Wehedi
Fekrat El Wehedi
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of shruti27
shruti27

ASKER

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.
Avatar of Fekrat El Wehedi
Fekrat El Wehedi
Flag of Canada image
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.
SOLUTION
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of shruti27
shruti27

ASKER

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!
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
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 :(
Avatar of shruti27
shruti27

ASKER

ok i guess i will work with the present scenario...thanks a lot for help. m accepting ur soln
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
Glad to be of help :)
Avatar of shruti27
shruti27

ASKER

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!
Avatar of shruti27
shruti27

ASKER

thanks.