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 in Oracle can be regarded as a name or a label for a predefined 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 still 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 and then attempt to open the cursor again without closing it, Oracle raises an exception as in the following example.

declare
   cursor most_recent_hire is
       select first_name||' '||last_name
       from employees
       where hire_date=(select max(hire_date) from employees);
       last_hired_emp most_recent_hire%ROWTYPE;
begin
   open most_recent_hire;
   fetch most_recent_hire into last_hired_emp;
   open most_recent_hire;
end;


When we run the above piece of code it generates the Oracle PL/SQL error
ORA-06511: PL/SQL: cursor already open


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. Note that if you are using an explicit cursor, Oracle does not return an error if you attempt to fetch more records than were returned by the cursor. Therefore if you are fetching records in a loop you must check (one way or another) that the most recent fetch returned data otherwise the loop will never exit! Ways to avoid this are to use a cursor for loop, use a bulk collect statement or check that the fetch returned data by using the %FOUND or %NOTFOUND cursor attributes.

There are other potential issues lurking with the use of explicit cursors. 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, even though Oracle will age out old (least recently used) cursors. If you don't, nothing may happen for a while but your Oracle database applications could just slowly grind to a halt or even suddenly stop working.

For more on PL/SQL see these questions and answers How to convert a local PL/SQL table to table I can select from and what is a PL/SQL ref cursor? and our PL/SQL tutorials

If you're looking for more formal training on Oracle, why not take a training course with one of our partners? See our Oracle training page for more details..

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

Average Rating starstarstarstarstar

Click here to add your own comments

Sep 14, 2017
Rating
starstarstarstarstar
Nicely put NEW
by: Indrani

Good summarised way of sharing a latent concept not much known

Jun 12, 2015
Rating
starstarstarstarstar
Explanatory
by: Jani

As said in the below comment, really u covered almost all my doubts in a single paragraph. Thanks for that ,,, . Really appreciated.

Oct 11, 2012
Rating
starstarstarstarstar
hi
by: Anonymous

Your answer to this problem is too good and useful and u have covered almost every aspect . its good to get answers when u are searching on the whole internet and getting nothing ,
Dhanywad

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.