Home » Developer & Programmer » Forms » Migration from Forms 6i to Forms 10g - Data update problem (Oracle Forms 10g, Windows XP Professional)
Migration from Forms 6i to Forms 10g - Data update problem [message #409677] Tue, 23 June 2009 08:08 Go to next message
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 Smile
Re: Migration from Forms 6i to Forms 10g - Data update problem [message #409687 is a reply to message #409677] Tue, 23 June 2009 08:46 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
What you've described shouldn't happen - not from a change in forms version at least.
It might help if you posted the code.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12404
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 Go to previous messageGo to next message
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 #409704 is a reply to message #409677] Tue, 23 June 2009 09:43 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Two things you can try:
1) Running the code as anonymous block in sqlplus
2) tracing the database session of the form to see exactly what updates are issued to the db.
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 Go to previous messageGo to next message
Signum
Messages: 13
Registered: July 2008
Junior Member
Thanks for your answer cookiemonster but I found the solution on another forum Razz
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

Re: Migration from Forms 6i to Forms 10g - Data update problem [message #409877 is a reply to message #409677] Wed, 24 June 2009 05:55 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
That'll work but it does suggest there's a bug in your version of forms.
Where current of uses rowid internally.

Might be worth checking on metalink to see if there's any patches you need to apply.
Previous Topic: Running Oracle Reports in Forms
Next Topic: Tree for the forms
Goto Forum:
  


Current Time: Tue Dec 06 02:30:13 CST 2016

Total time taken to generate the page: 0.05714 seconds