APEX

Here's a basic skeleton code to get you going with calling a webservice from PL/SQL...

  PROCEDURE p_soap_request(p_username IN VARCHAR2, p_password IN VARCHAR2, p_proxy IN VARCHAR2) IS
    soap_request  VARCHAR2(30000);
    soap_respond  CLOB;
    http_req      utl_http.req;
    http_resp     utl_http.resp;
    resp          XMLType;
    soap_err      exception;
    v_code        VARCHAR2(200);
    v_msg         VARCHAR2(1800);
    v_len number;
    v_txt Varchar2(32767);
  BEGIN
    UTL_HTTP.SET_PROXY(p_proxy);
    -- Define the SOAP request according the the definition of the web service being called
    soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>'||
                   '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'||
                   '  <SOAP-ENV:Body>'||
                   '    <m:DownloadRequest xmlns:m="http://www.website.net/messages/GetDetails">'||
                   '      <m:UserName>'||p_username||'</m:UserName>'||
                   '      <m:Password>'||p_password||'</m:Password>'||
                   '    </m:DownloadRequest>'||
                   '  </SOAP-ENV:Body>'||
                   '</SOAP-ENV:Envelope>';
    http_req:= utl_http.begin_request
              ( 'http://www.website.net/webservices/GetDetailsService.asmx'
              , 'POST'
              , 'HTTP/1.1'
              );
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
    utl_http.set_header(http_req, 'Content-Length', length(soap_request));
    utl_http.set_header(http_req, 'Download', ''); -- header requirements of particular web service
    utl_http.write_text(http_req, soap_request);
    http_resp:= utl_http.get_response(http_req);
    utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1); -- Obtain the length of the response
    FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K
    LOOP
        utl_http.read_text(http_resp, v_txt, case when i < CEIL(v_len/32767) then 32767 else mod(v_len,32767) end);
        soap_respond := soap_respond || v_txt; -- build up CLOB
    END LOOP;
    utl_http.end_response(http_resp);
    resp:= XMLType.createXML(soap_respond); -- Convert CLOB to XMLTYPE
  END;

The actual structure of your SOAP message will depend on the webservice you're calling and you typically find this out by looking at the WSDL for the service.

The response is typically an XML response, also defined in the WSDLs, and then you can extract the data from the XML to determine the actual response.

See for the basics of calling a web service from PL/SQL

Hmm since I never used web services before I do have a hard time understanding what I need to put into XML in order to call my procedure. I ll see if I can make it work. Btw whats the difference between your example and something like this:

{code}

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;

{code}

Best regads,

Igor

The WSDL of the web service defines the XML structure that the web service expects as input, and the XML structure the web server outputs. I recommend using something like SOAPUI to assist you in understanding and using the web service's XML structures.

UTL_DBWS is Java cr@p. I do not use it as it sucks ito controlling and managing the web service call. I do not take kindly to large meaningless Java stack traces when bug hunting.

A web service means using the HTTP(S) application protocol. A very simplistic, well specified and documented, and easy protocol to use - and fully supported by UTL_HTTP. So I fail to see why HTTP needs to be obfuscated by using a poorly designed abstract layer like UTL_DBWS.

So the first thing I need to do is understand what is the structure of XML used for input and what is the structure of XML output from the webservice? And I can do this by using ".svc?wsdl" and SOAPUI. So my question is is there any program that doesnt need installation that does the same as SOAPUI?... since I dont got admin rights on my computer for installations.

Best regards,

Igor

You can typically just use a web browser with the WSDL url to obtain the WSDL from the web service provider.   The browser will just show you the raw WSDL xml document and then you can manually deconstruct that to work out what the SOAP message needs to look like.  SOAPUI just does some of that for you.

Developers need to be able to install s/w on their desktops IMO.

As Blu said, you can use a web browser to view the WSDL. However, that is particularly not easy to convert manually to a SOAP envelope.

Which is why I suggest using SOAPUI. There are however other tools that can be used too - many of which are used by corporates as part of the set of development tools for analysts and developers.

Alternatively one can always ask the development team responsible for the web service to supply one with a sample raw SOAP envelope. In my experience the WSDL does not even always suffice as the web service can require specifics in the SOAP header (such as WSSE) that is not documented in the WSDL.

Another example(GET Method) :

CREATE OR REPLACE FUNCTION CALL_WS(p_URL in varchar2) return clob is
  req    utl_http.req;
  resp   utl_http.resp;
  value  VARCHAR2(7024);
  v_url  varchar2(7000);
  c_clob clob;
begin
  v_url := p_URL;

  req   := utl_http.begin_request(url => v_url, method => 'GET');
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
  utl_http.set_header(req, 'Content-Length', 150000);


  resp := utl_http.get_response(req);
  LOOP
    utl_http.read_line(resp, value, TRUE);
    c_clob := c_clob || value;
  END LOOP;
  utl_http.end_response(resp);


EXCEPTION
 WHEN utl_http.end_of_body THEN
   begin
     utl_http.end_response(resp);  
     return c_clob;
   end;  
  WHEN others THEN
      return SQLERRM;
END;

----

Ramin Hashimzade