In this Post we would be looking into a basic example for a calling a web service from the database
Oracle provides a set of packages to access the web services directly through PL/SQL.
UTL_HTTP is a standard package given by Oracle to consume a web service.
From Oracle database we can directly call the web services using PL/SQL.
Steps
1. View the SOAP envelope of the web service to be consumed.
We have a lot of free web services available over the internet, I have chosen
a free to web service to validate a given emailid
Click the above link to test the web service and to get the full details of the web services.
2. How the input needs to be send to a web service?
All the communications over the internet is done through XML. So we have to create a XML fragment.
{ All the request details are sent as an XML imput email address
}
Note
To have a complete description of what is SOAP and why it needs to be used kindly click the following link
3. Create a PL/SQL procedure to create the input to be given in the web service.
CREATE OR REPLACE
FUNCTION F_VALIDATE_EMAIL
(
p_email_id VARCHAR2)
RETURN VARCHAR2
AS
input_envelope VARCHAR2(32767);
http_req utl_http.req;
http_resp utl_http.resp;
resp sys.xmltype;
in_xml sys.xmltype;
WSDL_URL VARCHAR2(2000):='http://www.webservicex.net/ValidateEmail.asmx?WSDL';
res VARCHAR2(1000);
BEGIN
-- Generating the input to be sent to the web service
input_envelope := '<?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>
<IsValidEmail xmlns="http://www.webservicex.net">
<Email>'||p_email_id||'</Email>
</IsValidEmail>
</soap:Body>
</soap:Envelope>';
--Sending the input enevlope to the web service
http_req := utl_http.begin_request(WSDL_URL, 'POST','HTTP/1.1');
utl_http.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'Content-Length', LENGTH(input_envelope));
utl_http.set_header(http_req, 'SOAPAction', 'http://www.webservicex.net/IsValidEmail');
utl_http.write_text(http_req, input_envelope);
http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp, input_envelope);
res := REPLACE(input_envelope,'<?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">','');
res := REPLACE(res,'<soap:Body><IsValidEmailResponse xmlns="http://www.webservicex.net"><IsValidEmailResult>','');
res := REPLACE(res,'</IsValidEmailResult></IsValidEmailResponse></soap:Body></soap:Envelope>','');
dbms_output.put_line(SUBSTR(res,1,250));
utl_http.end_response(http_resp);
RETURN res;
END;
Now let us dissect the code
1. This portion of the code is used for creating the input to the web service
-- Generating the input to be sent to the web service
input_envelope := '<?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>
<IsValidEmail xmlns="http://www.webservicex.net">
<Email>'||p_email_id||'</Email> --p_email_id is sent as a parameter
</IsValidEmail>
</soap:Body>
The format for the SOAP envelope would be aldready given in the service description of the web service.You need to replace only the parameter to be passed.
2. Setting all the configurations before calling the web service
http_req := utl_http.begin_request(WSDL_URL, 'POST','HTTP/1.1'); -- Starts the request
utl_http.set_header(http_req, 'Content-Type', 'text/xml'); --Setting the content type
utl_http.set_header(http_req, 'Content-Length', LENGTH(input_envelope)); --Setting the length
utl_http.set_header(http_req, 'SOAPAction', 'http://www.webservicex.net/IsValidEmail'); --Setting the function to be called
3. Calling the web service and retrieving the value
utl_http.set_header(http_req, 'SOAPAction', 'http://www.webservicex.net/IsValidEmail');
utl_http.write_text(http_req, input_envelope);
http_resp := utl_http.get_response(http_req); --Getting the response for the given SOAP enevelope
utl_http.read_text(http_resp, input_envelope); -- Getting response in a variable
Note
As the repsone given by the web services is a XML fragment, use a XMLTYPE variable to manipulate the output given by the web service to retrieve the desired value
i.e
The response given by the web service would be as follows
<?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>
<IsValidEmailResponse xmlns="http://www.webservicex.net">
<IsValidEmailResult>true</IsValidEmailResult>
</IsValidEmailResponse>
</soap:Body>
</soap:Envelope>
This is a very helpful post.
ReplyDeleteI noticed, though, that although you declare the variables resp and in_xml of type sys.xmltype, and even mention this in your last note, you don't actually use these variables.
Could it be that the notes were for a different version of this, which did use these variables?
Hi,
ReplyDeleteThanks for your comment.
At the last step , we are getting a XML fragment from the web services, which we should get the output in IN_XML and we have to use the EXTRACT method to get the desired node values.
So after getting the output from the web services we are using this variable for extract the values from the XML output obtained from the web services.
Revert for any clarifications.
Subramanian -- thanks for the quick feedback.
ReplyDeleteI did continue with your example, using the xml fragment to instantiate an xmlType variable, then using extract() to retrieve values.
I was only pointing out that your example might be confusing to someone who saw the xmlType variables declared but not used.
Anyway, your post is very helpful -- thank you!
-Paul