Does Tomcat use cached versions of pl/sql modules?

Question

Ok imagine this situation. I make a change to a pl/sql module, recompile it and everything is fine. No errors. I then try to access a GUI screen on an application that runs on Tomcat. The screen calls a pl/sql module in an oracle database.

When i submit the form which should have called that pl/sql module for processing the data, i get this error

ORA-20001: ORA-06508: PL/SQL: could not find program unit being called 

I checked all packages in $USER_OBJECTS and there is nothing with a status of INVALID.

I restarted Tomcat and then it starts working. Does this mean that when i recompiled the package the first time i effectively removed some cached reference to the package which Tomcat was using?

The connection to the database is via JDBC and DBCP connection pools. Does the recompile maybe invalidate the connection?

Answer 1

The error you are getting usually indicates that the stored procedure/package either:

1) doesn't exist by the name you are trying to call it with (from your Java class)

2) is missing a grant EXECUTE privilege to the user (defined by the JDBC connection) that is trying to execute it

3) is missing a synonym, if it is being executed via its name only (i.e. without a schema prepended)

4) is being called without the correct number and types of parameters

Answer 2

The screen calls a pl/sql module in an oracle database.

No, it doesn't. Assuming you're running a servlet based web-app on Tomcat then your browser sends a request to Tomcat, which invokes the relevant servlet and then some java class in the web-app will execute a pl/sql program as a CallableStatement.

The key to this is how the java class executes the CallableStatement and whether it retains a reference to it or prepares it each time it executes it. Can you shed any light on this?

Edit:
I beleive the Oracle JDBC driver is capable of performing statement caching, which may be causing the problem. Have a read of the driver docs to get more info.

Answer 3

This issue is with the JDBC connection pools and is an issue you get with all app servers using JDBC connection pools, not just Tomcat. The connection pools will keep a number of connections open in the pool ready for the next request. If a PL/SQL package has been referenced by the connection and is recompiled then the next call to that package will raise an ORA-06508 error. This will affect packages anywhere in the call stack - not just the package you called directly.

To resolve this some app servers (like Weblogic) have a test method that is called periodically. If the test fails the connection is removed from the pool or refreshed in some way. I'm not sure what mechanism Tomcat has.

Another way to address it is to call dbms_session.reset_package as the first method call in your JDBC call. This will clear the package state from you're session. This approach isn't recommended as it has a performance overhead plus any package-scoped variables are reset so package initialisation blocks need to be called again - another performance hit.

If you've got the issue and you haven't got a way of dropping bad connections you'll need to reset the whole connection pool as any connection in the pool will suffer from the same exception.