What will happen if we do not close a cursor in PL/SQL
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.
cursor most_recent_hire is
select first_name||' '||last_name
where hire_date=(select max(hire_date) from employees);
fetch most_recent_hire into last_hired_emp;
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..