HOWTO: Consume Webservices via PL/SQL

Before I forget, hereby on my “notepad”, a small reminder for me and others that might be interested, how you can consume a webservice via plain old PL/SQL, for instance the SOAP service that can be created via the Oracle 11g Native Database WebService (so called NDWS), out-of-the-box 11g database functionality, as explained by Mark D on the XMLDB forum site here: “Re: Using utl_dbws to call web service”

The essential consuming part/code is shown below:

SET serveroutput ON
--
DECLARE
  V_SOAP_REQUEST      XMLTYPE := XMLTYPE(
'<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><soap -ENV:Body>
                <m :SNUMBER-GET_SQRTInput xmlns:m="http://xmlns.oracle.com/orawsv/&USERNAME/GET_SQRT">
                        </m><m :INPUT_VALUE-NUMBER-IN>2</m>
 
        </soap>
');
  V_SOAP_REQUEST_TEXT CLOB := V_SOAP_REQUEST.getClobVal();
  V_REQUEST           UTL_HTTP.REQ;
  V_RESPONSE          UTL_HTTP.RESP;
  V_BUFFER            VARCHAR2(1024);
BEGIN
 
  V_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => :URL, METHOD => 'POST');
  UTL_HTTP.SET_HEADER(V_REQUEST, 'User-Agent', 'Mozilla/4.0');
  V_REQUEST.METHOD := 'POST';
  UTL_HTTP.SET_HEADER (R => V_REQUEST, NAME => 'Content-Length', VALUE => DBMS_LOB.GETLENGTH(V_SOAP_REQUEST_TEXT));
  UTL_HTTP.WRITE_TEXT (R => V_REQUEST, DATA => V_SOAP_REQUEST_TEXT);
 
  V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST);
  LOOP
    UTL_HTTP.READ_LINE(V_RESPONSE, V_BUFFER, TRUE);
    DBMS_OUTPUT.PUT_LINE(V_BUFFER);
  END LOOP;
  UTL_HTTP.END_RESPONSE(V_RESPONSE);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(V_RESPONSE);
END;
/

A demo script for an Oracle 11g database can be downloaded here.

More info in how to set-up Native Database WebServices in Oracle 11g is explained here:

HTH

Marco