Closing cursors in Forms 3

I'm currently working at a client which insists that all select statements in Forms 3 which are only supposed to return one row should use explicit cursors. Their reason is that it avoids the extra fetch to check if more rows could be returned. That's reasonable and, I think, good practice.

Where we differ however is on closing cursors. They say that a procedure should open the cursor, fetch from it then close it. I have this idea that it is actually better not to close the cursor after use if it is likely to be used again

I cannot remember how I came to think this. I think it was writing Pro*COBOL programs back in version 5 and that the logic is this. If the cursor is not closed then the parsed statement remains in memory. The next time it is opened, Oracle does not need to re-parse the statement but only has to re-bind to bring in changed variable values. All open cursors will anyway be closed when the program which opened them ends.

I hope that was clear. Any help on this would definitely be appreciated since I'm going to have to justify what I've done in a code review soon.

Thanks a lot

Ian

Ian Dixon                      |  Email : idixon_at_syntaxis.infocom.co.uk |
Independent Oracle Consultant  |  Tel   : +44 (0)734 478092             |
Reading, England               |                                        |