A cursor is a name used to access a specific private SQL area.

Oracle creates implicit cursors for all SQL DML (data manipluation language) statements, even for select statements returning one row.

If your query returns more than one row, you can explicitly declare a cursor in PL/SQL to process the rows one by one.

Here is a simple example of using a cursor in a ‘cursor for loop’ (which automatically opens, fetches from and closes the cursor for you) :

Your client application may hit the following error :

This means that your application’s session has hit the database-wide limit of the maximum number of open cursors which one session is allowed to have.

The database parameter which governs this limit is : open_cursors

You can change this parameter in real-time, but you should ask yourself the question if the value is really too low, perhaps the application code should be rewritten, to either do better housekeeping (close cursors when they are done), or rewrite the code so that many DML statements can be recoded to be used in one cursor.

Never set this to an excessive value, you want to keep open cursors in check.
Rather let a new, untuned application hit this error so that the code can be tuned.

Setting this value higher does not cause performance overhead on the database.

If you do decide to change the value , this is how to do it :
(the default value of 50 is usually too small, so you will probably change it sooner or later).

How to find out which cursors are open for a session ?

Get the session’s sid from v$session, then get the sql_text from v$open_cursor :

Recommended reading for more information on oracle cursors :

Oracle online documentation: Oracle® Database Application Developer’s Guide – Fundamentals