Resolving ORA-04061 Issue

In this post, let's discuss about fixing ORA-04061:existing state of package has been invalidated issue when calling Database standard procedure/package from SOA web service using database adapter.

Introduction, sometimes the SOA web service start returning following error message during invocation of standard procedure/package.

Error:
ORA-04061: existing state of package "HR.EMPLOYEES_API" has been invalidated
ORA-04065: not executed, altered or dropped package "HR.EMPLOYEES_API"
ORA-06508: PL/SQL: could not find program unit being called: "HR.EMPLOYEES_API"

But when I check the state of package in database, its in valid state and it worked without any issues, when I try to execute the same program unit from database.

Considering above analysis, I concluded that there is no issue with database program unit. Then I started looking into SOA Web service and Weblogic server data source configuration.

Further investigation on SOA web service testing reveals that, the database program unit is getting invoked sometimes without any issues.

During my R&D, I did resetting data source connection pool. From then, I did not faced this issue.

Final Report:
This issue raises, when the database program being altered/created/compiled by another database user and SOA web service tries to invoke it using old data source connection pool i.e. created before altering database program.

Here comes the Weblogic data source connection pooling mechanism, which holds few logical connections created before I alter the database program with different user.

When I reset the data source, it will destroy the existing connections and will re-create them with current state of the database programs and resolves the issue.

Steps to Reset Data Source in WLS:

1. Go to the Weblogic Console > data sources and select your data source. For Ex: LocalXEDS
2. Go to the tab ‘Control’ and select the servers to which the data source is targeted and click ‘Reset’.