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(
'
        
                
                        2
                
        
');
  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:

• HOWTO: Create a Native Database Web Service

HTH

Marco