[Running 8.2.0 on Suse 8.1]
I'm building a stored procedure that simulates cars arriving in a car
park, and staying for different amounts of time, based on historical
data.
So, first I define a cursor that's populated by querying last year's
bookings for the period that I'm interested in. This is about 6,500
bookings.
The sp then looks at the first booking and compares it to how many cars
are already in the car park. If adding that booking would violate the
maximum space in the car park, it excludes the booking. Otherwise, it
increments the number of cars in the car park on the days of the
booking, and then cycles through to the next booking.
However, it fails to process more than 10 bookings before exiting with
status 0. I couldn't understand what was going on, so I tried
modifying the procedure to add another input parameter. This then
limits the query that populates the cursor to a single booking
reference. I then tried running this sp for the next booking after the
last successful one in the first stored procedure.
This went in fine. As did the booking after that, and the booking
after that. I'd rather not run this sp 6,500 times, particularly as my
analysts want to use this stored procedure multiple times, against
different scenarios for space in the car park. What should I be
looking for to diagnose the error here? Could there be an issue with
the space available for the stored procedure, such that the cursor
can't read in all the rows?
