My Oracle Support Banner

ORA-01000: Maximum Open Cursors Exceeded using PL/SQL Cursor based on a Select Statement Containing a Cursor (Doc ID 1474211.1)

Last updated on AUGUST 30, 2017

Applies to:

PL/SQL - Version 11.2.0.2 and later
Information in this document applies to any platform.
****Checked for Relevance on 24-Aug-2017****

Symptoms

The following error occurs when you iterate through a PL/SQL cursor loop that is based on a SQL statement that contains another cursor for each row in the result set.

ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 12

The cursors are not being closed until the outer loop calls close at the end of the PL/SQL cursor loop.  If the SQL statement contains more rows than the value of the datatabase initialization parameter OPEN_CURSORS, then this error will occur.

Sample Code

SET serveroutput ON

DECLARE
  CURSOR my_cursor IS
    SELECT level, CURSOR(SELECT dummy FROM dual) FROM dual CONNECT BY level < 1000;
  l_level        USER_TABLES.TABLE_NAME%TYPE;
  l_outer_count  PLS_INTEGER := 0;
  l_inner_count  PLS_INTEGER := 0;
  l_dummy        VARCHAR2(20);
  c_dummy        SYS_REFCURSOR;
BEGIN
  OPEN my_cursor;
  LOOP
    FETCH my_cursor
      INTO l_level, c_dummy;
    EXIT WHEN my_cursor%NOTFOUND;
    l_outer_count := l_outer_count + 1;
    DBMS_OUTPUT.PUT_LINE('Outer Loop Count: ' || l_outer_count || ', Level: ' || l_level);

    l_inner_count := 0;
    LOOP
       FETCH c_dummy
         INTO l_dummy;
       EXIT WHEN c_dummy%NOTFOUND;
       l_inner_count := l_inner_count + 1;
       DBMS_OUTPUT.PUT_LINE('Inner Loop Count: ' || l_inner_count || ', Level: ' || l_level);
    END LOOP;

  END LOOP;
  CLOSE my_cursor;  -- Closes all cursor resources here (c_dummy and my_cursor)
END;
/

Results

Database initialization parameter OPEN_CURSORS = 300

Outer Loop Count: 1, Level: 1
Inner Loop Count: 1, Level: 1
Outer Loop Count: 2, Level: 2
Inner Loop Count: 1, Level: 2
...
Outer Loop Count: 296, Level: 296
Inner Loop Count: 1, Level: 296
Outer Loop Count: 297, Level: 297
Inner Loop Count: 1, Level: 297
declare
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 12

SQL>

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.