Call web Service From PLSQL

[dbsoracle]

Hello:
 
Does anyone know how to call a web service from PL/SQL?  For example:
I have a WSDL URL that returns the signature of the web service calls. I would like to format a request, with the proper parms such as AcctNum, User, Pass, etc... and receive the response and then parse the response from XML into Oracle Tables. Can this be done with PL/SQL? Is there any sample code available?
 
Thanks for the help.
 
Don

[Mike]

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_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;


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;
/

[dbsoracle]

Thanks so very much for the help.
 
Don