Kbase P70789: Q1: Is it possible for an exception to occur within the sto
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/03/2004 |
|
Status: Unverified
GOAL:
Q1: Is it possible for an exception to occur within the stored procedure that prevents the call to close the cursor from occurring?
GOAL:
Q2: If the answer to #1 is "yes", are "try/catch/finally" blocks allowed in stored procedures that would allow for the close of the cursor to be placed in a "finally" block, ensuring that it is being called?
GOAL:
Q3. Is this a common error that has a fairly common solution to it?
GOAL:
Q4. If exceptions occur within the compiled stored procedure, is there a tracing log of some sort that the stored procedure wrappers write to that we could look at that may shed some light on our problem?
GOAL:
Q5. Is there a "recommended" setting for the number of open cursors parameter or a formula for calculating their optimum value?
FIX:
A1: An SQL-92 stored procedure runs as a true Java function (with wrappers generated by SQL). So most ordinary Java rules and execution models apply. Hence, an exception certainly can occur and it is possible for an exception to occur within the stored proc that prevents the call to close the cursor from occurring?
A2. The Try/catch blocks are just ordinary Java, and are certainly "allowed" however, adding a "finally" would not be a good idea because the JVM in the server is shared across all execution threads/connections and it is very difficult to predict when the Java garbage collector runs. Therefore, Progress strongly suggests to never use the "finally" blocks in stored procedures as they could be very dangerous in a Progress SQL-92 server environment.
A3. The error is not common. It is an expected behavior when the code does not properly cleanup by closing each cursor after it serves its purpose. This error strongly suggests the need for code review to ensure that all cursors are properly closed in a timely manner.
A4. There is no special a tracing log or mechanism for the stored procedure errors.
A5. There is no ?recommended? setting or formula for the calculation of the optimum number of cursors to use as that depends on the specific application. The maximum possible is 500. Set the number that you feel you application needs. But more important than that is to ensure that the code does close all open cursors when they are no longer needed.