The multi-record business case

Even though the single-record business case shows the proof of concept and can satisfy many requirements, it has restricted scalability. A bulk interface, that accepts multiple records and returns multiple records, is more scalable and can satisfy more requirements accordingly. This section describes the multi-record business case. Because the principle of this second example is the same as the business case above certain steps will be described very briefly.

The mapping

Figure 12 below shows the mapping that is used to implement the multi-record business case.

Figure 12. The mapping that implements the multi-record business case.

Notice that compared to the mapping for the first business case (figure 2) the input and output parameters have been removed. All the mapping does is read from a source table and insert into a target table. The mapping is included as MAP_NA_BULK in the MDL file. In this case, the PL/SQL code that calls the wrapper has to do the translation of input into records in the source table.

Invoking the mapping through PL/SQL

In order to make the translation between multiple input values and records in a table, to be called by a web service, you have to use database object types. Please refer to script create_na_bulk_PLSQL.sql for the full code to implement the business case.

create type address_in_type is object
( address varchar2(50)
, city varchar2(50)
, state varchar2(20)
, zip5 varchar2(5)
) ;
/

create type address_out_type is object
( address varchar2(50)
, city varchar2(50)
, state varchar2(20)
, zip5 varchar2(5)
, zip4 varchar2(4)
) ;
/

create type addresses_in is table of address_in_type
/

create type addresses_out is table of address_out_type
/

Next, the PL/SQL wrapper that calls the mapping makes the translation between the object type and the records in the table. In this case, a database function in a package is being used to invoke the mapping. The function returns an object of type ADDRESSES_OUT, which is a table of ADDRESS_OUT_TYPE.

create or replace package na_bulk is

  function invoke (p_address in addresses_in) return addresses_out ;

end na_bulk ;
/

create or replace package body na_bulk is

  function invoke (p_address in addresses_in) return addresses_out
  is

    l_result number ;
    l_audit_id number ;

    cursor c_addresses_out is
    select address_out_type(street_address
    , city
    , state
    , zip5
    , zip4
    ) my_address
    from address_out_table ;

    l_addresses_out addresses_out ;

  begin

    execute immediate 'truncate table address_in_table' ;

    for i in 1..p_address.count
    loop

      insert into address_in_table
      ( street_address
      , city
      , state
      , zip5
      )
      values
      ( p_address(i).address
      , p_address(i).city
      , p_address(i).state
      , p_address(i).zip5
      ) ;

    end loop ;

    commit ;

    run_owb_code( l_result
    , l_audit_id
    , 'RTR'
    , 'WS_TARGET_LOC'
    , 'PLSQL'
    , 'MAP_NA_BULK'
    ) ;

    open c_addresses_out ;
    fetch c_addresses_out
    bulk collect into l_addresses_out ;
    close c_addresses_out ;

    execute immediate 'alter session set current_schema = rt' ;

    return l_addresses_out ;

  end ;

end na_bulk ;
/

Notice that the target user in this example is RT. This package is included in script create_na_bulk_PLSQL.sql.

Warehouse Builder Deployment

Deploy the mapping to the database, and run the script to create the PL/SQL definitions into the Warehouse Builder target schema.

Publishing the package as a web service

In a similar way as described in the first business case, you go through the wizards in JDeveloper in order to define and expose the web service. Please refer to the single-record business case for the details and the steps to go through.

Invoking the web service

Once you have defined the web service, run it in JDeveloper. JDeveloper will again show the URL to retrieve the web service definition. Figure 13 below shows the call to the invoke method of this web service. Notice that San Francisco in the second address is spelled incorrectly.

Figure 13. Invoking the multi-record web service.

Figure 14 below shows the result of invoking the web service.

Figure 14. The results of invoking the web service with multiple records.

The results show the address in Redwood Shores standardized to REDWOOD CITY, and also the typo in San Franccisco has been corrected to SAN FRANCISCO. Again, because you invoke Warehouse Builder generated code, the runtime audit browser will show the execution audit.

This example showed another business case that processed data. Even though not every Warehouse Builder mapping or process flow returns data (or has to return data), it will always return the resulting status after processing. The call to the web service would invoke the mapping or process flow and return the status to the application used to invoke the web service.