intermittent error "no data found" accessing SPROC in oracle database
temApr 10 2013 — edited Apr 15 2013I'm running into an INTERMITTENT error using Java 1.6 connecting to Oracle database 11.2 using GlassFish 3.2 JDBC pool, all on CentOS servers (java app server is separate from database server).
I'm not sure if it is a database or Java problem, but I get the following error from the java program:
Apr 10, 2013 8:35:44 AM com.mycompany.MyClass MySPROC
SEVERE:
stack trace: java.sql.SQLException: ORA-01403: no data found
ORA-06512: at "MYSCHEMA.MY_PACKAGE", line 144
ORA-06512: at line 1
If I run that line 144 of code in Oracle database using SQL Developer, it returns data fine. Also, if I re-run the client program to repeat the same execution of the java code, there is no problem retrieving the data. However, at some time in the future, the same program attempts to retrieve the same data and the error appears intermittently.
Anyone know what could lead to this error? The most logical problem would be if the database table didn't have any data for line 144 sql query, but since it's intermittent, and the database table doesn't change, and the java program variables accessing the database stored procedure do not change (I'm in a development environment and all the data is controlled, so I know there's no changes entering the java program from the client), I think we can eliminate this as the suspect.
Are there other scenarios that lead to this error people have seen that might be happening here?
My Java POJO has the following structure, and I'm not sure how to modify it to see more of the stack trace...
public String MyJavaPOJO(...) throws Exception {
try {
...
} catch (Exception e) {
loggerWrapper.myLogger.log(Level.SEVERE, "\nstack trace: "+e.fillInStackTrace());
} finally {
closeDatabase(var1, cs, rset, conn);
}
return myOutputVariable;
}
Any advice much appreciated.