<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=269747997384049&amp;ev=PageView&amp;noscript=1">

AVIO Insights

Handling Schema Name Changes in the Database Adapter

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 <Default Schema> is selected, the schema JDeveloper is connected to the database with will be specified in JCA file that is created.

 

Stored Procedure Selection Screenshot
Store Procedure Selection Screen

 

Initial EmployeeNameService_db.jca

<adapter-config name=“EmployeeNameService” adapter=“db” wsdlLocation=“../WSDLs/EmployeeNameService.wsdl” xmlns=“http://platform.integration.oracle/blocks/adapter/fw/metadata“>
<connection-factory UIConnectionName=“HRDB” location=“eis/DB/HRDB”/>
<endpoint-interaction portType=“employeeNameService_ptt” operation=“employeeNameService">
<interaction-spec className=“oracle.tip.adapter.db.DBStoredProcedureInteractionSpec”>
<property name=“SchemaName” value=“HR”/>
<property name=“ProcedureName” value=“GET_EMPLOYEE_NAME”/>
<property name=“GetActiveUnitOfWork” value=“false”/>
</interaction-spec>
</endpoint-interaction>
</adapter-config>

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

    <adapter-config name=“EmployeeNameService” adapter=“db” wsdlLocation=“../WSDLs/EmployeeNameService.wsdl” xmlns=“http://platform.integration.oracle/blocks/adapter/fw/metadata“>
    <connection-factory UIConnectionName=“HRDB” location=“eis/DB/HRDB”/>
    <endpoint-interaction portType=“employeeNameService_ptt” operation=“employeeNameService">
    <interaction-spec className=“oracle.tip.adapter.db.DBStoredProcedureInteractionSpec”>
    <property name=“ProcedureName” value=“GET_EMPLOYEE_NAME”/>
    <property name=“GetActiveUnitOfWork” value=“false”/>
    </interaction-spec>
    </endpoint-interaction>
    </adapter-config>

     

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

    Configuration plan snippet

    ...
    <reference name=“EmployeeNameService”>
    <property name=“SchemaName”>
    <replace>HR_PROD</replace>
    </property>
    <property name=“jca.retry.count”>
    <replace>4</replace>
    </property>
    <property name=“jca.retry.interval”>
    <replace>1</replace>
    </property>
    <property name=“jca.retry.backoff”>
    <replace>2</replace>
    </property>
    <property name=“jca.retry.maxInterval”>
    <replace>120</replace>
    </property>
    <binding type=“jca”/>
    </reference>
    ...
  3. Change the policy. If you can influence the policy, you could have all the schema names changed to match.
1 minute read