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_joke return varchar2 is 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;
Resources
Tom Krueger 
Recent Comments