Home » Developer & Programmer » Forms » cursor is misbehaving like save records into database (oracle 10g)
cursor is misbehaving like save records into database [message #581482] Mon, 08 April 2013 02:37 Go to next message
oraclehi
Messages: 40
Registered: July 2012
Location: India
Member
i have 2 buttons in my form as: SHOW and PROCEED. first i click on SHOW, that retrive data from table TB1(OL_LCY_NDC_OCC) through cursor, then i click on PROCEED button, which is only responsible for saving data in tables TB2(OL_LCY_REFERENCE) and TB3(OL_LCY_NDC), it has nothing to do with TB1 as per my coding.

but problem is when i click on SHOW and then on PROCEED then PROCEED button is misbehaving as it again save all record in TB1, which makes my record duplicate in table TB1.
it gives message as FRM-40400:Transactin complete: 1 record applied and saved

SHOW button coding:
cursor c1 is select qtr_type, b.locality, sector, block, qtrno, area, c.hid, form_no, acc_status, doi, doo, dov
	    from house_mirror a, locality b, OL_LCY_NDC_OCC c 
	    where a.lcode=b.lcode and a.hid=c.hid and form_no in 
	    (select form_no from OL_LCY_NDC where aan=:select.aan and event_id='ACL') order by doi; 

select count(*) into cnt from OL_LCY_NDC_occ 
      where form_no in (select form_no from ol_lcy_ndc where aan=:select.aan and event_id='ACL');
if cnt > 0 then
go_block('OL_LCY_NDC_OCC');
     clear_block(no_validate);
       first_record;
         open c1;
            loop
	  fetch c1 into
	 :OL_LCY_NDC_OCC.qtrtype,
	 :OL_LCY_NDC_OCC.locality,
	 :OL_LCY_NDC_OCC.sector,
	 :OL_LCY_NDC_OCC.block,
	 :OL_LCY_NDC_OCC.house_no,
	 :OL_LCY_NDC_OCC.area, 
	 :OL_LCY_NDC_OCC.hid,
	 :OL_LCY_NDC_OCC.form_no,
	 :OL_LCY_NDC_OCC.acc_status,
	 :OL_LCY_NDC_OCC.doi,
   :OL_LCY_NDC_OCC.doo,
	 :OL_LCY_NDC_OCC.dov;
	 
	 exit when c1%notfound;
			 next_record;
	      end loop;
	       close c1;
         first_record;
end if;  

--------------------------------
PROCEED button coding:
Declare
	cnt number;
	form_ndc number;
 -- your varchar2(50);

Begin
--	:global.ur:= get_application_property(username);

	-------UPDATE OR INSERT INTO TABLE OL_LCY_REFERENCE-------------
--	select count(*) into cnt from ol_lcy_ndc where aan=:select.aan and event_id= 'ACL';

 select count(*) into cnt from ol_lcy_reference where rent_section='ACS' and year=:control.year 
                                                   and volume_no=:control.volume and page_no=:control.page_no;
	if cnt = 0  then
	 	insert into OL_LCY_REFERENCE (form_no, rent_section, year, volume_no, page_no)
	 	values (GAMS. OL_REG_FORMNO_SEQ.NEXTVAL, 'ACS', :control.year, :control.volume, :control.page_no);
	 commit;
	 	
	 
	 	
	 	select form_no into form_ndc from OL_LCY_REFERENCE where rent_section='ACS' and year=:control.year 
                                                   and volume_no=:control.volume and page_no=:control.page_no;
    
    
	 	insert into OL_LCY_NDC (form_no, aan, regno, event_id, doev, status, edt, ludt, username)
	 	values (form_ndc, :select.aan,:select.regno,'ACL', SYSDATE, 'DRAFT', SYSDATE, SYSDATE,'' );
	 commit;  
	 	
	 	go_block('OL_LCY_NDC');
    execute_query;
	 	
	 	go_block('CONTROL');
	 	
	 	message('Now you can proceed with selecting house details '); message('.');                                               
  
 :masterbuttons.section:='ACS';
 :masterbuttons.year:=:control.YEAR;
 :masterbuttons.volume:=:control.volume;
 :masterbuttons.page_no:=:control.page_no;
	 
	else 

	 	message('This Ledger Reference is already recorded. '); message('.');
	 
	end if;
  end;
    
    
	 

	 	


i need your help urgent....Sad

i am uploding my form for your better reference
Re: cursor is misbehaving like save records into database [message #581484 is a reply to message #581482] Mon, 08 April 2013 03:24 Go to previous message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SHOW retrieves records into a form. You chose to use a LOOP and populate - as it appears - data block. As far as Forms is concerned, these records are NEW (i.e. not queried from the database).

PROCEED performs (among other things) COMMIT, which commits records in the "show" block as well.

Why don't you simply query records in the "show" block, using built-in Forms capabilities? That would save you from a lot of pain. Alternatively, make "show" block items non-database.
Previous Topic: how to make button visibleproperty false by clicking on it
Next Topic: deployment of an application forms
Goto Forum:
  


Current Time: Sun Apr 20 12:19:21 CDT 2014

Total time taken to generate the page: 0.10933 seconds