Browse All Articles > Calling webservice from PLSQL
Web application components that can be published and used on the Internet are called web services. Here we will see how we can use Oracle PLSQL to access web services.
In Oracle we have a package called UTL_HTTP. By using this package we can call web services using a POST method and get a response from it. For this example I am using the web service available on www.w3schools.com for converting Celsius to Fahrenheit: http://www.w3schools.com/webservices/tempconvert.asmx?op=CelsiusToFahrenheit
We will be sending the POST method using UTL_HTTP package to the above URL for any value as Celsius and then that value gets converted and responds back to use with Fahrenheit value. So the conversion will be happening on the web service.
Let us first check manually using a browser for any values that can be converted. When we open the URL to the service we will see something as shown below:
![pic-1.jpg]()
Let's put 10 as input to the parametrer value for Celsius and check what output we are getting:
![pic-2.jpg]()
After giving the input click on Invoke button to get the response as like below:
![pic-3.jpg]()
So from the above response we can see that 10 Celsius converted as Fahrenheit with a value of 50.
Now let us try to call the same webservice from Oracle.
To use any web services we need to register the URL in our DB by assigning it to the Access Control List (ACL). Here is the below code which need to be executed under SYS user, so that we can utilize it based on any primary users on the schema. I am using to demonstrate this with Oracle Database 11g Release 11.2.0.1.0
Once we have executed the code above, we then have to allow the user to execute the UTL_HTTP package.
Now we can connect to the schema and execute the below code to get the response.
Once executed, we will get the response from the web services as like below:
So we can see from the above that we are also able to get the same result as we did accessing the URL manually.
In Oracle we have a package called UTL_HTTP. By using this package we can call web services using a POST method and get a response from it. For this example I am using the web service available on www.w3schools.com for converting Celsius to Fahrenheit: http://www.w3schools.com/w
We will be sending the POST method using UTL_HTTP package to the above URL for any value as Celsius and then that value gets converted and responds back to use with Fahrenheit value. So the conversion will be happening on the web service.
Let us first check manually using a browser for any values that can be converted. When we open the URL to the service we will see something as shown below:
Let's put 10 as input to the parametrer value for Celsius and check what output we are getting:
After giving the input click on Invoke button to get the response as like below:
So from the above response we can see that 10 Celsius converted as Fahrenheit with a value of 50.
<string xmlns="http://www.w3schols.com/webservices/">50</string>Now let us try to call the same webservice from Oracle.
To use any web services we need to register the URL in our DB by assigning it to the Access Control List (ACL). Here is the below code which need to be executed under SYS user, so that we can utilize it based on any primary users on the schema. I am using to demonstrate this with Oracle Database 11g Release 11.2.0.1.0
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',
description => 'Test Sample ACL',
principal => 'EXP1',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',
principal => 'EXP1',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',
host => 'www.w3schools.com');
END;
/
COMMIT
/Once we have executed the code above, we then have to allow the user to execute the UTL_HTTP package.
Connected to Oracle Database 11g Release 11.2.0.1.0
Connected as SYS
SQL>
SQL> BEGIN
2 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',
3 description => 'Test Sample ACL',
4 principal => 'EXP1',
5 is_grant => true,
6 privilege => 'connect');
7
8 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',
9 principal => 'EXP1',
10 is_grant => true,
11 privilege => 'resolve');
12
13 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',
14 host => 'www.w3schools.com');
15 END;
16 /
PL/SQL procedure successfully completed
SQL> COMMIT
2 /
Commit complete
SQL>
SQL> grant execute on UTL_HTTP to EXP1;
Grant succeededNow we can connect to the schema and execute the below code to get the response.
DECLARE
l_http_request utl_http.req;
l_http_response utl_http.resp;
l_buffer_size NUMBER(10) := 512;
l_line_size NUMBER(10) := 50;
l_lines_count NUMBER(10) := 20;
l_string_request VARCHAR2(512);
l_line VARCHAR2(128);
l_substring_msg VARCHAR2(512);
l_raw_data RAW(512);
l_clob_response CLOB;
l_host_name VARCHAR2(128) := 'www.w3schools.com';
l_celsius VARCHAR2(128) := '10'; -- Celsius is passed here
l_resp_xml xmltype;
l_result_xml_node VARCHAR2(128);
l_namespace_soap VARCHAR2(128) := 'xmlns="http://www.w3.org/2003/05/soap-envelope"';
l_response_fahrenheit VARCHAR2(128);
BEGIN
l_string_request := '<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<CelsiusToFahrenheit xmlns="http://www.w3schools.com/webservices/">
<Celsius>' || l_celsius || '</Celsius>
</CelsiusToFahrenheit>
</soap12:Body>
</soap12:Envelope>';
utl_http.set_transfer_timeout(60);
l_http_request := utl_http.begin_request(url => 'http://www.w3schools.com/webservices/tempconvert.asmx'
,method => 'POST'
,http_version => 'HTTP/1.1');
utl_http.set_header(l_http_request
,'User-Agent'
,'Mozilla/4.0');
utl_http.set_header(l_http_request
,'Connection'
,'close');
utl_http.set_header(l_http_request
,'Content-Type'
,'application/soap+xml; charset=utf-8');
utl_http.set_header(l_http_request
,'Content-Length'
,length(l_string_request));
<<request_loop>>
FOR i IN 0 .. ceil(length(l_string_request) / l_buffer_size) - 1
LOOP
l_substring_msg := substr(l_string_request
,i * l_buffer_size + 1
,l_buffer_size);
BEGIN
l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
utl_http.write_raw(r => l_http_request
,data => l_raw_data);
EXCEPTION
WHEN no_data_found THEN
EXIT request_loop;
END;
END LOOP request_loop;
l_http_response := utl_http.get_response(l_http_request);
dbms_output.put_line('Response> status_code: "' || l_http_response.status_code || '"');
dbms_output.put_line('Response> reason_phrase: "' ||
l_http_response.reason_phrase || '"');
dbms_output.put_line('Response> http_version: "' || l_http_response.http_version || '"');
BEGIN
<<response_loop>>
LOOP
utl_http.read_raw(l_http_response
,l_raw_data
,l_buffer_size);
l_clob_response := l_clob_response ||
utl_raw.cast_to_varchar2(l_raw_data);
END LOOP response_loop;
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(l_http_response);
END;
IF (l_http_response.status_code = 200)
THEN
-- Create XML type from response text
l_resp_xml := xmltype.createxml(l_clob_response);
-- Clean SOAP header
SELECT extract(l_resp_xml
,'Envelope/Body/node()'
,l_namespace_soap)
INTO l_resp_xml
FROM dual;
-- Extract Fahrenheit value
l_result_xml_node := '/CelsiusToFahrenheitResponse/CelsiusToFahrenheitResult';
dbms_output.put_line('Response from w3schools webservices:');
SELECT extractvalue(l_resp_xml
,l_result_xml_node
,'xmlns="http://www.w3schools.com/webservices/"')
INTO l_response_fahrenheit
FROM dual;
END IF;
dbms_output.put_line(l_celsius || ' Celsius = ' || l_response_fahrenheit ||
' Fahrenheit');
IF l_http_request.private_hndl IS NOT NULL
THEN
utl_http.end_request(l_http_request);
END IF;
IF l_http_response.private_hndl IS NOT NULL
THEN
utl_http.end_response(l_http_response);
END IF;
COMMIT;
END;Once executed, we will get the response from the web services as like below:
Connected to Oracle Database 11g Release 11.2.0.1.0
Connected as exp1
SQL> set serveroutput on
SQL> ed
SQL> /
Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response from w3schools webservices:
10 Celsius = 50 Fahrenheit
PL/SQL procedure successfully completed
SQL> So we can see from the above that we are also able to get the same result as we did accessing the URL manually.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)