0

Using JUNIT, I am calling a stored procedure java side several times (lets say 10 times). After some calls (lets say 7 seven times), I have a java.sql.SQLException: ORA-01000: too many cursors open

I have the same number of tests Oracle side in a plsql script : I have no problem when I call this procedure 10 times or 20 times, all of the cursors I used are closed after I use them.

So the problem seem to be java side, though the error is an oracle code (???).

Java side, I call the procedure with a statement and get the resultSet :

cstmt = c.prepareCall("CALL myStoredProcedure(?,?,?,?)");
cstmt.setDate(1, sDate);
cstmt.setString(2, myVar);
cstmt.setString(3, myVar2);
cstmt.registerOutParameter(4, java.sql.Types.ARRAY, "My_TYPE_ITEMS");
cstmt.execute();

...

rs.close();
cstmt.close();

The exception happens on the execute command for sure.

Any idea ?

mlwacosmos
  • 4,391
  • 16
  • 66
  • 114
  • Somewhere in your code you are not closing the `Statement` instances correctly. –  May 12 '20 at 07:22

1 Answers1

0

PL/SQL may close automatically cursors whereas JDBC may not do do it: this can be an explanation. It could also be that Oracle OPEN_CURSORS parameter is just too low.

This is a frequent error with Oracle and has already been explained with many details in java.sql.SQLException: - ORA-01000: maximum open cursors exceeded

pifor
  • 7,419
  • 2
  • 8
  • 16
  • OPEN_CURSOR is 400. It stopped after 7 calls...it is a bit short. – mlwacosmos May 12 '20 at 07:42
  • Do you create a new database session or reuse one ? How many open cursors do you have before you run your test in the database session ? You have to open the "black box" to understand what is going on. – pifor May 12 '20 at 07:48
  • if I check v$session, I just see one session for my user. If I select V$OPEN_CURSOR, it returns 142 rows – mlwacosmos May 12 '20 at 08:04
  • To list the number of open cursors in the current session, try: `select sid, sql_text from v$open_cursor where sid = (select sys_context('USERENV','SID') from dual)`. To check open cursor usage at instance level, see https://docs.oracle.com/cd/E40329_01/admin.1112/e27149/cursor.htm#OMADM5352. – pifor May 12 '20 at 08:08
  • Maybe JUNIT is opening a lot of cursors ? If the test runs quickly it's difficult to check manually with V$OPEN_CURSOR: you need to run another script to monitor V$OPEN_CURSOR or trace JDBC or to run SQL trace on the session: https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof – pifor May 12 '20 at 08:15
  • the test runs slowly because I log a lot, fifteen seconds at least before the exeption happens. Like I said before, 142 cursors open in V$OPEN_CURSOR whatever the user. 400 for OPEN_CURSOR. Having the exception after 8 calls, is very strange. – mlwacosmos May 12 '20 at 09:22
  • The parameter is for a single session but V$OPEN_CURSOR is for all sessions. If you don't see the 400 cursors for your single session during or after the test maybe you have found a bug in Oracle or JDBC but in my experience this is unlikely for this error. What is the Oracle version and JDBC version ? – pifor May 12 '20 at 09:39
  • There are knowns bugs for ORA-1000 with JDBC version 12.1.0.2. – pifor May 12 '20 at 10:11