Posted tagged ‘Web Service’

How To Call a Web Service from Oracle PL/SQL

September 17, 2008

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
  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;
  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_, '');
  sys.utl_dbws.set_property( call_, 'SOAPACTION_USE', 'TRUE');
  sys.utl_dbws.set_property( call_, 'SOAPACTION_URI', '');
  sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');
  request := sys.xmltype(
       '<getJoke xmlns="">'
    || '<Category>Excuses-10</Category>'
    || '</getJoke>');
  response :=sys. utl_dbws.invoke(call_, request);
  return response.extract('//getJokeResult/child::text()',