java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

Question

I m getting the below SQL exception and I don't know what's the root cause for this exception? I am also closing db connection and statement too.

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded

Following is my code:

 while(true)
 {

   Statement stmt2 = conn1.createStatement();
   ResultSet rs2 = null;

   int rec_count=0;
   rs2 = stmt2.executeQuery("select count(*) as cnt from   some_table");                                  
    while(rs2.next())
   {
     rec_count = rs2.getInt("cnt");
   }

   if(rec_count>0)
   {
     update_qry_b_trg1 = "update some_table set to_be_triggered=1,algo_status='D',dealer_id='HD001',price_trig_date=sysdate where buy_sell = 'SELL' and ordertype = 'BNLD' and to_be_triggered = 0 and algo_status = 'P' and Mod(group_ref_no,5)="+th_id;

   String final_qry = "BEGIN \n"+update_qry_b_trg1+";\n"+";\n END;";

   int rows = stmt1.executeUpdate(final_qry);
   stmt1.close();
   }

   rs2.close();
   stmt2.close();

   }

You are vulnerable to SQL Injection. Please read about prepared statements to fix this. — Ben
the code you've pasted doesnt appear to have any cursor leaks. i'd suggest you check v$open_cursor on the DB when this is running to determine what is happening. Ben's point is very valid too, th_id shouldn't be just pasted in there. also if you're only looking for 1 row in the table dont just count(*) at least add where rownum = 1 to cut down the work you do. — DazzaL
why is your update query in wrapped in BEGIN and END ? i think your update query is getting executed as a PROCEDURE and since your while loop is infinite its causing the problem. — Sajan Chandran

Hiren Hiren · Accepted Answer · 2013-04-25T17:13:22

Whereever stmt1 is initialized, it is a better idea to close it in a finally block. In your case you are closing it in an if condition. if the condition doesn't pass, the statement will remain open and you will get this

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

Also you have this running in a while loop, so you need to make sure, you close each and every Statement which is opened.

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

1 Answers