Calling Webservices from pl/sql

For a client of us we had to call a webservice from a database, and the received information that we received from this call was needed in another procedure.

It was the first time for me that I had to do this and I believe that not so many people now of this functionality in the database and that’s why it is obviously a good idea to post this on our blog.

It is even possible to let the database be a webservice provider, but in this case the webservice already existed and should simply be called by the database.

What was the objective: we wanted to call the webservice from the database, get the resulting XML file and analyze some of the content of the returned XML file and then do some actions with the information that the webservice provided us.

Therefore I wrote 3 procedures in a package:
• fnc$_get_xml
• fnc$_handle_xml
• prc$_ws_call

As you will see when you look at the code it is possible to make this much more dynamically but for this case we only had to call only one specific webservice, as usual you can make it very complex but for the blog I made the procedures and functions as simple as possible.

The first function that I will explain is the prc$_ws_call
This procedure will contact the function fnc$_get_xml that will get the xml file. After this function the fnc$_handle_xml will be called to retrieve specific information out of the XML file.
When this is done the received values will be printed

PROCEDURE prc$_ws_call
IS
v_xml VARCHAR2(32767);
v_type cab_base_adres.type%TYPE;
r_receive r_info;
BEGIN
v_xml := fnc$_get_xml(p_search => 'search value'); --call to webservice
r_receive := fnc$_handle_xml(v_xml); --analization of xml content
dbms_output.put_line(‘value nr1=’||r_receive.value1) ;
dbms_output.put_line(‘value nr2=’||r_receive.value2) ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm) ;
END;

fnc$_get_xml
This function will receive a parameter(search) this parameter will be included in the soap call(called v_soap_request).
Ones that I have build my soap envelope, I have to create a httpRequest, this is the variable v_httpRequest which is of the type utl_http.req.
For this variable I have to set some parameters like the webservice url that I will call, the content-type (plain text in this case), the content_length of the soap envelope and the specification that this is a soapaction. This is the preparation for the call to the webservice.

Next thing to do is to write this data to the body of the http request, this is done with the utl_http.write_text where we give in the http_request and the soap_request.
Now we want to receive the response from our soap call.
This will be created with the utl_http.get_response which is of the utl_http.resp type.

Next thing we want, is to have this file in a readable form. Therefore we use the utl_http.read_text, that will translate the response in a readable variable.

FUNCTION fnc$_get_xml(p_search VARCHAR2)
RETURN VARCHAR2
IS
v_soapRequest VARCHAR2(32000);
v_soapResponse VARCHAR2(32767);
v_httpRequest utl_http.req;
v_httpResponse utl_http.resp;
BEGIN
v_soapRequest :=
'<?xml version="1.0" encoding="utf-8" standalone="no"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:apachesoap="http://xml.apache.org/xml-soap" xmlns:impl="<the webservice>" xmlns:intf="<the webservice>" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns1=" " xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:wsdlsoap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<SOAP-ENV:Body>
<mns: <webservice name>xmlns:mns="<services link>" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<inputAddress xsi:type="tns1: <input parameter name>">
<input search xsi:type="xsd:string">'||p_search||'</search>
</mns: <webservice name> >
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';

v_httpRequest:= utl_http.begin_request
(' '
,'POST'
,'HTTP/1.1');

utl_http.set_header(v_httpRequest, 'Content-Type', 'text/xml');
utl_http.set_header(v_httpRequest, 'Content-Length', length(v_soapRequest));
utl_http.set_header(v_httpRequest, 'SOAPAction', '');

utl_http.write_text(v_httpRequest, v_soapRequest);
v_httpResponse:= utl_http.get_response(v_httpRequest);
utl_http.read_text(v_httpResponse, v_soapResponse);
utl_http.end_response(v_httpResponse);
RETURN v_soapResponse;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm) ;
dbms_output.put_line('Error in fnc$_get_xml') ;
utl_http.end_response(v_httpResponse);
raise;
RETURN NULL;
END;

Great, we have now received the xml file that contains the result of our request. Now we want to retrieve the data we need, out of this XML file.
Therefore I will call the fnc$_handle_xml function with the received XM file as parameter.

I will have to handle the content of the xml file. To make this easier I am going to put the content of the XML file into an XMLType by using the XMLType.createXML function. Now the parameter resp contains the XML file. But I am only interested in a certain part of the XML file. By using the ‘extract’ function I am able to get a certain part out of the hierarchical structure of the XML file.

Next thing that I want to receive is the information of certain parts of this resp variable. I will put his in the resp1 variable which make it possible to always use the resp file for the next value I want to retrieve.

In this example I get 2 values out of the XML file. And I will return this back to the calling procedure.

FUNCTION fnc$_handle_xml(p_xml VARCHAR2)
RETURN r_info
IS
resp XMLType;
resp1 XMLType;
r_result r_info;
BEGIN
resp:= XMLType.createXML(p_xml);
resp:= resp.extract('/soap:Envelope/soap:Body/child::node()'
, 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);

resp1:= resp.extract('/multiRef[2]/<xml level>/text()'
, 'xmlns:ns2="<beans url>"'
);
IF resp1 IS NOT NULL THEN
r_result.value1 := resp1.getStringVal();
END IF;

resp1:= resp.extract('/multiRef[2]/<xml level>/text()'
, 'xmlns:ns2="<beans url>"'
);
IF resp1 IS NOT NULL THEN
r_result.value2 := resp1.getStringVal();
END IF;

RETURN r_result;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm) ;
RETURN r_result;
END;