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:

HTH

Marco

Marco Gralike Written by:

6 Comments

  1. A_Non
    July 16

    Just a note of caution when sending large amounts of info to the remote web service. UTL_HTTP.WRITE_TEXT takes in a VARCHAR2 so if your data is over 32K, you’ll need a loop around UTL_HTTP.WRITE_TEXT to write the total message as 32K chunks to the web service.

  2. Aditya
    March 28

    This is good, if the db version is 10.2 and above, using UTL_DBWS is the best way to consume Webservice

    • March 28

      It might…but there are reasons why UTL_DBWS is not installed by default, and/or a part of a database feature, in the database. Oracle might become fuzzy regarding its support.

  3. Abhisekh
    August 1

    Hi all , can anybody tell me how to generate the below xml type
    (

    2

    ‘);

Comments are closed.