The single-record business case

The first business case describes a mapping that accepts a US address as input variables and returns a standardized, cleansed result including zip+4 (US standard) as output. Below are the steps that you need to go through in order to publish and use the mapping as a web service. This example was built using OWB 9.2.0.4, but it should work with any version greater than 9.0.4.10. The included MDL file is an OWB 9.2 MDL file.

The mapping

Figure 2 below shows a screenshot of the mapping.

Figure 2. The mapping that implements the single-record address cleansing business case.

Warehouse Builder's mapping paradigm today always has to always process one or more records. In order to achieve this for the input parameters the values are combined with a select from the DUAL database table, which contains one and only one record. The filter is being used to combine the attribute from dual and the parameters. The filter condition is 1=1, so the record is always passed through. Into the name and address operator go only the address related input attributes. Name and address output are individual components to be loaded into the ADDRESS_TABLE.Loading type on ADDRESS_TABLE is TRUNCATE/INSERT. Eventually, the post mapping procedure reads the ADDRESS_TABLE and publishes the results as output attributes. The mapping is included as MAP_NA in the MDL file.

Invoking the mapping through PL/SQL

The mapping can be invoked using Warehouse Builder's generic framework for running mappings. In this case, a slightly extended wrapper around the <owb home>/owb/rtp/sql/sqlplus_exec_template.sql template is being used in order to invoke the mapping and pass in the input parameters. Download the standalone procedure here.

The procedure has to be installed into the runtime repository schema. For the business case, the target user was used as the user who invokes the mappings. Generally, a runtime access user runs the mappings. In any case, make sure the grants are correct in order to be able to run the mapping through PL/SQL:

• Grant execute on RUN_OWB_CODE to the user you want to use to invoke the mapping, and in the schema you use to invoke the mapping, create a local synonym RUN_OWB_CODE to point to <runtime repository>.RUN_OWB_CODE.
• Grant execute on <runtime repository>.WB_RT_API_EXEC to the user you use to invoke the mapping.
• If the user is a runtime access user, then grant create procedure to <access user>. This is needed in order to create a package that calls the RUN_OWB_CODE procedure.
• If the user is a target user, then grant the 4 runtime roles to the user in order to promote the target user to a runtime access user: WB_A_<runtime repository>, WB_D_<runtime repository>, WB_R_<runtime repository> and WB_U_<runtime repository>. The target user acts as a runtime access user in this case.

Because the MAP_NA mapping has input and output parameters, you define a specific wrapper that calls the generic wrapper. Because the wrapper is generic, the specific wrapper is fairly straightforward. The code for the wrapper package/procedure can be downloaded here, but is also included below:

CREATE OR REPLACE PACKAGE INVOKE_MAP_NA IS 
 
  PROCEDURE  CALL_MAPPING ( P_IN_ADDRESS IN VARCHAR2
                          , P_IN_CITY IN VARCHAR2
                          , P_IN_STATE IN VARCHAR2
                          , P_IN_ZIP5 IN VARCHAR2
                          , P_OUT_ADDRESS OUT NOCOPY VARCHAR2
                          , P_OUT_CITY OUT NOCOPY VARCHAR2
                          , P_OUT_STATE OUT NOCOPY VARCHAR2
                          , P_OUT_ZIP5 OUT NOCOPY VARCHAR2
                          , P_OUT_ZIP4 OUT NOCOPY VARCHAR2
                          )  ; 
END INVOKE_MAP_NA;
/
     
CREATE OR REPLACE PACKAGE BODY INVOKE_MAP_NA AS 
     
  PROCEDURE  CALL_MAPPING ( P_IN_ADDRESS IN VARCHAR2
                          , P_IN_CITY IN VARCHAR2
                          , P_IN_STATE IN VARCHAR2
                          , P_IN_ZIP5 IN VARCHAR2
                          , P_OUT_ADDRESS OUT NOCOPY VARCHAR2
                          , P_OUT_CITY OUT NOCOPY VARCHAR2
                          , P_OUT_STATE OUT NOCOPY VARCHAR2
                          , P_OUT_ZIP5 OUT NOCOPY VARCHAR2
                          , P_OUT_ZIP4 OUT NOCOPY VARCHAR2
                          ) IS 
    l_result number ;
    l_audit_id number ;

  begin

    rtr.run_owb_code( l_result
    , l_audit_id
    , 'RTR'
    , 'WS_TARGET_LOC'
    , 'PLSQL'
    , 'MAP_NA'
    , '","'
    , 'PI_ADDRESS='
      || p_in_address
      || ',PI_CITY='
      || p_in_city
      || ',PI_STATE='
      || p_in_state
      || ',PI_ZIP5='
      || p_in_zip5
    ) ;

    if l_result = 1
    then

      dbms_output.put_line('Mapping execution was successful') ;

      p_out_address := rtr.wb_rt_api_exec.get_output_parameter(l_audit_id,'PO_ADDRESS') ;
      p_out_city := rtr.wb_rt_api_exec.get_output_parameter(l_audit_id,'PO_CITY') ;
      p_out_state := rtr.wb_rt_api_exec.get_output_parameter(l_audit_id,'PO_STATE') ;
      p_out_zip5 := rtr.wb_rt_api_exec.get_output_parameter(l_audit_id,'PO_ZIP5') ;
      p_out_zip4 := rtr.wb_rt_api_exec.get_output_parameter(l_audit_id,'PO_ZIP4') ;

    elsif l_result = 2
    then

      dbms_output.put_line('Mapping execution with warning') ;

    else

      dbms_output.put_line('Mapping execution failed') ;

    end if ;

    execute immediate 'alter session set current_schema = rt' ;

  end ;


END INVOKE_MAP_NA;
/

Notice that in this case schema RT is being used to invoke the mapping. The wrapper RUN_OWB_CODE that implements the sqlplus_exec_template.sql performs an 'alter session set current_schema = <runtime repository>'. The execute immediate statement at the end of the transformation changes the current schema back to the original schema, so that all procedures etc. can be found based on the current schema again. If you want to use this example for your business case, then you may have to update this value to include the user schema you use.

Warehouse Builder Deployment

All code must be deployed to the database. This includes the mapping and the necessary transformations. If you use your target schema to invoke the mapping, then all code can be deployed from the MDL file.

Publishing the package as a web service

You can use JDeveloper in order to define a web service that calls PL/SQL in the database. Once you have defined a new (empty) project, you can use the New option from the right-mouse popup menu on the project to define a new web service (see below). Notice that this is the 10g version of JDeveloper. JDeveloper 10g is the preferred version, because the earlier versions do not support procedures with output parameters as web services. You can work around this by using a database function that returns the result in the function.

Figure 3. JDeveloper with a new empty project.

Use the PL/SQL Web Service under the Business Tier in JDeveloper (see figure 4).

Figure 4. Exposing a database PL/SQL procedure or function as a web service through a wizard in JDeveloper.

Go through the wizard to point to the database schema, and select the procedure in the package and define the web service (see figure 5 below).

Figure 5. Select the INVOKE_MAP_NA package to be published as a web service.

In the second step of the wizard, make sure to select the CALL_MAPPING procedure to be exposed as a web service (see figure 6).

Figure 6. Select the procedure to be exposed as a web service.

Once you complete the wizard JDeveloper will generate all code you need in order to publish the web service and deploy it to a target OC4J instance. Once you have the code, use the right-mouse button popup menu on MyWebService1 and select Run. JDeveloper will then invoke its own OC4J instance and host the web service (see below).

Figure 7. Running a web service in an OC4J engine through the JDeveloper framework.

Notice the URL, specifically the listener port, in the bottom right panel. This is the URL to the web service.

Invoking the web service

Finally, you can copy and paste the URL from the Embedded OC4J Server panel into your web browser in order to connect to the web service. This link provides you the link to the web service definition.

Figure 8. The generated web service definition.

You can use the link to callMapping in order to invoke the web service. Specify address values...

Figure 9. Invoke the web service through the default interface that JDeveloper generated.

... and click the Invoke button. You will now use the web service to invoke the mapping. The mapping performs address verification and validation using Warehouse Builder's name and address cleansing operator and returns the result in a SOAP message.

Figure 10. The SOAP message showing the result of the mapping execution.

Notice that the address values are standardized to uppercase and Redwood Shores is being replaced by REDWOOD CITY. Also, the ZIP+4 is being returned.

Because the mapping runs in Warehouse Builder's framework, it populates the audit details like you would expect from any other mapping.

Figure 11. Mapping execution statistics in the same way as you get those for any other mapping execution.

This end-to-end example shows how you can interact with a Warehouse Builder mapping in realtime, running as a web service.

This example can be extended to execute any mapping of process flow generated from Warehouse Builder. Of course, not all mappings or process flows return data values. What they do return is the status of execution: successful, with warnings, or error. You could use this example to define a generic framework to execute any mapping or process flow that was generated from Warehouse Builder and return the status after the execution. With that solution the web services API opens up mappings and process flows to any J2EE (tm) or .NET application.