We are using Oracle (v10.2) procs as data sources for our reports (Crystal 11).
When running reports one right after the other we will often have the db throw an exception back to the Java component saying, "ORA-01000: maximum open cursors exceeded
ORA-06512: at "<proc_name>", line 12
ORA-06512: at line 1"
We've tried increasing the number of avaliable cursors to a large amount (greater than 1000) and this has only reduced the number of occurences of the issue. We have also tried to explicitly close any open cursors with the same name before the cursor is opened within the proc:
CREATE OR REPLACE PROCEDURE <owner>.<proc_name> ( resultCursor IN OUT RPT_CURSOR_PACKAGE.Rpt_Curr , project_id IN NUMBER , start_date IN VARCHAR2 , end_date IN VARCHAR2 , segments IN VARCHAR2 ) AS ... IF resultCursor %ISOPEN then CLOSE resultCursor ; END IF ; OPEN resultCursor FOR ' SELECT <columns> from <tables> ' ; END <proc_name> ;
But this doesn't seem to help either.
Can anyone suggest how we can resolve or get around this issue?
Thanks in advance for your time and help!
JMR