When we open a cursor it has to be closed, or else there will be memory leak in the system global area. What is this memory leak? Under what circumstances does it occur and how is it overcome?

    Requires Free Membership to View

A memory leak occurs when a program allocates memory for a data structure and then never releases that memory and does not reuse that memory. The next time the program needs the data structure, it allocates more memory again. Over time, the available memory appears to be shrinking and this is called a "memory leak." Memory leaks are overcome by ensuring the memory allocated for a data structure is deallocated when you are done using that data structure.

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.

While Oracle does automatically close cursors, it is still a very good idea to close them in your code. You should close your cursor when you are done looping through the cursor's contents. This way, you free up some memory for your next cursor. Plus, closing cursors is good programming style.

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: