Home » Developer & Programmer » Forms » problem with insertion in database (10g form developer)
problem with insertion in database [message #575290] Tue, 22 January 2013 00:30 Go to next message
oraclehi
Messages: 41
Registered: July 2012
Location: India
Member
i have a simple insert statement in oracle form, which is sucessfully run in oracle database(sql). but it is in oracle form trigger: WHEN BUTTON PRESSED as in this format:

Declare
	cnt number;
	
begin
	select count(*) into :control.cnt from ol_lcy_ndc where aan=:control.aan and event_id= 'ACL';
	 if cnt = 0  then
	 	insert into ol_lcy_ndc (form_no, aan, regno, event_id, doev, status, edt, ludt, username)
	 	values (12345, 255257,10030661,'ACL', SYSDATE, 'DRAFT', SYSDATE, SYSDATE, ' ');
	 else
	 	update ol_lcy_ndc set LUDT= to_date('09-09-2009','DD-MM-YYYY') where aan=:control.aan and event_id= 'ACL';
   end if;
end;


but after giving count in cnt, it is not doing anything like insert or update from oracle form, but both the statements are correctly execute in oracle database.
may problem is linked with some properties of property palette, upto my knowledge i checked: insertion allowed--> yes.
Re: problem with insertion in database [message #575292 is a reply to message #575290] Tue, 22 January 2013 00:53 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How do you know that it doesn't work?

Did you COMMIT at the end of the transaction? If not, and if you tried to check what happened (using SQL*Plus, for example), no wonder you can't see any changes.

[Updated on: Tue, 22 January 2013 00:54]

Report message to a moderator

Re: problem with insertion in database [message #575293 is a reply to message #575292] Tue, 22 January 2013 00:55 Go to previous messageGo to next message
oraclehi
Messages: 41
Registered: July 2012
Location: India
Member
i am checking it in database after every execution
Re: problem with insertion in database [message #575294 is a reply to message #575293] Tue, 22 January 2013 01:04 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So - did you COMMIT in a form before checking?
Re: problem with insertion in database [message #575296 is a reply to message #575294] Tue, 22 January 2013 01:13 Go to previous messageGo to next message
oraclehi
Messages: 41
Registered: July 2012
Location: India
Member
yes i also checked it by
	select count(*) into :control.cnt from ol_lcy_ndc where aan=:control.aan and event_id= 'ACL';
	 if cnt = 0  then
	 	insert into ol_lcy_ndc (form_no, aan, regno, event_id, doev, status, edt, ludt, username)
	 	values (12345, 255257,10030661,'ACL', SYSDATE, 'DRAFT', SYSDATE, SYSDATE, ' ');
	 else
	 	update ol_lcy_ndc set LUDT= to_date('09-09-2009','DD-MM-YYYY') where aan=:control.aan and event_id= 'ACL';
	 end if;
	 commit;


but it is not working.
Re: problem with insertion in database [message #575303 is a reply to message #575296] Tue, 22 January 2013 01:45 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, as COUNT always returns some value (different from NULL), :CONTROL.CNT will be >= 0.

Suppose it is 0 - INSERT statement inserts constants so there's no reason for it to fail.

If CNT is different from 0, UPDATE *might* fail if there are no records that satisfy the WHERE condition. However, as UPDATE and SELECT share the same WHERE clause, it should also work.

I might be overlooking something, but - if you are connected to the same schema with both Forms and SQL*Plus, I have no idea what might be wrong here. Perhaps you should run a form in debug mode and trace its execution. Debugger will allow you to see form items' values, variables, ... everything you need to know. So, have a look. Hopefully, you'll find a culprit.

P.S. Do you, by any chance, have WHEN OTHERS exception handler in your code? If so, remove it and run the form again. What happens?

[Updated on: Tue, 22 January 2013 01:47]

Report message to a moderator

Re: problem with insertion in database [message #576142 is a reply to message #575303] Thu, 31 January 2013 23:59 Go to previous messageGo to next message
oraclehi
Messages: 41
Registered: July 2012
Location: India
Member
thanks for your help, now i got the problem as i have commit it after if-else-endif; thats why it was not working. the correct way was commit it to just below the insert statment like
	select count(*) into cnt from ol_lcy_ndc where aan=:select.aan and event_id= 'ACL';
	 if cnt = 0  then
	 	insert into OL_LCY_NDC (form_no, aan, regno, event_id, doev, status, edt, ludt, username)
	 	values (GAMS. OL_REG_FORMNO_SEQ.NEXTVAL, :select.aan,:select.regno,'ACL', SYSDATE, 'DRAFT', SYSDATE, SYSDATE, ' ');
	 	commit;
	 else
	 	update ol_lcy_ndc set LUDT= sysdate where aan=:select.aan and event_id= 'ACL';
	 	commit;
	 end if;
Re: problem with insertion in database [message #576146 is a reply to message #576142] Fri, 01 February 2013 00:14 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you say so. Though, doesn't make much sense. Once the IF does its job (either INSERT or UPDATE), it exits and the next statement is executed - and that was COMMIT (as in a message #575296).
Re: problem with insertion in database [message #576173 is a reply to message #576146] Fri, 01 February 2013 03:03 Go to previous messageGo to next message
oraclehi
Messages: 41
Registered: July 2012
Location: India
Member
now i'm inserting in a new table(OL_LCY_NDC_OCC). i have a datablock based on this table through i am inserting data in that table by this code:
Declare
	cnt number;
	cnt_f number;

Begin
	
	-------UPDATE OR INSERT INTO TABLE OL_LCY_NDC-------------
	select count(*) into cnt from ol_lcy_ndc where aan=:select.aan and event_id= 'ACL';
	 if cnt = 0  then
	 	insert into OL_LCY_NDC (form_no, aan, regno, event_id, doev, status, edt, ludt, username)
	 	values (GAMS. OL_REG_FORMNO_SEQ.NEXTVAL, :select.aan,:select.regno,'ACL', SYSDATE, 'DRAFT', SYSDATE, SYSDATE, ' ');
	 	commit;
	 else
	 	update ol_lcy_ndc set LUDT= sysdate where aan=:select.aan and event_id= 'ACL';
	 	commit;
	 end if;
	  
 --------INSERT INTO TABLE OL_LCY_NDC_OCC --------------------
	  
	  go_block('OL_LCY_NDC_OCC');
     last_record;
       next_record;
	 :OL_LCY_NDC_OCC.qtrtype:= :CONTROL.qtr_type;
	 :OL_LCY_NDC_OCC.locality:= :CONTROL.locality;
	 :OL_LCY_NDC_OCC.sector:= :CONTROL.sector;
	 :OL_LCY_NDC_OCC.block:= :CONTROL.block;
	 :OL_LCY_NDC_OCC.house_no:= :CONTROL.qtrno; 
	 :OL_LCY_NDC_OCC.form_no:= cnt_f;
	 :OL_LCY_NDC_OCC.hid:=:CONTROL.hid;
	 :OL_LCY_NDC_OCC.doi:=:CONTROL.doa;
	 :OL_LCY_NDC_OCC.doo:=:CONTROL.doo;
	 :OL_LCY_NDC_OCC.dov:=:CONTROL.dov;
	 :OL_LCY_NDC_OCC.acc_status:=:CONTROL.acc_status; 
	 commit_form;

  go_block('control');
		clear_block(No_Validate); 
End;





i, m getting error FRM:40508 for datablock OL_LCY_NDC_OCC only.
Re: problem with insertion in database [message #576187 is a reply to message #576173] Fri, 01 February 2013 04:17 Go to previous message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
While running the form and getting the error message, go to Help menu and choose "Display Error" - it will tell you what happened. If you can't fix it, copy/paste the "display error" outcome over here.
Previous Topic: Moving to Next Record from detail table
Next Topic: Date Validation in PRE-INSERT Trigger
Goto Forum:
  


Current Time: Sat Aug 30 05:33:47 CDT 2014

Total time taken to generate the page: 0.05228 seconds