Oracle Open cursors ora-1000 errors, "Maximum open cursors exceeded."

Question

I've done connection to Oracle Database. Now I'm facing

ORA-01000: maximum open cursors exceeded

I used code to insert data:

public static void OracleJDBC(String Serial_Number, String Charged_MDN) {

     String dataInsertQuery = "insert into CDR_Huawei (Serial_Number, Charged_MDN) values ('" + Serial_Number + "', '" + Charged_MDN + "')";
     String dataSelectQuery = "select * from CDR_Huawei";
     Statement statement = null;

    try {
    statement = connection.createStatement();
    statement.execute(dataInsertQuery);
    //System.out.println("Data Inserted Successfully");

    } catch (SQLException e) {
            e.printStackTrace();
    }
   }

It works only for first 500 records, then I have the error Ora-1000. I have about 6000 records in total. I found some topics saying that configuration should be changed, but I can't change configuration.

Is there another way to solve this error?

write statement.close() ....after executing...it is coming because you are not closing it — Hardik Bhalani, Oct 04 '13 at 09:53
Please. This is not PHP, and creating queries this way is frowned upon even there... Use PreparedStatements. You'll thank the decision later, believe me... — ppeterka, Oct 04 '13 at 09:55

score 3 · Accepted Answer · answered Oct 04 '13 at 09:54

3

Close your statement in a finally block.

try {
    statement = connection.createStatement();
    statement.execute(dataInsertQuery);
} catch (SQLException e) {
        e.printStackTrace();
} finally {
    if (statement != null) statement.close();
}

answered Oct 04 '13 at 09:54

Alex

25,147
6
59
55

score 1 · Answer 2 · answered Oct 04 '13 at 09:56

1

Every time new statement object is generated while you write statement = connection.createStatement()

It is good practice to close the statement after using it...

 statement.close(); after `statement.execute(dataInsertQuery);`

will solve your problem.

answered Oct 04 '13 at 09:56

Hardik Bhalani

863
2
8
24

score 0 · Answer 3 · answered Oct 04 '13 at 12:40

An extra answer to draw attention to ppeterka's comment:

You really should be using PreparedStatements here. The reason is that you are currently sending 6000 unique SQL insert statements to the database. The statements are unique because the values of the insert statements are glued in your statement text. The database will have to parse each unique statement and place it in its shared pool for reuse. But it won't reuse, since they are unique.

Using PreparedStatements where you bind in the values, you'll create only one unique SQL insert statement, which only needs to parse once and won't clutter the shared pool. Your database administrator will thank you for it.

ok thank you, I will search and try it. Best regards! – Ryainad Oct 07 '13 at 10:44 — Ryainad, Oct 07 '13 at 10:44

Oracle Open cursors ora-1000 errors, "Maximum open cursors exceeded."

3 Answers3