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  |
 |
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 #575296 is a reply to message #575294] |
Tue, 22 January 2013 01:13  |
|
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 |
 |
Littlefoot
Messages: 21813
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 |
|
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 #576173 is a reply to message #576146] |
Fri, 01 February 2013 03:03 |
|
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 |
|
Littlefoot
Messages: 21813
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.
|
|
|
|
Goto Forum:
Current Time: Tue Jul 02 13:38:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
