UTL_HTTP to Call a Web Service from PL/SQL

You can call a web service from PL/SQL using the Oracle-supplied package UTL_HTTP. Here’s a demo of calling a public web service that returns latest city weather by ZIP.

A PL/SQL script to invoke a web service, followed by its execution…

Call a web service from PL/SQL using UTL_HTTP

PL/SQL script that invokes the web service to get latest weather, then reads the response into a CLOB variable. The response can later be converted to XMLType and the values can be extracted using XMLTable.

declare
  -- String that holds the HTTP request string
  soap_request       varchar2(32767);

  -- PL/SQL record to represent an HTTP request, 
  -- as returned from the call to begin_request
  http_req           utl_http.req;

  -- PL/SQL record to represent the output of
  -- get_response 
  http_resp          utl_http.resp;

  -- HTTP version that can be used in begin_request: 1.0 or 1.1
  t_http_version     varchar2(10) :=  'HTTP/1.1';

  t_content_type     varchar2(50) := 'text/xml; charset=utf-8';

  --  URL of the HTTP request, set after begin_request.
  t_url              varchar2(100) := 'wsf.cdyne.com/WeatherWS/Weather.asmx';

  -- Variables to handle the web service response
  x_clob             CLOB;
  l_buffer           VARCHAR2(32767); 
  
  -- US city ZIP for which the weather data has 
  -- to be fetched via web service
  l_zip              varchar2(10) := '10007'; 

begin

  dbms_output.put_line('ZIP: ' || l_zip );

  -- Build the HTTP soap request
  soap_request := '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetCityWeatherByZIP xmlns="http://ws.cdyne.com/WeatherWS/">
      <ZIP>' || l_zip || '</ZIP>
    </GetCityWeatherByZIP>
  </soap:Body>
</soap:Envelope>';  

  -- Begin a new HTTP request: establish the network 
  -- connection to the target web server or proxy 
  -- server and send the HTTP request line.
  http_req := utl_http.begin_request(t_url
                        , 'POST' -- method
                        , t_http_version);

  -- Set HTTP request header attributes. The 
  -- request header is sent to the web server 
  -- as soon as it is set.
  utl_http.set_header(http_req
    , 'Content-Type', t_content_type);
  utl_http.set_header(http_req
    , 'Content-Length'
    , length(soap_request));
  utl_http.set_header(http_req
    , 'SOAPAction'
    , '"http://ws.cdyne.com/WeatherWS/GetCityWeatherByZIP"');

  -- Write soap request text data in the HTTP request
  utl_http.write_text(http_req, soap_request);

  -- get_response output of record type utl_http.resp. 
  -- http_resp contains a 3-digit status_code, a
  -- reason_phrase and HTTP version.
  http_resp := utl_http.get_response(http_req);

  -- Build a CLOB variable to hold web service response
  dbms_lob.createtemporary(x_clob, FALSE );
  dbms_lob.open( x_clob, dbms_lob.lob_readwrite );

  begin

    loop
      -- Copy the web service response body 
      -- in a buffer string variable l_buffer
      utl_http.read_text(http_resp, l_buffer);

      -- Append data from l_buffer to CLOB variable
      dbms_lob.writeappend(x_clob
                        , length(l_buffer)
                        , l_buffer);
    end loop;

  exception
      when others then
        --  Exit loop without exception
        --  when end-of-body is reached
        if sqlcode <> -29266 then
          raise;
        end if;
  end;

  -- Verify the response status and text
  dbms_output.put_line('Response Status: ' 
                   ||http_resp.status_code
                   ||' ' || http_resp.reason_phrase);
  dbms_output.put_line('Response XML:' 
                   || cast(x_clob as varchar2));

  utl_http.end_response(http_resp) ;  
  
  -- x_clob response can now be used for extracting text
  -- values from specific XML nodes, using XMLExtract
end;
/

Execution of PL/SQL Script Using UTL_HTTP

When the above script is run, this is what happens:

SQL> declare
  2    -- String that holds the HTTP request string
  3    soap_request       varchar2(32767);
  4  
  5    -- PL/SQL record to represent an HTTP request,
  6    -- as returned from the call to begin_request
  7    http_req           utl_http.req;
  8  
  9    -- PL/SQL record to represent the output of
 10    -- get_response
 11    http_resp          utl_http.resp;
 12  
 13    -- HTTP version that can be used in begin_request: 1.0 or 1.1
 14    t_http_version     varchar2(10) :=  'HTTP/1.1';
 15  
 16    t_content_type     varchar2(50) := 'text/xml; charset=utf-8';
 17  
 18    --  URL of the HTTP request, set after begin_request.
 19    t_url              varchar2(100) := 'wsf.cdyne.com/WeatherWS/Weather.asmx';
 20  
 21    -- Variables to handle the web service response
 22    x_clob             CLOB;
 23    l_buffer           VARCHAR2(32767);
 24  
 25    -- US city ZIP for which the weather data has
 26    -- to be fetched via web service
 27    l_zip              varchar2(10) := '10007';
 28  
 29  begin
 30  
 31    dbms_output.put_line('ZIP: ' || l_zip );
 32  
 33    -- Build the HTTP soap request
 34    soap_request := '<?xml version="1.0" encoding="utf-8"?>
 35  <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 36  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 37    <soap:Body>
 38      <GetCityWeatherByZIP xmlns="http://ws.cdyne.com/WeatherWS/">
 39        <ZIP>' || l_zip || '</ZIP>
 40      </GetCityWeatherByZIP>
 41    </soap:Body>
 42  </soap:Envelope>';
 43  
 44    -- Begin a new HTTP request: establish the network
 45    -- connection to the target web server or proxy
 46    -- server and send the HTTP request line.
 47    http_req := utl_http.begin_request(t_url
 48                          , 'POST' -- method
 49                          , t_http_version);
 50  
 51    -- Set HTTP request header attributes. The
 52    -- request header is sent to the web server
 53    -- as soon as it is set.
 54    utl_http.set_header(http_req
 55      , 'Content-Type', t_content_type);
 56    utl_http.set_header(http_req
 57      , 'Content-Length'
 58      , length(soap_request));
 59    utl_http.set_header(http_req
 60      , 'SOAPAction'
 61      , '"http://ws.cdyne.com/WeatherWS/GetCityWeatherByZIP"');
 62  
 63    -- Write soap request text data in the HTTP request
 64    utl_http.write_text(http_req, soap_request);
 65  
 66    -- get_response output of record type utl_http.resp.
 67    -- http_resp contains a 3-digit status_code, a
 68    -- reason_phrase and HTTP version.
 69    http_resp := utl_http.get_response(http_req);
 70  
 71    -- Build a CLOB variable to hold web service response
 72    dbms_lob.createtemporary(x_clob, FALSE );
 73    dbms_lob.open( x_clob, dbms_lob.lob_readwrite );
 74  
 75    begin
 76  
 77      loop
 78        -- Copy the web service response body
 79        -- in a buffer string variable l_buffer
 80        utl_http.read_text(http_resp, l_buffer);
 81  
 82        -- Append data from l_buffer to CLOB variable
 83        dbms_lob.writeappend(x_clob
 84                          , length(l_buffer)
 85                          , l_buffer);
 86      end loop;
 87  
 88    exception
 89        when others then
 90          --  Exit loop without exception
 91          --  when end-of-body is reached
 92          if sqlcode <> -29266 then
 93            raise;
 94          end if;
 95    end;
 96  
 97    -- Verify the response status and text
 98    dbms_output.put_line('Response Status: '
 99                     ||http_resp.status_code
100                     ||' ' || http_resp.reason_phrase);
101    dbms_output.put_line('Response XML:'
102                     || cast(x_clob as varchar2));
103  
104    utl_http.end_response(http_resp) ;
105  
106    -- x_clob response can now be used for extracting text
107    -- values from specific XML nodes, using XMLExtract
108  end;
109  /
ZIP: 10007                                                                      
Response Status: 200 OK                                                         
Response XML:<?xml version="1.0" encoding="utf-8"?><soap:Envelope               
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"                          
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"                           
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetCityWeatherByZIPResp
onse                                                                            
xmlns="http://ws.cdyne.com/WeatherWS/"><GetCityWeatherByZIPResult><Success>true<
/Success><ResponseText>City Found</ResponseText><State>NY</State><City>New      
York</City><WeatherStationCity>White                                            
Plains</WeatherStationCity><WeatherID>15</WeatherID><Description>N/A</Descriptio
n><Temperature>63</Temperature><RelativeHumidity>87</RelativeHumidity><Wind>E7</
Wind><Pressure>29.97S</Pressure><Visibility /><WindChill /><Remarks             
/></GetCityWeatherByZIPResult></GetCityWeatherByZIPResponse></soap:Body></soap:E
nvelope>                                                                        

PL/SQL procedure successfully completed.

A response status of 200 OK indicates that the request has succeeded. The response XML shows the weather details for the input ZIP.

Possible Errors in UTL_HTTP Call

A few errors you might encounter when using UTL_HTTP, and their possible causes.

PLS-00201: identifier ‘UTL_HTTP’ must be declared

You will see the above error when the database user (say [utluser]) executing the PL/SQL code does not have execute permission on UTL_HTTP.

To resolve the error, login as SYS or SYSTEM, and provide the execute permission.

grant execute on sys.utl_http to [utluser];

Log back again as [utluser] and run the PL/SQL code again. It should work this time.

ORA-29273: HTTP request failed or ORA-12535: TNS:operation timed out

This suggests a connection error. Are you behind a firewall or is the web service down? From command prompt, ping the server and verify.

You will receive a response of this form if the connectivity is fine:

Pinging ws.cdyne.com [4.59.146.110] with 32 bytes of data:
Reply from 4.59.146.110: bytes=32 time=269ms TTL=120
Reply from 4.59.146.110: bytes=32 time=275ms TTL=120
Reply from 4.59.146.110: bytes=32 time=259ms TTL=120
Reply from 4.59.146.110: bytes=32 time=272ms TTL=120

Ping statistics for 4.59.146.110:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 259ms, Maximum = 275ms, Average = 268ms

You will receive a response of this form if the connectivity is not OK:

[Pinging ws.cdyne.com [4.59.146.110] with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

Ping statistics for 87.106.3.248:
    Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

In the second case, test the PL/SQL from outside the firewall or ensure that the web service is up.

ORA-24247: network access denied by access control list (ACL)

This error indicates that an essential piece of security setup – ACL configuration – is missing. Look up Access Control List for details on what ACL is and how to set it up.

For Further Reading

Oracle 10G documentation on UTL_HTTP
PSOUG on UTL_HTTP
Keep the Response Body with non-2xx Responses in UTL_HTTP