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
