A while back I posted on how to call a web service from Oracle PL/SQL here, but it was more of a basic starting point. I had further enhanced the version to be more generic but never posted it. Sorry.
I’ve written this version in a single declare so you can easily run the example. When you put it in practice you will want to break it out in to the proper packages and add logging as needed.
declare ls_result clob; -- -- ------------------------------------------------------------------------------- -- f_callWebService -- -- Helper method to call a web service. -- -- Returns the value returned from the web service method. -- ------------------------------------------------------------------------------- function f_callWebService ( as_serviceUrl in varchar2, as_targetNamespace in varchar2, as_serviceName in varchar2, as_portName in varchar2, as_wsMethodName in varchar2, as_parmName1 in varchar2, -- TODO: Update to pass an array of name/value pairs. as_parmValue1 in varchar2) return clob is cs_soapActionUseKey constant varchar2(14) := 'SOAPACTION_USE'; cs_soapActionUseVal constant varchar2(4) := 'TRUE'; cs_soapActionUriKey constant varchar2(14) := 'SOAPACTION_URI'; ls_targetNamespace varchar2(500); ls_serviceQname sys.utl_dbws.qname; ls_portQname sys.utl_dbws.qname; ls_operationQname sys.utl_dbws.qname; ln_service sys.utl_dbws.service; ln_call sys.utl_dbws.call; lx_serviceRequest sys.xmltype; lx_serviceResponse sys.xmltype; ls_resultXpath varchar2(100); -- -- function extractResult( ac_webServiceResponse in xmltype, as_targetNamespace in varchar2, as_wsMethodName in varchar2) return clob is lx_result sys.xmltype; begin -- -- Parse the result from the service response. -- Example Xpath: ExecuteResponse/ExecuteResult/child::text() -- ls_resultXpath := as_wsMethodName || 'Response/' || as_wsMethodName || 'Result/child::text()'; -- -- Extract return value. lx_result := lx_serviceResponse.extract(ls_resultXpath, 'xmlns="' || as_targetNamespace || '"'); -- if lx_result is not null then return lx_result.getClobVal(); end if; return null; end extractResult; begin -- if as_serviceUrl is null or trim(as_serviceUrl) is null then raise_application_error( -20001, 'Argument not passed exception. Argument: ''as_serviceUrl'''); end if; --TODO: Validate other parameters. -- -- Append url separator to end if one does not exist. ls_targetNamespace := as_targetNamespace; if substr(ls_targetNamespace, length(ls_targetNamespace), 1) != '/' then ls_targetNamespace := ls_targetNamespace || '/'; end if; -- -- Initialize the service components. -- ls_serviceQname := sys.utl_dbws.to_qname( name_Space => ls_targetNamespace, name => as_serviceName); -- ls_portQname := sys.utl_dbws.to_qname( name_Space => ls_targetNamespace, name => as_portName); -- ls_operationQname := sys.utl_dbws.to_qname( name_Space => ls_targetNamespace, name => as_wsMethodName); -- -- Create the service and call objects. ln_service := sys.utl_dbws.create_service ( service_name => ls_serviceQname); -- ln_call := sys.utl_dbws.create_call ( service_handle => ln_service, port_name => ls_portQname, operation_name => ls_operationQname); -- -- Set values on the call object. sys.utl_dbws.set_target_endpoint_address( call_Handle => ln_call, endpoint => as_serviceUrl); -- sys.utl_dbws.set_property( call_Handle => ln_call, key => cs_soapActionUseKey, value => cs_soapActionUseVal); -- sys.utl_dbws.set_property( call_Handle => ln_call, key => cs_soapActionUriKey, value => ls_targetNamespace || as_wsMethodName); -- -- -- Create service request xml. -- lx_serviceRequest := sys.xmltype( '<' || as_wsMethodName || ' xmlns="' || ls_targetNamespace || '">' || '<' || as_parmName1 || '>' || as_parmValue1 || '</' || as_parmName1 || '>' || '</' || as_wsMethodName || '>'); dbms_output.put_line(lx_serviceRequest.getclobval()); -- -- -- Call the service. -- begin lx_serviceResponse := sys.utl_dbws.invoke(ln_call, lx_serviceRequest); exception when others then -- TODO: Log Error dbms_output.put_line(sqlerrm); raise; end; -- sys.utl_dbws.release_call (call_handle => ln_call); sys.utl_dbws.release_service (service_handle => ln_service); -- return extractResult(lx_serviceResponse, ls_targetNamespace, as_wsMethodName); end f_callWebService; begin ls_result := f_callWebService( as_serviceUrl => 'http://interpressfact.net/webservices/getjoke.asmx', as_targetNamespace => 'http://interpressfact.net/webservices/', as_serviceName => 'getJoke', as_portName => '', as_wsMethodName => 'getJoke', as_parmName1 => 'Category', as_parmValue1 => 'Excuses-10'); dbms_output.put_line(ls_result); return; end;
Disclaimer: This code could very well be improved upon. Please test it first and please let me know of your improvements. Thanks!
Run It
I Run the example in Golden6.
- Paste the code into a query tab
- Open the DBMS.Output window (F10). This is were the output will be for this example.
- Execute Query (F5).
I have noticed that sometimes, the first time I run it, it takes a long time to run (more than a minute). I assume that this is because the joke web service has not be hit lately and is spinning up, but I’m not certain. My point is be patient.
Notes
There is currently only a single parameter being passed in because I used it to pass xml to a single parameter web service.
Errors
If the target namespace does not end with a separator ‘/’ the following exception will be raised. However, you should not have to worry about it because the code sample appends the separator if it is not passed in.
ORA-29532: Java call terminated by uncaught Java exception: javax.xml.rpc.soap.SOAPFaultException: System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: http://interpressfact.net/webservicesgetJoke.
at System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest()
at System.Web.Services.Protocols.SoapServerProtocol.Initialize()
at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest req
Recent Comments