ORA-01000: maximum open cursors exceeded

[blarooij]

Dear Lee Lindley,

We plan to use the software to create multiple PDFs in 1 program.
One PDF per case. It concerns thousands of cases.
We use multiple ref cursors per case
We are doing some testing and are running into problems with ORA-01000: maximum open cursors exceeded

  BEGIN
     CLOSE v_src;
  EXCEPTION
     WHEN INVALID_CURSOR
     THEN
        NULL;
  END;

The close cursor does not work for the refcursor

Is this a known problem?

Sincerely

Bessel

[lee-lindley]

I do not pretend to understand the reason, but I've found that it is best to create the sys_refcursor in a separate function. It can be a local function in the declaration of the main procedure, but as long as generation of the cursor is done elsewhere, I've avoided issues with cursor caching. You can then close the cursor in the main procedure. I believe I mention this in the documetnation.

I have no experience with thousands of cursors.
I'm interested in hearing your findings.

[blarooij]

You always get get ORA-01001: invalid cursor when je cursor is closed after the call to PdfGen.refcursor2table.

I you don't call PdfGen.refcursor2table the cursor can be closed.

So:

  v_src := l_getsrc1;
  close v_src;

=> OK!

  v_src := l_getsrc1;
  PdfGen.refcursor2table (p_src =>         v_src
                        , p_col_headers => TRUE
						, p_grid_lines => FALSE);

  close v_src;

=> NOK Always get ORA-01001: invalid cursor

So all ref cursors you use are not closed.
And with many ref cursors you get ORA-01000: maximum open cursors exceeded

[lee-lindley]

I understand the second part of the issue now. I believe there is code that closes the cursor inside pdfgen or maybe inside the package it uses. I put cursor close in an exception block and ignore failure to close it because I wasn't sure it was always closed and wanted a safety net. Closing the cursor is likely redundant, but doing so is a good practice; you just have to trap for the case where it was already closed.

The "ORA-01000: maximum open cursors exceeded" error you are getting seems to mean that closing the cursor does not help with the maximum per session limit. I suspect this is an oracle bug with caching cursors in a session. It does not seem to release a slot when the cursor is closed. You could file a bug report. I wouldn't hold my breath on it.

As for workarounds, I don't see any that don't involve batching your reports into chunks and launching separate session for each chunk.

Good luck.