Friday, July 13, 2007

SOAP in SQL: Invoking external Web Services

What the Heck?

Have you ever felt the need to federate data from multiple sources, including Web services (e.g., stock price, scientific data, policy tables, tax tables, weather information)?

How would you invoke business operations implemented as external Web services, such as placing new orders, orders shipment, credit card payment transactions (and getting valid authorization numbers) from within the database?


Well, you need to turn your database into a Web Services Consumer.

Do you need to dive into the Web Services protocols, APIs and jargon beforehand?
No, magic JPublisher remove this pain from your neck! DBAs, PL/SQL bigots, and database developers in general don't need to know anything about WSDL, SOAP, UDDI, SOA to call a Web services from SQL or PL/SQL.


How does it work?

4 simple steps
1) Web Service enable your database: see the Web Services Callout utility guide
2) identify the target Web Services and save the location of the WSDL and the endpoint where
it is listening to requests. Examples: the Google Web Service is identified by its wsdl
WSDL: http://api.google.com/GoogleSearch.wsdl and is listening to requests @
Endpoint: http://api.google.com/search/beta2

A simple way to check that a Web service is up and listening to requests at the endpoint in
question is to direct your browser at the endpoint URL; you shoud, receive the following
message:

SOAP RPC Router
Sorry, I don't speak via HTTP GET- you have to use HTTP POST
to talk to me.


3) Ask JPublisher to generate everything needed to invoke the Web services from within the
database

C:\>jpub -u scott/tiger -sysuser=scott/tiger
-proxywsdl=http://api.google.com/GoogleSearch.wsdl
-proxyopts=tabfun,soap
-httpproxy=www-proxy.us.oracle.com:80
-endpoint=http://api.google.com/search/beta2 -dir=tmp


4) Invoke an operation (e.g., Spell Check) of the Web services directly from SQL

SQL> select jpub_plsql_wrapper.dospellingsuggestion( '', 'Nelson Mandelo')
as GOOGLE_Spell_Check_Web_Service
from dual;


That's it!

You can learn more in the following Oracle Database docs: JPublisher, the Java developers guide and also in chapter 16 of my book.

Have fun!