1. The error occurs when using a JNDI (error shows up in the log, and no BPEL instance is created).

2. The error occurs when using MCF properties (instance is created, same error appears).

3. The connection pool and datasources are configured and working correctly.

4. The procedure APPS.CCI_INV_O_21_ITEM_AVAIL_PKG_N.ITEM_CHECK works when invoked from sqlplus using the same values used in our BPEL process.

The ORA-06531 error obviously seems to indicate an error with the package, as it is clearly an error returned from the database. But we just confirmed that it is neither a connectivity issue nor is there anything wrong with the package itself.

Solution:

The DB Adapter deployed was an older version. Get a copy of the newer ‘DBAdapter.rar’ and redeploy it as follows:

opmnctl stopall

cd $ORACLE_HOME/j2ee/oc4j_soa

cp -Rp connectors connectors.old

cd $ORACLE_HOME/j2ee/oc4j_soa/connectors

rm -rf DbAdapter

mkdir DbAdapter

chmod 700 DbAdapter

cp /tmp/DBAdapter.rar $ORACLE_HOME/j2ee/oc4j_soa/connectors/DbAdapter

opmnctl startall

Explanation:
It appears that we have hit Oracle Bug 5954475 (which is related to the DB Adapter) and supposedly fixed by the 10.1.3.3 patchset, as documented in Oracle Metalink Note ID 437825.1.

The ‘DBAdapter.rar’ deployed on this environment was 356,884 bytes in size, while on our other environments was 374,183 bytes.

It seems that when we applied the 10.1.3.3 patchset, it did not fully patch everything. Fortunately, patchsets can be re-applied if a component failed to patch (it is smart enough to determine if a specific component has been patched and ignore it).

In our case, the patchset did update the DB Adapter, but failed during the redeploy phase. When patchsets update BPEL libraries, they will typically do it in the $ORACLE_HOME/bpel/lib directory or $ORACLE_HOME/bpel/system/services/lib directory. Adapters are updated in the /bpel/services/lib directory and are stored there for re-deployment.

Since the ‘DBAdapter.rar’ file in the /bpel/services/lib directory is larger than the one in the /connectors directory, this is an indication that the patchset did not re-deploy it after it patched it.

I did not dig deeper into this to investigate why, but at least our issue was resolved.