How to invoke an OWB mapping or process flow as a web service

Oracle Warehouse Builder permits a developer to quickly and easily design a process to move data from a data source into the Oracle database. The implementation can be a data flow from different sources into the Oracle database, a mapping in Warehouse Builder's terms, or a combination of individual data flows with dependencies on eachother and other activities, a so-called process flow. Warehouse Builder provides a graphical design environment to design these, and through code generation the code is deployed into the target environment. The traditional interfaces to invoke the deployed mappings and process flows include the Warehouse Builder deployment manager, SQL*Plus (using an execution template), a batch scheduler such as Oracle Enterprise Manager, or programmatic access via APIs in PL/SQL. This technical note describes how another API interface - web services - can easily be added on top of these deployed mappings and process flows, thus making them accessible to J2EE (tm) and .NET applications.

Note: the remainder of this article does assume a basic level of knowledge of Oracle Warehouse Builder and web services.

The architecture for the solution looks as depicted in figure 1. In principle, any device that can communicate with a web service interface can be used to invoke mappings and process flows generated by Warehouse Builder.

Figure 1: Warehouse Builder code exposed as a web service.

This note describes a business case on how to invoke a mapping as a web service. The business case describes a solution that leverages Warehouse Builder's data quality (in this case, address cleansing) capabilities, but of course countless other business cases could be used, leveraging other technology Warehouse Builder makes accessible. In this technical note two implementations will be discussed: one single-record implementation and one multiple-record implementation. Notice that nothing has been done to optimize these examples for multi-request situations and/or specific performance requirements.

• The single-record business case
  • The mapping
  • Invoking the mapping through PL/SQL
  • Warehouse Builder Deployment
  • Publishing the package as a web service
  • Invoking the web service
• The multi-record business case
  • The mapping
  • Invoking the mapping through PL/SQL
  • Warehouse Builder Deployment
  • Publishing the package as a web service
  • Invoking the web service
• Deployment to an application server
• Conclusion

If you prefer to read the materials offline, then you can download them here (1,204 Kb).