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( '
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!
5 comments:
Kuassi - as you know, there is also the utl_dbws method to consume an external web service. Curious to know advantages/disadvantages between the utl_dbws and jpublisher methods.
Todd
Hi Todd,
UTL_DBWS is a PL/SQL interface to the dynamic invocation as opposed to the static proxy approach however, both use the Java-based Web Services Callout stack.
Kuassi
Hi Kuassi,
Follow up to my question. I am just learning about this technology so bear with me.
I want to eventually consume a BI Publisher web service from a pl/sql package. That package will schedule a bunch of reports to run at a given time with a specific destination. Given that it is a static web service (in my mind, the end point does not change) and you can use either Jpublisher or utl_dbms, are there any performance advantages of one approach over another? I am leaning towards the JPublisher method just because it seems like less code overall and thus less maintenance in the long run. Since I am starting from scratch, however, I was interested in best practice and thus my initial question.
Thanks again.
Todd
Todd,
In your case, a static proxy approach will be faster; UTL_DBWS is more flexible.
Kuassi
Great code, the author is handsome! It seemed to me that you have it too detailed and from this large in size, I think you can reduce it at least twice if you use pseudo-classes and identifiers, for example, I generally recommend watching a video on Youtube on how to shorten any code by almost five times and not cut it its functionality, unfortunately I don't remember the name of this video, but I do remember that it had about 77 thousand of subscribers! I am sure that the owner of this channel are always buy youtube subscribers to wind up their number.
Post a Comment