Handling database package migrations in BPEL

The catch
Recently while working on an integration project for a client, I was told that the custom database packages my BPEL process was referencing needed to be moved to a different database schema. Migration of the packages was promptly done. All that remained was tweaking the BPEL process so that it referenced the packages under the new schema. Since I had used server-side JNDI, my first impression was resetting the data source to the new schema would do the trick. As it turned out, that was not the only thing I needed to do, but a few more.

Recofigure the run-time connection
The first step is to reset the data source so that it points to the new schema. This is fairly easy. Since every data source is tied to a connection pool, it is in essence the connection pool that needs to be changed. To do that, first, log on to the enterprise manager and then modify the existing connection pool by resetting the jdbc url, user name and password. Save the changes. If you insist on creating a new connection pool altogether for better readability, by all means do so. But don't forget to tie it to the data source you are referring to from the JNDI name.

Reset the JCA operation
Go to your BPEL project and open the WSDL files for all the database adapters that were referring to the old schema. Scroll down to the jca:operation tag. You will find something like this:

<jca:operation
SchemaName="APPS"
PackageName="XXALV_BPEL_UTILITY_LIB_PKG" ProcedureName="SUBMIT_JOURNAL_IMPORT" InteractionSpec="oracle.tip.adapter.db.DBStoredProcedureInteractionSpec" > </jca:operation>

Notice that the SchemaName is set to the one you were referring to previously. Reset it to the new schema name. For instance, in my case the new schema is BOLINF. Thus, I will set it as:

...
SchemaName="BOLINF"
...

Leave everything else the same.

Compile and redeploy
You are done. Save the changes and deploy the process. It should now pick up the packages under the new schema.

Gotchas
Most of the time your procedures will call other procedures and functions that may not be under the same schema as your packages are. In such cases your schema will need privileges to perform operations on objects that are outside your schema. Be sure to ask the DBA to grant you the necessary permissions. Also, it is recommended that you qualify all the database objects you create with the schema name so that all references are made explicit.