What will happen if we do not close a cursor in PL/SQL

by jamuna
(hyderabad)

From Jamuna, Hyderbad - With Oracle pl/sql if an explicit cursor is opened and we do not close the cursor what will happen? Will the programme run?

The short answer is - it depends!

An explicit cursor can be regarded as a name or a label for a pre-defined query and just as you can run the same query more than once you can open and close a cursor more than once (providing it is in scope).

But opening a cursor doesn't actually return any data from the database. It is the FETCH from the cursor that returns the data.

When a cursor is opened, Oracle runs the query to generate the results and positions the cursor before the first row of the result set. However, a cursor can only be opened if it is not already open, attempting to open a cursor that is already open generates a "CURSOR_ALREADY_OPEN" exception.

In other words if you declare a cursor and open it, if you try to open it again withouth closing it, Oracle raises an exception.

Let's look at another possible scenario - assuming that you've opened the cursor and fetched the results from it and have finished with the cursor, what happens then?

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. If you have a large number of open cursors you might exceed the Oracle database initialization parameter OPEN_CURSORS which is the limit for the maximum number of open cursors per session, or your database might run out of memory. Either of those could obviously cause major problems for an application.

In other words, if nothing else, it is good practice to close your cursors in your PL/SQL programs once you've finished with them. 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.