Does anyone know how to call a web service from PL/SQL?
Tom Krueger has an excellent write up on this:
I would like to say that calling a web service has been made easy with Sys.UTL_DBWS package but it took me a bit of time to get it right. This biggest issue that I ran into is that the samples that I found online and in the forums simply don’t work.
The sample below was provided by Oracle support and helped a lot in getting started so I thought that I would pass it along. The sample uses a public web service so if the service is still running this code should just work for you. My ultimate goal was to call a Microsoft WCF service which I have been able to do and plan to post the code soon.
function get_jokereturn varchar2is service_ sys.utl_dbws.SERVICE; call_ sys.utl_dbws.CALL; service_qname sys.utl_dbws.QNAME; port_qname sys.utl_dbws.QNAME; xoperation_qname sys.utl_dbws.QNAME; xstring_type_qname sys.utl_dbws.QNAME; response sys.xmltype; request sys.xmltype;begin service_qname := sys.utl_dbws.to_qname(null, 'getJoke'); service_ := sys.utl_dbws.create_service(service_qname); call_ := sys.utl_dbws.create_call(service_); sys.utl_dbws.set_target_endpoint_address(call_, 'http://interpressfact.net/webservices/getjoke.asmx'); sys.utl_dbws.set_property( call_, 'SOAPACTION_USE', 'TRUE'); sys.utl_dbws.set_property( call_, 'SOAPACTION_URI', 'http://interpressfact.net/webservices/getJoke'); sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document'); request := sys.xmltype( '<getJoke xmlns="http://interpressfact.net/webservices/">' || '<Category>Excuses-10</Category>' || '</getJoke>'); response :=sys. utl_dbws.invoke(call_, request); return response.extract('//getJokeResult/child::text()', 'xmlns="http://interpressfact.net/webservices/"').getstringval(); end;
http://tomkrueger.wordpress.com/2008/09/17/how-to-call-a-web-service-from-oracle-plsql/Also, there's another page that gives a good explanation of doing this:
http://akdora.wordpress.com/2007/08/03/calling-a-web-service-by-plsql-utl_http/Web services uses XML to code and decode your data and SOAP to transport it.
We have some input parameters and make a request.Then, we get a response from it with output parameters. All this syntaxis in XML format. So, we will use XMLtype in our pl/sql package.
Let’s start to write a soap access package or we can use this package (
www.oracle-base.com/dba/miscellaneous/soap_api.sql)
Let’s assume we have a function “myFunction” in this page
http://www.mywebservice.com/web Then,start to write our code
Web services uses XML to code and decode your data and SOAP to transport it.
We have some input parameters and make a request. Then, we get a response from it with output parameters. All this syntax is in XML format. So, we will use XMLtype in our pl/sql package.
Let’s start to write a soap access package or we can use this package

(
www.oracle-base.com/dba/miscellaneous/soap_api.sql)

Let’s assume we have a function “myFunction” in this page
http://www.mywebservice.com/web 
Then,start to write our code
CREATE OR REPLACE PACKAGE pkg_my_webservice IS FUNCTION call_myfuntion ( vp_parameter1 VARCHAR2, vp_parameter2 VARCHAR2 ) RETURN VARCHAR2;END pkg_my_webservice;/CREATE OR REPLACE PACKAGE BODY pkg_my_webservice IS vg_funciton_fnc VARCHAR2(256) := 'myFunction'; vg_ws_address VARCHAR2(255) := 'http://www.mywebservice.com/web'; FUNCTION call_myfuntion( --lets assume that it inputs two parameters called string1, string2 vp_parameter1 VARCHAR2, vp_parameter2 VARCHAR2) RETURN VARCHAR2 AS ol_req soap_api.t_request; ol_resp soap_api.t_response; BEGIN -- we initilize a new request ol_req := soap_api.new_request(vg_funciton_fnc, 'xmlns="' || vg_ws_address || '"'); -- we started to add parameters soap_api.add_parameter(ol_req, 'string1', 'partns:string', vp_parameter1); soap_api.add_parameter(ol_req, 'string2', 'partns:string', vp_parameter1); -- we call the web service ol_resp := soap_api.invoke(ol_req, vg_ws_address, vg_funciton_fnc); -- we get back the results RETURN soap_api.get_return_value(ol_resp, 'result', -- result tag name 'xmlns:m="' || --can be change as "xmlns:n1" vg_ws_address || '"'); END call_myfuntion;END pkg_my_webservice;/