Home » SQL & PL/SQL » SQL & PL/SQL » Functions (Oracle Database 8i)
Functions [message #345320] Wed, 03 September 2008 00:01 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: differnece b/w bulk collect/forall and bulk collect/for
Next Topic: PL/SQL Dynamic SQL & bind variable question
Goto Forum:
  


Current Time: Sat Feb 15 11:39:37 CST 2025