Calling Web Services from OWB 10gR2
By David Allan-Oracle on May 18, 2007
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;
5. For my example I created a table of stadium names in order to make an interesting call on the generated table function;
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;
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.
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.
Change the name of the operator for readability;
Add the input attribute for the function being used (this is taken from the dependent function):
Add the output attribute; this will be the type returned by the table function (you can assume OBJECT_VALUE here):
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.
For simplicity let�s create a new table and write all of the records returned to this table;
Just map from the output group of the expand operator to the new table operator�s group:
Then create and bind the new table;
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:
Now add the STADS source table and map the NAME attribute into the table function�s input:
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):
Previewing the code we see the following, looks good:
After executing the mapping we see the following content in the target:
Here is the procedure definition that we are using to initialize the HTTP proxy:
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 :).