SQL & PL/SQL

Hello there ...

I am programming a PL/SQL Code that is throwing 0RA-01000 Maximum Open Cursors Exceeded.

Having already read quite a lot about ORA-01000 errors, I know I should be closing cursors, and have already tried setting OPEN_CURSORS parameter to a high number (1000).

I declared a lot of procedures in my pl/sql, each of which uses one cursor since i am working with a non-Oracle table linked by ODBC ... and each procedure sometimes does thousands of inserts -- but all WITHIN the explicit cursors. The explicit cursors are not declared within each loop.

I already checked the code many times, and made sure all open cursors are closed. In addition, I also verified the numberopen cursors generated by the PL/SQL by running the following SQL after every procedure i run... and outputting it... and it appears the value just keeps on increasing, even though I had explicitly closed all the cursors in all the earlier procedures.

What is funny is that the most number of cursors reported by the code below only hits 150+ cursors. Nowhere near the 1000 open_cursors limit per session.

select a.value into strtxt --, b.name         
        from v$mystat a, v$statname b
        where a.statistic# = b.statistic#
        and a.statistic#= 3;

When I run the procedures separately though, all the procedures run smoothly (even when I had not yet updated the open_cursors parameter).

I was thinking of the following, but maybe you have some other ideas?
Does this have anything to do with my procedures not being stored procedures?
Or should i be committing records within my procedures instead of out of it?

I really have run into a wall and would really appreciate any tips or helps on this. Thanks in advance!

My basic pl/sql code looks like below. I did not give the actual details cause it will be too long (up to 5000 lines).

DECLARE 
...

PROCEDURE proc1
IS

    CURSOR cur_hca
       is 
           select ...from..where;
   TYPE cur_hca_fetch
        Is TABLE OF cur_hca%ROWTYPE
            INDEX BY PLS_INTEGER;
            
    temp_collect cur_hca_fetch;
 ... 
BEGIN
 ...  
   open cur_hca;         --cur_hca is the cursor name.
                                  --i use exactly the same cursor name in the other procedures
      ...
      loop
         fetch cur_hca bulk collect into temp_collect LIMIT 1000;
         exit when temp_collect.count=0
  
         for indx in 1 .. temp_collect.count
            loop
              ...run some sql
            end loop;
      end loop;
  close cur_hca;
END proc1;

PROCEDURE proc2   --almost the same as above the only changes are the query for the 
                             -- cursor and the sql that happens for each record
IS
 ... 
BEGIN
 ...  
   open cur_hca;         --cur_hca is my cursor name
      ...
      loop
       
         ...
      end loop;
  close cur_hca;
END proc2;

... up to 40 other very similar procedures


BEGIN
   proc1;
   commit;

   select a.value into strtxt 
        from v$mystat a, v$statname b
        where a.statistic# = b.statistic#
        and a.statistic#= 3;
  DBMS_OUTPUT.PUT_LINE('Number of Cursors After STATUSproc1: ' || strtxt);  


   proc2;
   commit;
   select a.value into strtxt 
        from v$mystat a, v$statname b
        where a.statistic# = b.statistic#
        and a.statistic#= 3;
   DBMS_OUTPUT.PUT_LINE('Number of Cursors After STATUSproc2: ' || strtxt);  
   ...
   ...
   ... 40 other procedures

END;

Edited by: user4872285 on May 6, 2013 6:49 PM

Edited by: user4872285 on May 6, 2013 7:01 PM

Edited by: user4872285 on May 6, 2013 8:02 PM

Edited by: user4872285 on May 6, 2013 8:03 PM