Functions [message #345320] |
Wed, 03 September 2008 00:01  |
Derek N
Messages: 80 Registered: September 2002
|
Member |
|
|
I have a strange problem with a function. This function worked fine and now is giving me hasssles witha few accounts that the user is processing.
I have a package that call other procedures and functions from other packages. ie.: z1.calc_fin_year;
z1.calc_no_days;
z3.get_batch_no; (function)
z1.insert_document;
In the z3.get_batch_no function, it selects the batch_number + 1 from the batches table into a variable. The next statement updates the batches table with batch_number +1 and then returns the variable.
This function worked fine for many months. The user now complained that when processing a few accounts he gets an error regarding constraints when using this application. I checked the constraints and the values are all corect. After investigation I discovered that the first two procedures run fine. When the get_batch_no is run, it selects the next batch number then does the update of the batches table and does not return to the next procedure. I then commented out the update of the batches table and it now returns to the next procedure after the function.
Can we have a select and update of the same table in a single function? What else could be causing this intermittent problem.
|
|
|
Re: Functions [message #345322 is a reply to message #345320] |
Wed, 03 September 2008 00:13   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Quote: |
it selects the batch_number + 1 from the batches table into a variable
|
use sequence.
Your problem is that you have not considered that when multiple user will call the same function at same time.They can get the same value for the variable
Regards,
Rajat
[Updated on: Wed, 03 September 2008 00:15] Report message to a moderator
|
|
|
Re: Functions [message #345324 is a reply to message #345320] |
Wed, 03 September 2008 00:48  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | Can we have a select and update of the same table in a single function?
|
Yes.
Quote: | What else could be causing this intermittent problem.
|
Concurrent batches.
As already been said: use a sequence.
Regards
Michel
|
|
|