Spring UncategorizedSQLException: ORA-01012

views:

244

answers:

Spring UncategorizedSQLException: ORA-01012

I am trying to retrieve data form an Oracle database using jdbc (ojdbc14.jar). I have a limited number of concurrent connections when connecting to the database and these connections are managed by Websphere connection pool.

Sometimes when I make the call I see an UncategorizedSQLException exception thrown in my logs with one of the following oracle codes:

ORA-01012 (not logged in) exception
ORA-17410 (connection timed out, socket empty),
ORA-02396 exceeded maximum idle time, please connect again

Other times I get no exceptions and it works fine.

Anyone understand what might be happening here?

In Websphere I have my cache statement size set to 10. Not sure if it is relevant in this situation, when it looks like the connection is being dropped.

+2 A:

It looks like the database is deciding to drop the connection. It's a good idea to write your code in a way that doesn't require that a connection be held forever. A better choice is to have the program connect to the database, do its work, and disconnect. This eliminates the problem of the database deciding to disconnect the application due to inactivity/server overload/whatever, and the program needing to figure this out and make a reasonable stab at reconnecting.

I hope this helps.

Bob Jarvis 2010-01-08 12:09:49

thanks.A couple of comments though, for server overload shouldn't Websphere conection pooling be able manage connections efficiently? For inactivity should i be ensuring that the timeout in my connection pool matches or is less than that of the database timeout?

combi001 2010-01-08 12:22:41

From the look of it pooling isn't helping much. I strongly suggest that you have your application connect, do its work, and disconnect. Don't hold a connection open waiting for your user - he/she/it may decide to walk away, go to lunch, drop dead, or some other form of rudeness. As far as timeouts go, if you follow the connect-do the work-disconnect pattern the timeout won't matter, unless it's so short it prevents your statement from completing. Good luck.

Bob Jarvis 2010-01-09 15:56:03

ansaurus

tags:

views:

answers:

Spring UncategorizedSQLException: ORA-01012

related questions