Weblogic 10.3, JDBC, Oracle, SQL - Table or View does not exist

Question

Hi, I've got a really odd issue that I've not had any success googling for.

It started happening with no changes to the DB, connection settings, code etc.

Problem is, when accessing a servlet, one of the EJB's is doing a direct SQL call, very simple

"select \n" +
" value, \n" +
" other_value \n" +
" from \n" +
" some_table \n" +
" where some_condition = ? "

That's obviously not the direct SQL, but pretty close. For some reason, this started returning an error stating "ORA-00942: table or view does not exist".

The table exists, and the kicker is if I hook in a debugger, change a space or something minor (not changing the query itself) in the query, and hot-deploy the change, it works fine. This isn't the first time I've run across this. It only seems to happen in dev environments (haven't seen it in q/a, sandbox, or production yet), is not always replicable, and driving me seriously insane.

By not always replicable I mean that occasionally a clean build & redeploy will sometimes fix the problem, but not always. It's not always the same table (although if the error occurs it continues with the same query).

Just throwing a feeler out there to see if anybody has run into issues like this before, and what they may have discovered to fix it.

Answer 1

Sounds like maybe one connection in your JDBC pool has a problem, which could explain the intermittent nature and that redeploy only sometimes fixes it, as you could end up still using the same connection afterwards. You could try resetting the connection pool instead of redeploying, perhaps. (java weblogic.Admin -url t3://<server_url> RESET_POOL <pool_name>, I think)

You've said there's only one schema, but does that mean only one schema exists or that all the tables are under one schema? Is it possible that you're doing an ALTER SESSION SET CURRENT_SCHEMA somewhere, and when whichever connection that's issued against is returned to the pool and then randomly used for the query later it can't see anything in the main schema any more? That could happen in a package or trigger as well as from the Java side, and could be a 'temporary' change that doesn't get reverted after an exception. Sounds like something that might only exist in a dev environment, too...