April 26 2017

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.

About the Author

Bio
Adam has over 10 years of software engineering experience. He has designed and developed with SOA/BPM technologies for more than 7 years.
 
Partner Network Certified Specialist SOA
 

 

Join the Conversation

Enter your first name. It will only be used to display with your comment.
Enter your email. This will be used to validate you as a real user but will NOT be displayed with the comment.
By submitting this form, you accept the Mollom privacy policy.