Calling Web Services from OWB 10gR2

Consuming a web service in OWB where the web service produces a document? Read on... This entry details how to incorporate a web service into an OWB mapping.

There are a number of ways to execute web services from within the database and therefore from with OWB 10gR2:
·    perform all the work using UTL_HTTP
·    utilize Jpublisher which does a lot of work to interface SQL to the web service
·    utilize the UTL_DWS package that allows generic web service consumption

This example leverages the JPublisher code that generates object types, tables of objects and table functions from a WSDL file related to the 2006 World Cup (this was a topical example from the summer based on a World Cup web service, see WSDL here). It is similar to the example on OTN (see here) but includes how to consume a web service that produces a document.

1. Download JPub 10.2 from OTN (here) - on Linux edit the jpub script for local env.
2. Download DBWS zip for 10.2 (here)
3. The demo uses a web service providing information from the 2006 World Cup, the WSDL was copied to my local machine for reference, this contains complex types with collections etc.
4. Publish PLSQL wrapper and proxy code for the WSDL into the schema (tgt_102), also generated table function proxies;

./jpub -user=<user>/<password> "-sysuser=sys/<passwd>" "-compile=true" "-proxyopts=tabfun" "-proxywsdl=http://<host>:<port>/footballpoolwebservice.wsdl"
If you use the "-proxyopts=tabfun,noload" and remove -user and sysuser, you can simply generate the code independent of a schema - the code is not loaded into the server, but is generated. The code includes SQL scripts with the type definitions, functions and table functions and also Java code that must be loaded into the database that binds the SQL, Java and Web Service worlds.
5. For my example I created a table of stadium names in order to make an interesting call on the generated table function;
create table stads (name varchar2(256));
insert into stads values ('Olympiastadion');
insert into stads values ('Zentralstadion');

The example built in OWB is one to retrieve all of the stadium information for the tables listed in the table STADS above. From SQL*Plus the query looks like;
exec sys.utl_dbws.set_http_proxy('');
  r.arg0 name,
  r.res.iSeatsCapacity_ capacity,
  r.res.sCityName_ city
from table(JPUB_PLSQL_WRAPPER.to_table_stadiuminfo(cursor(select name from stads))) r;

There are options for supplying PLSQL package names (rather than getting JPUB_PLSQL_WRAPPER), and also java package names for the proxies (the default is genproxy).

Let�s see how we leverage this from OWB.....

Import the generated package or just the types and table types. Table function support is loosely coupled in OWB 10gR2 so there is not a great value in reverse engineering the package if you need the table functions (since the names have to be manually entered in the table operator), but certainly the types etc. are used so ensure all the types and table types are imported.
WS Import:
Also import the table STADS that has been created above, this will be used in the mapping.

Create a mapping then add a table function operator to the mapping. You will have to enter the table function name in the operator�s property panel.
WS TF Props:

Change the name of the operator for readability;
WS TF Name:

Add the input attribute for the function being used (this is taken from the dependent function):
WS TF Atts:

Add the output attribute; this will be the type returned by the table function (you can assume OBJECT_VALUE here):
WS TF Out Atts:

Now add an expand operator for the type returned by the table function and another expand operator for the RES value (which is of type OBJ_TSTADIUMINFO), here we are expanding into the structure for each row returned by the web service.
WS Map1:

For simplicity let�s create a new table and write all of the records returned to this table;
WS Map2:
Just map from the output group of the expand operator to the new table operator�s group:
WS Map3:

Then create and bind the new table;
WS Map4:

We�ll define the source rows for the web service, first let�s define the input to be a ref cursor, this will be based on the STADS source table:
WS Map5:

Now add the STADS source table and map the NAME attribute into the table function�s input:
WS Map6:
For calling web services from the database we must set up the HTTP proxy information, add a pre mapping process that uses the procedure INITIALIZE_PROXY (we�ll define this in a second):
WS Map7:

Previewing the code we see the following, looks good:
WS Map8:

After executing the mapping we see the following content in the target:
WS Map9:

Here is the procedure definition that we are using to initialize the HTTP proxy:
WS Map10:

WS Map11:

I like this web service example, it illustrates producing complex documents and how such documents can be consumed from OWB -  but is missing the Scotland football (or soccer) team :).


Post a Comment:
  • HTML Syntax: NOT allowed

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.


« April 2014