Some companies create database schemas that are either prefixed or suffixed with the environment name. This can cause a problem when using the Oracle Database Adapter to execute a stored procedure. When adding a database adapter to a project, one must specify the schema in which the stored procedure was created. Even if is selected, the schema JDeveloper is connected to the database with will be specified in JCA file that is created.

Initial EmployeeNameService_db.jca

If this gets deployed to another environment where the schema name has changed, it will fail to execute.  

There are multiple options to deal with this issue:

1. Remove the SchemaName property from the JCA file. If the stored procedure is owned by the user the server is connecting to the database with, the SchemaName property can be removed from the JCA file and the adapter will execute the stored procedure in the schema the database adapter is connecting as.

   Modified EmployeeNameService_db.jca – SchemaName property removed

    

2. Use a configuration plan. The SchemaName property can be changed using a SOA Configuration Plan.

   Configuration plan snippet

   ...
   HR_PROD412120
   ...

3. Change the policy. If you can influence the policy, you could have all the schema names changed to match.