Ask the Expert

When did Oracle begin implicitly closing cursors?

In your response regarding cursor memory leaks, you said: "It used to be that if you did not close a cursor, that memory stayed allocated and unusable to anything else. This is no longer the case with Oracle. If you open a cursor and forget to close it, Oracle will close the cursor for you when the PL/SQL block ends execution. This assumes your PL/SQL block does reach an end."

Do you know with what version Oracle started implicitly closing cursors at the end of PL/SQL block execution? Also, when this happens implicitly, does this mean that your cursor is no longer contributing to the max_open_cursor count, meaning if there are 49 open cursors (and yours is one of them) when Oracle implicitly closes your cursor there will be 48 open cursors? Thanks so much for your help.

    Requires Free Membership to View

I do not recall the exact version in which Oracle implicitly started closing cursors. But I know for sure that this happens in Oracle 8i and higher. I do not recall having memory leaks in Oracle as far back as early Oracle 7 days, but I am not as sure of that. Hopefully, you are using a supported version of Oracle which means your cursors are being closed implicitly.

Once a cursor is closed, it no longer contributes to the max open cursor count. Keep in mind that there are other cursors aside from your explicit CURSOR definition in a PL/SQL block. A simple SELECT statement opens an implicit cursor in the Oracle database, which does count against the max cursor count. This implicit cursor will be closed automatically for you once the SELECT statement is done being processed.

This was first published in March 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: