ORA-1000 Maximum Number of Cursors Exceeded

Error Description:
----------------------------------
Whenever you run any procedures or functions or any other application or transaction either database level or application level you get the following error,

ORA-01000: "maximum open cursors exceeded"

Cause of The Problem:
--------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. To run a application the program needs to open more cursor in a session than the OPEN_CURSORS and error arises.

To process a SQL statement, Oracle opens a work area called a private SQL area. This private SQL area stores information needed to execute a SQL statement. Cursors are stored in this area to keep track of information.

There are two types of cursor named implicit cursor and explicit cursor.

An IMPLICIT cursor is declared for all data definition and data manipulation statements. They are internal to Oracle and is handled by oracle. They are harder to tune because they are internal to Oracle.

But in order to handle the queries that return more than one row, you must declare an EXPLICIT cursor to retrieve all the information. They can be tuned by determining when you will open or close them.

Solution of The Problem:
--------------------------------------
You can solve the ORA-01000 error by tuning cursor usage at the database level and at the application level.

1. Tuning at the DATABASE LEVEL
-------------------------------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. The default value of it is 50 and the value ranges from 0 to 65535. By setting the highest value of it likely solve the ORA-01000 error. Like you can issue,
SQL> ALTER SYSTEM SET open_cursors=5000;
System altered.

SQL> show parameter open_curs
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000


2. Tuning at the APPLICATION LEVEL
----------------------------------------------------------------------
The three parameters that affect handling cursors at the application level are RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS.

The default value of HOLD_CURSOR is NO which means that after Oracle executes a SQL statement the links to the cursor cache, memory, and parse locks are released and marked for reuse.

The default value of RELEASE_CURSOR is NO which means that after Oracle executes a SQL statement, the links to the cursor cache is maintained and not released.

To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and RELEASE_CURSOR=YES.