| Migration from Forms 6i to Forms 10g - Data update problem [message #409677] |
Tue, 23 June 2009 08:08  |
Signum
Messages: 13
Registered: July 2008
|
Junior Member |
|
|
Hi everyone.
I have the following problem : I have a form which generates picking slips. In order to do that, there is a button. When we click on it, there is a cursor with a "SELECT" query and with "for update" that looks for picking slips lines entered by the user. For each line, I update the "numberOfPickingSlip" column with the appropriate number of picking slip (generated before the loop of update).
The weird thing is that the form works with Forms 6i but it doesn't work at all with Forms 10g, even though I just recompiled the form (no need for Webutil to run this form)... Only the last line of the picking slip is updated. The code of the procedure is the same for BOTH versions... Nothing has changed. So, it's not a query problem. Even more interesting, if I run the form directly from Oracle Forms Builder and then, clicking on the "generate" button, it works but just once... And the worst cas :, after running the form directly from Forms Builder, loading another form (accessible from a menu above), closing that form and then clicking on the "generate" button, it doesn't work at all and only the last line is updated...
I checked the cursor content and it contains all the lines that have to be updated but for some reason, only the last one is updated...
How is that possible ? Is there anything special that I have to do ?
Thanks for any help 
|
|
|
|
|
|
| Re: Migration from Forms 6i to Forms 10g - Data update problem [message #409690 is a reply to message #409677] |
Tue, 23 June 2009 08:58  |
Signum
Messages: 13
Registered: July 2008
|
Junior Member |
|
|
I agree it shouldn't happen but unfortunately it does...
Here's the code :
procedure gen_pickingSlip is
p_year VarChar2(4);
p_numPickS number;
p_line number := 0;
cursor recup_maj is
select * from prepa
where numPickingSlip is null
for update of numPickingSlip nowait;
recPickSlip prepa%rowtype;
begin
-- Retrieving the new picking slip number
retrieve_num_pickingSlip(numPickS);
-- Year of the picking slip
p_year = to_char(sysdate,'YYYY')
open recup_maj;
loop
fetch recup_maj into recPickSlip;
exit when recup_maj%notfound;
-- Position in the picking slip
p_line := p_line + 1;
--*This is the part where it seems only one update is done*
update prepa
set year=p_year, numPickingSlip=p_numPickS, line=p_line
where current of recup_maj;
end loop;
close recup_maj;
if p_line > 0 then
commit_form;
end if;
synchronize;
end;
[EDITED by LF: applied [code] tags instead of [quote] ones]
[Updated on: Tue, 23 June 2009 13:50] by Moderator Report message to a moderator |
|
|
|
| Re: Migration from Forms 6i to Forms 10g - Data update problem [message #409700 is a reply to message #409677] |
Tue, 23 June 2009 09:19 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
When posting code you should
a) post code that actually compiles (you're missing declarations for recPickSlip and numPickS at least)
b) use CODE tags not QUOTE tags
I can't see anything obviously wrong - I can only assume the cursor is finding less records than you think it should and I doubt that is due to the change in version.
Try putting a message in the loop to see how many records the cursor is finding.
Are these records being created in this form or another?
If it's this one the real problem might be with when the data gets inserted.
Couple of general points:
1) for loops are wonderful things - you should use them.
2) Never use select * unless you are really interested in the contents of every column - you can use select 1.
Actually the commit_form might be the problem - it doesn't do anything unless the datablocks have changed and this code doesn't change the datablocks - try using standard.commit instead.
|
|
|
|
| Re: Migration from Forms 6i to Forms 10g - Data update problem [message #409703 is a reply to message #409677] |
Tue, 23 June 2009 09:36 |
Signum
Messages: 13
Registered: July 2008
|
Junior Member |
|
|
Thanks for your answer.
The cursor found every record that needs to be updated. If I create 5 records, there will be 5 records in the cursor. The cursor has the right number of records and I checked their number, no problem. All these records represents different lines of the picking slip.
The records are created by another form and there is no problem with that. I checked the database to be sure of that.
Nice try with "standard.commit" but it doesn't work either. The same problem occurred.
|
|
|
|
|
|
| Re: Migration from Forms 6i to Forms 10g - Data update problem [message #409860 is a reply to message #409677] |
Wed, 24 June 2009 04:44 |
Signum
Messages: 13
Registered: July 2008
|
Junior Member |
|
|
Thanks for your answer cookiemonster but I found the solution on another forum 
I have to use "ROWID" in order to do the update. Here is the code :
procedure gen_pickingSlip is
p_year VarChar2(4);
p_numPickS number;
p_line number := 0;
cursor recup_maj is
select ROWID from prepa
where numPickingSlip is null
for update of numPickingSlip nowait;
recPickSlip recup_maj%rowtype;
begin
-- Retrieving the new picking slip number
retrieve_num_pickingSlip(p_numPickS);
-- Year of the picking slip
p_year = to_char(sysdate,'YYYY')
open recup_maj;
loop
fetch recup_maj into recPickSlip;
exit when recup_maj%notfound;
-- Position in the picking slip
p_line := p_line + 1;
-- Update the line
update prepa
set year=p_year, numPickingSlip=p_numPickS, line=p_line
where ROWID=recPickSlip.ROWID;
end loop;
close recup_maj;
if p_line > 0 then
commit_form;
end if;
synchronize;
end;
[Updated on: Wed, 24 June 2009 05:25] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
