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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top