ORA-01000: maximum open cursors exceeded

This ORA error message can occur in different situations. One such situation arises due to a silly mistake when PLSQL invokes a Java method which accesses database.

Let us see how we can reproduce the issue.

1. Create a stored procedure of language Java.

create or replace and compile java source named DBUser as
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
public class DBUser {
    public static String getUser()
    {
        String sql =
            "SELECT user FROM dual ";           
        String myUser="myUser";
        try {
            Connection conn =  DriverManager.getConnection("jdbc:default:connection:");
            for (int i = 0; i< 10000; i++) {
                PreparedStatement pstmt = conn.prepareStatement(sql);
                ResultSet rset = pstmt.executeQuery();
                rset.next();
                myUser = rset.getString(1);
                rset.close();
                /*pstmt.close();*/
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
        return myUser;
    }
}
Please note that PreparedStatement is not explicitly closed.

2. Create a PLSQL function which invokes the Java stored procedure.

create or replace function getDBUser return varchar2
AS LANGUAGE JAVA NAME  'DBUser.getUser()  return oracle.sql.String';

3. Execute the PLSQL procedure in a loop

select getDBUser from dual;

The error message will be reported when the for loops iteration crosses the number of allowed open cursors.The number of allowed open cursors in the system can be checked with the below SQL.

 select value from v$parameter where name = 'open_cursors';


Thus, if the issue is encountered, it is a good idea to check if there are any Java methods which misses the explicit close statements on resultSet, PreparedStatement  etc.. . This is often overlooked by inexperienced Java developers (or PLSQL developers writing one off Java code).