16 Creating and Consuming Web Services in Warehouse Builder

Advantages of Web Services

Web services provide the following advantages:

• Support Service Oriented Architecture (SOA)

• Enable sharing of application functionality between application developers

• Enable you to build services that invoke data integration processes

• Enable you to build applications faster because you can reuse application logic created by others

About Web Services in Oracle Warehouse Builder

Warehouse Builder supports Web services integration using the SOAP and WSDL standards, and thus can be fully integrated into SOA-based enterprise architectures. Developers experienced with Warehouse Builder can create and leverage Web services-based solutions in their data integration designs using their existing tools, code, and skillset. For example, you can integrate your ETL design into larger solutions based on products such as Oracle BPEL Process Manager.

Warehouse Builder supports the following Web service-related functionality:

• Publishing Warehouse Builder ETL jobs as SOAP-based Web services, which can then be invoked or consumed by other systems.

  See "About Publishing Web Services".

• Calling Web services that expose functionality created outside of your Warehouse Builder ETL design.

  See "About Consuming Web Services".

These two areas of functionality enable Warehouse Builder-based designs to participate fully in SOA-based solutions.

About Public Web Services

Public Web services are accessible across the workspace in which they are defined and are not limited to a particular project. Public Web services are created under the Public Application Servers node of the Globals Navigator.

Public Web services can only be based on URLs, not on Warehouse Builder objects. Creating public Web services enables you to create Web services based on existing WSDL files.

Creating Web Service Packages

A Web service package is a container for a set of Web services. Use Web service packages to group a set of related Web services. A Web service package does not have any location associated with it and uses the same location details of the Application Server module that contains it.

You can create Web service packages only in the Projects Navigator and not in the Globals Navigator.

To create a Web service package:

1. Expand the project node under which you want to create a Web service package.

2. If you have not already done so, create an application server module to contain the Web service package.

   See Also:

   Oracle Warehouse Builder Sources and Targets Guide for more information about creating application server modules

3. Expand the application server node under which you want to create the Web service package, right-click Web Service Packages, and then select New Web Service Package.

   The Create Web Service Package dialog box is displayed.

4. Enter the following details in the Create Web Service Package dialog box.

   • Name: The name of the Web service package

   • Description: An optional description for the Web service package

Creating Web Services Based on Warehouse Builder Objects

Use the Projects Navigator to create Web services based on Warehouse Builder objects.

To create a Web service based on a Warehouse Builder object:

1. In the Projects Navigator, expand the project node and then the application server node under which you want to create a Web service.

2. Right-click the Web Services node and select New Web Service.

   To create a Web service under a Web service package, in the Projects Navigator, right-click the Web service package and select New Web Service.

   The Create Web Service Wizard is displayed.

3. On the Welcome page of the wizard, click Next.

4. On the Name and Description page, provide details as described in "Naming the Web Service" and click Next.

5. On the Implementation page, provide details as described in "Defining the Web Service Implementation" and click Next.

6. On the Review Specification page, review the details that you entered in the wizard. To modify any values, click Back. To complete the definition of the Web service, click Finish.

   Click View Source to view the WSDL code that will be generated by Warehouse Builder to implement this Web service.

The Web service is created and added to the navigator tree.

Alternatively, you can quickly create a Web service by right-clicking the object based on which you want to create a Web service and selecting Create Web Service. The Select Application Server or Web Service Package dialog box is displayed. Select the and click OK. The Web service is created and added under the application server node you selected.

Example: WSDL File for a Web Service

The following is an example of a WSDL file created for a Web service.

<definitions
     name="HttpSoap11"
     targetNamespace="http://dbWebService.packaging.sdk.jrt.wh.oracle/"
     xmlns="http://schemas.xmlsoap.org/wsdl/"
     xmlns:tns="http://dbWebService.packaging.sdk.jrt.wh.oracle/"
     xmlns:mime="http://http://schemas.xmlsoap.org/wsdl/mime/"
     xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/"
     xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
    >
    <types>
    </types>
    <message name="FUNCTION_TARGETInput">
        <part name="P1" type="xsd:string"/>
        <part name="P2" type="xsd:string"/>
    </message>
...
...
...

Validating Web Services

When you validate a Web service, Warehouse Builder verifies the metadata definitions and configuration parameters to ensure that they are valid according to the rules defined by Warehouse Builder. When you validate a Web service, the WSDL file associated with the Web service is validated against the W3C WSDL schema. Successful validation ensures that code can be generated to deploy the Web service.

To validate a Web service, select the Web service in the Design Center and click the Validate icon on the toolbar. Or, right-click the Web service in the Design Center, and select Validate.

You can also validate an application server module or a Web service package. This validates all the Web services contained in the application server module or Web service package.

Generating Web Services

Generating Web services creates the code required to deploy the Web service to the associated OC4J or Oracle Application Server location. When you generate a Web service, Warehouse Builder creates a corresponding WSDL file for each Web service. For Web services based on Warehouse Builder objects, an .ear file is also generated. For Web service packages, one .ear file is generated for each Web service.

The generated files are stored in a default location on the file system on which the Design client is installed. You cannot view or edit these generated files.

To generate a Web service, select the Web service and click the Generate icon. Or right-click the Web service and select Generate. You can also generate an application server module or a Web service package, to generate code concurrently for all the Web services contained in the application server module or Web service package at once.

Deploying Web Services

Deploying Web services uses the scripts created during generation to create the Web service in the agent location associated with the application server module containing the Web service.

When you deploy a Web service, the .ear file corresponding to this Web service is located in the OWB_ORACLE_HOME/owb/jrt/applications directory. This directory also contains a separate folder for each Web service deployed to the OC4J instance that is embedded in Warehouse Builder.

To deploy Web services, you need a Control Center Agent (CCA) and an OC4J instance.

You can deploy Web services to:

• OC4J standalone instance

  When you install Warehouse Builder, an OC4J instance is installed with it. You can deploy Web services to this OC4J instance or to an OC4J instance that is part of an Oracle Application Server instance.

• Oracle Application Server

  You can deploy Web services to any Oracle Application Server. Before you do so, create a URI location that points to this Oracle Application Server instance. For more information about creating a location related to an Oracle Application Server instance, see Oracle Warehouse Builder Sources and Targets Guide.

You can deploy Web services either from the Design Center or from the Control Center Manager.

Prerequisites for Deploying Web Services

Before you deploy a Web service based on a Warehouse Builder object, ensure that you:

• Deploy the Warehouse Builder objects on which the Web service is based

• Start the Control Center Agent

  See Also:

  "Starting the Control Center Agent (CCA)" for information about starting the Control Center Agent

Naming and Describing a Public Web Service

Use the Name and Description page to specify the location of the WSDL file that will act as a basis for the Web service. This page contains the following fields:

• Name: Represents the name of the Web service. The name is derived automatically from the WSDL file and you can edit it, if required.

• WSDL File Location: Click Browse to specify the path of the WSDL file. The URL address can be the path of a local file or the URL address of a remote accessible across a network. The WSDL file contains the definitions of the existing Web service.

  This property is displayed only when you use the Globals Navigator to create a Web service based on a URL.

• Description: An optional description for the Web service.

Using the Control Center Manager to Execute Web Services

To execute a Web service using the Control Center Manager:

1. In the Control Center Manager, expand the location node that contains the Web service, select the Web service and click the Start icon. Or right-click the Web service and select Start.

   The Select Operations dialog box is displayed. The Operations list contains the list of operations that you can perform using the Web service.

   Note that if your Web service contains only one operation, the Select Operations dialog box is not displayed.

2. In the Operations list, select the operation that you want to perform and click OK.

   The Input Parameters dialog box that is used to provide the execution parameters for the Web service is displayed.

3. In the Input Parameters dialog box, enter values for the displayed parameters.

   The parameters listed depend on the type of object on which the Web service is based. For example, Web services based on PL/SQL mappings, process flows, and data auditors have the CUSTOM_PARAMS and SYSTEM_PARAMS parameters. Web services based on Code CT mappings contain the parameters OWB_PARAMS.

   • CUSTOM_PARAMS: Represents the values for the mapping input parameters used in the Web service.

     For example, when you create a Web service based on a mapping and the mapping requires input parameters, use the CUSTOM_PARAMS field to enter values for these input parameters. Use commas to separate multiple values.

   • SYSTEM_PARAMS: Represents the values for mapping execution parameters, if any, such as Bulk Size, Audit Level, Operating Mode, Maximum Number of Errors, and Commit Frequency. When you have multiple system parameters, use a comma to separate each parameter.

     For example, OPERATING_MODE=SET_BASED,AUDIT_LEVEL=NONE.

   • OWB_PARAMS: Represents the parameters of the CT mapping on which the Web service is based.

4. Click OK.

The Web service is executed and the results of the execution are displayed in a new log window in the Design Center. The details displayed include the number of rows selected, inserted, updated, or deleted and any errors or warnings that occurred.

Using a Browser to Execute Web Services

Use any browser to execute Web services that were deployed to either the OC4J server embedded in Warehouse Builderor to other OC4J servers.

When you use the OC4J server embedded in Warehouse Builder to access Web services, all the prerequisites for Web service security are provided. You must provide basic authentication before you can execute the Web service.

To execute Web services using a browser:

1. Ensure that the prerequisites, as described in "Prerequisites for Executing Web Services", are satisfied.

2. (Optional) While executing Web services that were deployed to an OC4J server other than the one installed with Warehouse Builder, perform the steps listed in "Setting Up Secure Access on External OC4J Servers".

   Note:

   You may encounter errors while executing Web services that were not deployed to the OC4J server embedded in Warehouse Builder. See "Error Executing Web Services from the Secure Web Site" for information about resolving these errors.

3. Open a Web browser, specify the following URL in the address bar, and press the Enter key.

   http://host_name:8888/jndi_name/webservice

   To execute the Web service securely, use the following URL:

   https://host_name:4443/jndi_name/webservice

   The endpoint page for the Web service is displayed. If you use the AGENTWEBSERVICE Web service provided by Warehouse Builder, the AgentWebService Endpoint page is displayed.

   Here, host_name represents the host name of the computer on which the Web service is stored, and jndi_name is the name of the .ear file generated for the Web service. To execute Web services using the AGENTWEBSERVICE installed with Warehouse Builder, use jrt as the jndi_name.

   The default port numbers used for the Web service are 8888 and 4443. You can use different port numbers.

4. Follow the steps listed in "Performing Operations on Web Services Using a Browser" to execute the Web service.

Performing Operations on Web Services Using a Browser

The Web service AGENTWEBSERVICE, under the AGENT_SERVER node of the Globals Navigator, is an embedded Web service that exposes the Agent server installed with Warehouse Builder as a Web service. The steps listed in this section are performed using the AgentWebService Endpoint page, which is the interface corresponding to the AGENTWEBSERVICE. This Web service is started automatically when the Control Center Agent (CCA) is started. However, you can use other Web service endpoints to perform operations defined by Web services.

1. Select the operation that you want to perform on the Web service and provide the information required to perform the operation.

   Following are some of the operations that you can select when you use AGENTWEBSERVICE.

   • isDeployed

     See "Determining If a Web Service or Application Was Deployed to an OC4J Server"

   • runCCJob

     See "Executing a Control Center Job"

   • abortJob

     See "Terminating an Execution Job"

   • invokeEAR

     See "Running Deployed Applications"

2. Expand the Show Transport Info node and select Enable to the right of the label HTTP Authentication.

3. In the Transport Info section, enter details in the following fields:

   • Username: Name of a J2EE user, with the OWB_J2EE_OPERATOR role, that is used to execute the Web service

   • Password: Password for the J2EE user that you specified in the Username field

4. Click Invoke.

   The Test Result page containing the results of the operation is displayed.

   Note:

   Sometimes, after you enter the credentials and click Invoke, you may be prompted for credentials. Reenter the credentials of the J2EE that you specified in the Transport Info section.

Rules for Using Web Services in Process Flows

In process flows, Warehouse Builder only supports Web services that conform to the following rules:

• Only Web services described through an accessible WSDL file are supported.

• If the WSDL file contains more than one service, then one service must be nominated.

• Web services that have basic authentications need a URI location to provide the credentials. You must set the Deployed Location property of the Web Service activity to this URI location.

• If the Web service contains more than one port then the port must be nominated.

• The port must use the http transport.

Steps to Use Web Services in Process Flows

To use a Web service in a process flow:

1. In the Projects Navigator, create a process flow.

   For more information about creating process flows, see "Steps for Defining Process Flows".

2. Add all the activities, except the Web service activity, that are part of the process flow and establish data flows between them.

3. If you are using an external Web service, import the Web service into Warehouse Builder by creating a public Web service in the Globals Navigator.

   See Also:

   "Creating Web Services Based on a URL" for more information about importing Web services

4. Drag and drop the Web service, either from the Projects Navigator or the Globals Navigator, onto the Process Flow Editor canvas.

   or

   From the Graph menu, select Available Objects. The Add Available Objects dialog box is displayed. Select the required Web service and click OK.

   The Web Service Operation dialog box is displayed.

5. Select an operation from the available operations of the Web service and click OK. The selected operation is used in the process flow.

   The Web service is added to the Process Flow Editor and its properties are listed in the Structure panel.

   The operation in a synchronous Web service has both input and output messages. They will be mapped to input or output parameters of the Web Service activity in the process flow.

6. Provide the required input values for the Web service activity properties. Select the property in the Structure panel, and use the Property Inspector to set values.

   If the Web service needs authentication, create a URI location and set the Deployed Location property of the Web Service activity to this URI location.

7. Establish data flows to and from the Web Service activity.

8. Generate the process flow and resolve any errors that may occur.

9. Ensure that all Web services that you added to the process flow in the form of Web Service activities are deployed.

10. Deploy the process flow package containing the process flow created in Step 1. Use the Control Center Manager or right-click the process flow package in the Projects Navigator and select Deploy.

11. Execute the process flow. Right-click the process flow in the Projects Navigator and select Start.

Synchronizing Web Service Activities with Their Referenced Web Services

When the definition of a Web service is modified, you must propagate these changes to all the process flows that consume this Web service.

To synchronize Web services used in process flows:

1. Right-click the process flow that uses the Web service and select Open.

   The Process Flow Editor for this process flow is displayed.

2. Select the Web Service activity that represents the Web service that has changed. From the Edit menu, select Synchronize.

   The Synchronize dialog box is displayed. The object with which the Web service should be synchronized is selected, and you cannot modify this.

3. Specify the Matching Strategy by selecting one of the following matching options: Match By Object Id, Match By Object Position, or Match by Object Name.

   For details about these options, click Help.

4. Specify the Synchronize Strategy by selecting Replace or Merge.

5. Click OK to synchronize the Web service with the object on which it is based.

The changes made to the Web service are propagated to the Web Service activity that is based on the Web service.

J2EE Roles for Control Center Agent Security

Warehouse Builder provides the following three roles to facilitate Warehouse Builder Control Center Agent security.

• OWB_J2EE_EXECUTOR: Enables grantees to execute mappings in the Control Center Agent (CCA).

• OWB_J2EE_OPERATOR: Includes the OWB_J2EE_EXECUTOR role and enables grantees to access and manipulate audit information.

• OWB_J2EE_ADMINISTRATOR: Includes the OWB_J2EE_EXECUTOR role and enables grantees to administer OC4J and deploy Warehouse Builder objects to the Control Center Agent.

Setting Up Secure Access on External OC4J Servers

You can securely access Web services located on other OC4J servers (that are not embedded in Warehouse Builder). Before you do so, you must set up security on the OC4J server.

Use the following steps to set up secure access on other OC4J servers:

1. Create a key store with an RSA private/public key pair using the keytool utility.

   The following example uses the RSA key pair generation algorithm to generate a key store that resides in a file named mykeystore.jks and which has a password of 123456.

   %keytool -genkey -keylag RSA -keystore mykeystore.jks -storepass 123456
   

   The keystore option sets the file name where the keys are stored. The storepass option sets the password for protecting the key store. If you omit the storepass option, you will be prompted for the password.

2. You are prompted to enter a key entry password. In OC4J 10.1.3.x implementations, the key store password must be the same as the key entry password.

   The mykeystore.jks file is created in the current directory. The default alias of the key is mykey.

3. If you do not have a secure-web-site.xml file, create one in the following location: ORACLE_HOME/j2ee/home/config.

   To start, copy whatever content you need from default-web-site.xml. This typically includes the following subelements under the <web-site> element:

   • <web-app> (for each Web application that you want to secure)

   • <access-log> (for logging; confirm that this specifies an appropriate log file)

   • <default-web-app>

4. Update secure-web-site.xml with the following elements:

   • Update the web-site element to add secure="true" and to set the port number to some available port. For standalone OC4J, use the HTTP protocol, which is the default setting. To use the default of 443, you must be a super user.

     When you set protocol="http" and secure="true", the HTTPS protocol is used.

     The following is an example of a <web-site> element.

     <web-site port="4443" secure="true" protocol="http"
     display-name="Default Oracle OAS Containers for J2EE Web Site">
     ...
     ...
     </web-site>
     

   • Add an entry under the web-site element to define the key store and its password as follows:

     <ssl-config keystore="your_keystore" keystore-password="your_password" />

     Here, your_keystore is the path to the key store—either absolute, or relative to ORACLE_HOME/j2ee/home/config (where the Web site XML file is located)—and your_password is the key store password.

5. Save the changes to secure-web-site.xml.

6. Enable the secure Web site by adding the secure Web site to the server.xml file located in OWB_ORACLE_HOME/owb/jrt/config directory.

7. Restart the OC4J server to ensure that the previous changes are applied.

8. If they are not yet created, create the OWB_J2EE_EXECUTOR, OWB_J2EE_OPERATOR, and OWB_J2EE_ADMINISTRATOR roles.

   See the file system-jazn-data.xml file located in the OWB_ORACLE_HOME/owb/jrt/config folder.

9. Create the J2EE user used to execute Web services and grant the OWB_J2EE_EXECUTOR role to this user.

Updating the Key Store Password

For the OC4J server that is embedded in Warehouse Builder, you are provided with a secure key store for using SSL with Web services. This key store is available in the serverkeystore.jks file in the OWB_ORACLE_HOME/owb/jrt/config folder.

The default password for this key store is welcome. Use the JAVA_HOME/bin/keytool to change the password. Or just replace the key store with a newly created key store using JAVA_HOME/bin/keytool.

Example: Publishing Mappings as Web Services

Company A stores the sales details in a table called ORDERS. The PRODUCTS table stores details about products. The mapping LOAD_TOTAL_SALES_MAP transforms source data and loads the details of total sales into the target table TOTAL_SALES. As part of the data integration requirement, the business processes of Company A and Company B must be integrated.

Publishing the mapping LOAD_TOTAL_SALES_MAP as a Web service will enable the functionality defined in the mapping to be accessed remotely, without dependency on the location, data format, or provider's platform.

Company A uses the following tables to store data:

• ORDERS: contains the columns order_id, order_date, product_id, quantity, and customer_id

• PRODUCTS: contains the columns product_id, product_name, product_desc, and product_price

• CUSTOMERS: contains the columns customer_id, first_name, last_name, cust_address, and cust_city

Use the following steps to publish the LOAD_TOTAL_SALES_MAP as a Web service.

1. In the Projects Navigator, create an application server module called INTEGRATION_AS_MOD that will contain the Web service you are creating. Ensure that the location details of this module are set to the agent location to which the Web service will be deployed.

2. Expand the Oracle module that contains the LOAD_TOTAL_SALES_MAP mapping.

3. Right-click the LOAD_TOTAL_SALES_MAP mapping and select Publish as Web Service.

   The Select Application Server or Web Service Package dialog box is displayed.

4. Select INTEGRATION_AS_MOD and click OK.

The Web service called WS_LOAD_TOTAL_SALES_MAP is created and deployed to the agent location associated with the application server module INTEGRATION_AS_MOD.

Example: Consuming Web Services in Process Flows

Company B uses a CT mapping called LOAD_TOT_SALES_CT_MAP to load aggregate sales during a specified period to the target table TOT_SALES. This mapping is similar to the mapping used by Company A. However, a CT mapping is used because the source tables ORDERS, PRODUCTS, and CUSTOMERS are stored in a SQL Server database.

Because Company B is located in China, the source tables store the sales figures in Chinese Yuan. However, because Company A and Company B have been merged, the sales head wants to see the consolidated sales of both companies in a common currency, U.S. Dollar. You must now convert the sales figures of Company B to U.S. Dollar.

To determine the conversion rate, you can use an external Currency Converter Web Service. This Web service takes two input parameters, From Currency and To Currency. Its output is the multiple that must be used to convert the From Currency to the To Currency. Because this is an external Web service, you must first import this Web service into Warehouse Builder.

Steps to Consume a Web Service in a Process Flow

1. Modify the LOAD_TOT_SALES_CT_MAP Code Template (CT) Mapping.

2. Import the Currency Converter Web Service.

3. Create a Process Flow That Consumes the Currency Converter Web Service.

Create a Process Flow That Consumes the Currency Converter Web Service

Use a process flow to establish the order in which objects are executed and to use the output of an object as input to another. The process flow loads the target table with sales figures for Company B in U.S. Dollar.

Figure 16-1 displays the process flow that loads the TOT_SALES table with the sales figures for Company B in U.S. Dollar.

The Web service CONVERTSERVICE_CONVERSIONRATE is executed first and its output is the conversion value that should be multiplied to a value in Chinese Yuan to convert it to U.S. Dollar. This value is provided as the input to the Mapping Input Parameter operator in the LOAD_TOT_SALES_CT_MAP CT mapping, represented by the Mapping activity CMAP1, that represents the conversion value.

Figure 16-1 Process Flow that Consumes a Web Service

Description of "Figure 16-1 Process Flow that Consumes a Web Service"

Example: Integrating Warehouse Builder Web Services with Oracle BPEL Process Manager

You can integrate ETL functionality developed using Warehouse Builder with products such as Oracle BPEL Process Manager. This is achieved by publishing ETL objects as Web services that can be consumed by Oracle BPEL Process Manager.

Oracle BPEL Process Manager provides a comprehensive and easy-to-use solution for designing, deploying, and managing BPEL Processes.

Scenario

The sales head of Company A wants to evaluate the sales performance of both Company A and Company B over a specified time period. Both company A and Company B have their own processes to determine sales performance over a specified period. The need is to integrate these processes so that you have an easy way to determine the total sales, during a period, for the combined company.

Before You Integrate ETL Functionality with Oracle BPEL Process Manager

Company A uses the mapping LOAD_TOTAL_SALES_MAP, which is published as the Web service WS_LOAD_TOTAL_SALES_MAP. Company B uses the Code Template mapping LOAD_TOT_SALES_CT_MAP and a process flow to convert the sales in Chinese Yuan to U.S. Dollar. Ensure that you publish this process flow as a Web service. Also ensure that you publish any objects associated with these Web services.

Steps to Integrate With BPEL

1. Start the Oracle SOA Suite.

2. Start JDeveloper BPEL Designer

3. Create a BPEL process called BPEL_INTEG. This process uses the Web services created by Company A and Company B.

   Figure 16-2 displays the process BPEL_INTEG.

   Figure 16-2 BPEL Process that Uses Web Services

   
   Description of "Figure 16-2 BPEL Process that Uses Web Services"
   
   

4. Deploy the BPEL process by right-clicking the BPEL process in the Applications Navigator, selecting Deploy, and choosing the deployment location.

   You can use the Log panel to view the deployment results.

5. Login to BPEL Manager using a web browser.

6. On the Dashboard tab, select BPEL_INTEG in the Deployed BPEL Processes section.

7. Select the Initiate tab.

8. Enter values for the input parameters START_DATE and END_DATE using the Input field and click Post XML Message to run the BPEL process.

   For example, enter the following value in the Input field: START_DATA=2007-01-01,END_DATA=2008-12-31.

9. View the audit trail for the Business Process Execution. Select the Instances tab of the BPEL Console and click the Flow link.