What happens if cursor is not closed

Well, if you've finished with the cursor and don't close it, not much happens, unless as noted above you try to open it again or you try to fetch from it again assuming all the data has been read.

However, there are a couple of dangers lurking. Either of those could obviously cause major problems for an application. If you don't, nothing may happen for a while but your Oracle database applications could suddenly stop working or just slowly grind to a halt. Post a Comment. Share to Twitter Share to Facebook. Newer Post Older Post Home. Search This Blog. Daily Visits. Date And Time website clocks.

A common problem you run across in SQL programming is the need to group a series of records that contain bit fields and you need to determi I can also make the argument that in case of error none of those statements will run. So they can't be absolutely relied on anyway. See Aaron's article: sqlperformance. Show 3 more comments. Dirk Dirk 2, 14 14 silver badges 23 23 bronze badges. Can you clarify for how long not deallocated cursor will hold resources?

Updated the answer with some additional info — Dirk. Thanks, Dirk. As Aaron noticed in his answer cursors release behavior depends on whether cursors are local or global. As it is said in MSDN local cursors will be released automatically when go out of scope. What is interesting to me is what resources will be leaked with unreleased cursors besides table locks.

Eclipses Eclipses 1 1 gold badge 8 8 silver badges 17 17 bronze badges. Deallocating a cursor variable is not the same thing as deallocating a cursor. Yes you're right but as written in the official documentation, you don't need to deallocate the variable, so is extra code that you can avoid to use.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast The first ten years of our programming lives. Upcoming Events. Featured on Meta.

Now live: A fully responsive profile. Candidate changes in Moderator Election — review your ballot. Visit chat. If the cursor is declared as local, then it should only stay in scope for the current object but again, this is theoretical, and I haven't done extensive, low-level memory tests to confirm. However how many cursors do you have where cleaning up this syntax is an issue? If you have hundreds of cursors in your system, that is certainly a red flag to me.

As an addendum, I just want to clarify that cursors in and of themselves are not bad. They are often misused and abused, though - implemented in cases where a more efficient, set-based solution could have been implemented, but the person tasked with writing the query could only think procedurally. A few cases where cursors make sense:. Not closing a cursor will keep locks active that it holds on the rows where it is positioned.

Even after closing a reference is kept to the data structures the cursor is using though so it can be reopened These structures are SQL server specific so it is not just memory space or handles or so and depend on what the cursor is actually doing, but they will typically be temporary tables or query result sets.

The aforementioned resources will remain allocated and thus have a negative effect on server performance. The variable is implicitly deallocated when it goes out of scope. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. What happens when you forget to close and deallocate cursor?

Ask Question. Asked 9 years, 7 months ago. Active 7 years, 2 months ago. Viewed 32k times. Improve this question. Mike Mike 2, 3 3 gold badges 22 22 silver badges 29 29 bronze badges. Add a comment. Active Oldest Votes. The general concept, though, should be: when you're done with something, say so. A few cases where cursors make sense: Running totals.