Skip to content

max open cursors for Stored Procedure that returns nested SYS-REFCURSORs #12

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
jjd29 opened this issue Apr 6, 2017 · 6 comments
Closed

max open cursors for Stored Procedure that returns nested SYS-REFCURSORs #12

jjd29 opened this issue Apr 6, 2017 · 6 comments

Comments

@jjd29
Copy link

jjd29 commented Apr 6, 2017

I am calling a stored procedure via cursor.callproc in an Oracle 12.1.0.2 database that returns a sys_refcursor. There are 16 scalar data elements and two child sys_refcursors within the parent cursor.

My Python program simply attempts to iterate the cursors. For each parent cursor row/iteration, I explicitly create the child cursors, iterate them and close them. I am running into "ORA-01000: maximum open cursors exceeded" when the program attempts to iterate the 101st parent row.

open_cursors is set to 300 in my database. When I monitor v$session, v$open_cursor, etc., I can see that for each parent row/iteration, there is 1 open cursor for the parent and 100 cursors open for each child cursor.

It would seem that cursor.close() is not actually closing the child cursor. I can call the same stored procedure with PL/SQL and iterate the result set without issue. I would prefer to keep the nested cursors. Is there a method to close them as I iterate that actually closes them on the server side?

Environment:
Oracle 12.1.0.2 on RHEL 7
Python 3.5.2 64-bit
cx_Oracle 5.3-12c 64-bit
Windows 7 Enterprise SP1 64-bit client
Oracle 12.1.0.2 Instant Client 64-bit

Example code:

import cx_Oracle

conn = cx_Oracle.connect('user/password@host:port/service')

curs_main = conn.cursor()

p1 = 2016

l_cur_main = curs_main.var(cx_Oracle.CURSOR)

try:
    curs_main.callproc('My_Package_Pub.get_my_data', [p1, l_cur_main])
except cx_Oracle.DatabaseError as exc:
    error, = exc.args
    print("Oracle Error Code:", error.code)
    print("Oracle Error Message:", error.message)
    exit (1)

for mc in l_cur_main.getvalue():
    print(mc[0],mc[1],mc[2],mc[3],mc[4],mc[5],mc[6],mc[7],mc[8],mc[9],mc[10],mc[11],mc[12],mc[13],mc[14],mc[15])
    
    curs_ro = conn.cursor()
    l_cur_ro = curs_ro.var(cx_Oracle.CURSOR)
    l_cur_ro = mc[16]
    for ro in l_cur_ro:
        print('\t',ro[0])
    curs_ro.close()

    curs_rq = conn.cursor()
    l_cur_rq = curs_rq.var(cx_Oracle.CURSOR)
    l_cur_rq = mc[17]
    for rq in l_cur_rq:
        print('\t\t',rq[0])
    curs_rq.close()

curs_main.close()
conn.close()
@anthony-tuininga
Copy link
Member

Can you provide the contents of the packaged procedure? I'd like to make sure that I can replicate the situation without making any guesses. The code to iterate over each child cursor doesn't need to be that complicated, I believe -- and that may be part of the problem -- but I'd like to confirm with the code you are using.

@jjd29
Copy link
Author

jjd29 commented Apr 19, 2017

Sure thing. I have anonymized the PL/SQL code, but the structure is identical:

PROCEDURE get_my_data (p_input      IN INTEGER,
                       p_recordset  OUT SYS_REFCURSOR)
AS

BEGIN

   OPEN p_recordset FOR
      SELECT col1 "col1",
             col2 "col2",
             col3 "col3",
             col4 "col4",
             col5 "col5",
             col6 "col6",
             col7 "col7",
             col8 "col8",
             col9 "col9",
             col10 "col10",
             col11 "col11",
             col12 "col12",
             col13 "col13",
             col14 "col14",
             col15 "col15",
             col16 "col16",
             CURSOR(SELECT t1.t1col1 FROM child_table1 ct1 WHERE ct1.header_id = mt.header_id) "child1_col",
             CURSOR(SELECT t2.t2col1 FROM child_table2 ct2 WHERE ct2.header_id = mt.header_id) "child2_col"
        FROM master_table mt
       WHERE colx = p_input
    ORDER BY col1,
             col2,
             col3,
             col4;

END get_my_data;

The first child cursor is usually very shallow with only 1 - 15 integer values. The second child cursor can vary between 1 - 250 integer values.

@anthony-tuininga
Copy link
Member

Thanks. How many rows are in the master table?

@jjd29
Copy link
Author

jjd29 commented Apr 19, 2017

There are a little over 2 million rows in the parent table.

Also, I just ran updated queries on the child data tables. The first child cursor will return an average of 3 integers for every parent row and the second child cursor will return an average of 5 integers for every parent row. There are a couple of outliers in the second child cursor that will return 2500 integers.

Thank you!

@anthony-tuininga
Copy link
Member

I am able to replicate the problem with 5.3 but not with 6.0b1. Perhaps you can try that version and confirm? I will look into 5.3 further tomorrow.

anthony-tuininga added a commit that referenced this issue Apr 20, 2017
@anthony-tuininga
Ensure that multiple fetches of nested cursors allow the nested curso…
753cf72 
…rs to be

closed (#12).
@anthony-tuininga
Copy link
Member

This issue has now been corrected on the v5.x branch using the same approach used on the master branch (released in 6.0b1).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@anthony-tuininga @jjd29