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!

9 comments:

shahidsidd said...

Nice and knowledgeable sites for everyone-
booksshelf
knowledge
books
liberary
kitaben
Books and references
books
tutorial books

lipitor said...

companies marketing mineral makeups and also get the best bargains in mineral makeup you can imagine,
find aout how to consolidate your students loans or just how to lower your actual rates.,
looking for breast enlargements? in Rochester,
homeopathy for eczema learn about it.,
Allergies, information about lipitor,
save big with great bargains in mineral makeup,

change edition interviewing motivational people preparing second
,

interviewing motivational people preparing second time
,

interviewing people motivational preparing for a second time
,

black mold exposure
,

black mold exposure symptoms
,

black mold symptoms of exposure
,

free job interview questions
,

free job interview answers
,

interview answers to get a job
,

lookfor hair styles for fine thin hair
,

search hair styles for fine thin hair
,

hair styles for fine thin hair
,

beach resort in the philippines
,

great beach resort in the philippines
,

luxury beach resort in the philippines
,
iron garden gates, here,
iron garden gates,
wrought iron garden gates
, here
,
wrought iron garden gates
,
You: The Owner's Manual: An Insider's Guide to the Body That Will Make You Healthier and Younger
,
eat eating mindless more than think we we why
,


texturizer,
texturizers here,
black hair texturizer,
find aout how care curly hair,
find about how to care curly hair,
care curly hair,
lipitor rash,
lipitor reactions,
new house ventura california,
the house new houston tx,
new house washington dc,
new house pa philadelphia,
san antonio tx house new,
house new pa philadelphia,
new house washington dc,
new house ventura california,
the house new houston tx,
house new san antonio tx,
the house new houston tx, that you are looking for,
new house ventura california, you need to buy,
new house washington dc,
house new pa philadelphia,
new house san antonio tx,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

chocolate esophagus heartburn study
,

chocolate esophagus heartburn study
be informed,

digestion healing healthy heartburn natural preventing way
,

digestion healing healthy heartburn natural preventing way
,
sew skirts, 16simple styles you can make!,
sew what skirts 16 simple styles you,
rebates and discounts on sunsetter awnings,
sunsetter awnings discounts and rebates,
discount on sunsetter awnings


truck and bus tires 12r 22.5, get the best price,
tires truck and bus 12r 22.5 best price,
tires truck bus tires12r 22.5 best price,
plush car seat strap covers,
car seat strap covers,plush,
car seat strap, plush covers,
oscoda voip phone systems, the best!,
oscoda voip the phone system,
oscoda voip phone systems,
exterior iron gates,
oriental wrought iron gates,
powder coated iron garden fencing,

fero 52 said...

black mold exposure,
black mold symptoms of exposure,

wrought iron garden gates,
your next iron garden gates, here,

hair styles for fine thin hair,
search hair styles for fine thin hair,

night vision binoculars,
buy, night vision binoculars,

lipitor reactions,
lipitor reactions,

luxury beach resort in the philippines,
beach resort in the philippines,

homeopathy for baby eczema.,
homeopathy for baby eczema.,

save big with great mineral makeup bargains,
companies marketing mineral makeups,

prodam iphone praha,
Apple prodam iphone praha,

iphone clone cect manual,
manual for iphone clone cect,

fero 52 binoculars night vision,
fero 52 night vision,

best night vision binoculars,
buy, best night vision binoculars,

computer programs to make photo albums,
computer programs, make photo albums,

todd1vv said...

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

Kuassi Mensah said...

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

todd1vv said...

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

Kuassi Mensah said...

Todd,

In your case, a static proxy approach will be faster; UTL_DBWS is more flexible.

Kuassi

Anonymous said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰套,自慰,性感吊帶襪,吊帶襪,情趣用品加盟AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,成人網站,A片,A片下載,免費A片,免費A片下載愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片,豆豆聊天室,聊天室,UT聊天室,尋夢園聊天室,男同志聊天室,UT男同志聊天室,聊天室尋夢園,080聊天室,080苗栗人聊天室,6K聊天室,女同志聊天室,小高聊天室,上班族聊天室,080中部人聊天室,同志聊天室,聊天室交友,中部人聊天室,成人聊天室,一夜情聊天室,情色聊天室,寄情築園小遊戲情境坊歡愉用品,情趣用品,成人網站,情人節禮物,情人節,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,成人影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,視訊聊天,A片,A片下載,免費A片,嘟嘟成人網,寄情築園小遊戲,女同志聊天室,免費視訊聊天室,一夜情聊天室,聊天室

Anonymous said...

童裝批發
童裝批發
童裝
童裝
酒店喝酒
酒店消費
暑假打工
寒假打工
酒店經紀人
酒店現領
禮服店
酒店小姐
酒店兼職
酒店上班
酒店兼職
禮服店
酒店上班
酒店打工
酒店小姐
酒店經紀
酒店兼差
假日打工
台北酒店經紀
寒暑假打工
專業酒店經紀
酒店藝名
台北酒店
酒店日保
酒店入行
台北禮服店
酒店類型
酒店午場
酒店規定
酒店行政
酒店資訊