I got a problem where I was required to invoke a web service from Oracle database itself. From the net, I got multiple samples of code to do so using the UTL_HTTP package. The problem none worked for me. So I started from scratch :
- Created a HelloWorld web service in .NET 3.5 (VS 2008 SP1).
- Checked the service using the Internet Explorer browser. You should see a page similar to one below when you access the url http://localhost/myWeb/testws/Service.asmx?op=HelloWorld (the location of your service may be different):
- After lot of time spent on the internet, I found Oracle uses SOAP 1.1 to make web service calls. So look specifically in to the SOAP 1.1 Request format displayed on the browser.
- So now lets come to code:
declare
http_req utl_http.req;
http_resp utl_http.resp;
request_env varchar2(32767);
response_env varchar2(32767);
begin
request_env:='<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>
<HelloWorld xmlns="http://tempuri.org/" />
</soap:Body>
</soap:Envelope>'
dbms_output.put_line('Length of Request:' length(request_env));
dbms_output.put_line ('Request: ' request_env);
http_req := utl_http.begin_request('http://<your_url>/myWeb/testws/Service.asmx', 'POST', 'HTTP/1.1');
utl_http.set_header(http_req, 'User-Agent', 'Mozilla/4.0');
utl_http.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'Content-Length', length(request_env));
utl_http.set_header(http_req, 'SOAPAction', '"http://tempuri.org/HelloWorld"');
utl_http.write_text(http_req, request_env);
http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp, response_env);
utl_http.end_response(http_resp);
dbms_output.put_line('Response Received');
dbms_output.put_line('--------------------------');
dbms_output.put_line ( 'Status code: ' http_resp.status_code );
dbms_output.put_line ( 'Reason phrase: ' http_resp.reason_phrase );
dbms_output.put_line('Response: ');
dbms_output.put_line(response_env);
EXCEPTION WHEN UTL_HTTP.end_of_body THEN
utl_http.end_response(http_resp);
END; - Lets got through the code step by step:
- Declare some variables:
declare
http_req utl_http.req;
http_resp utl_http.resp;
request_env varchar2(32767);
response_env varchar2(32767);
begin - Create the SOAP request. It should exactly match what is displayed on the browser. Currenly I am not passing any input parameter to the service but if you need to, just copy paste the SOAP request from the browser and replace the placeholders mentioned in there with actual values.
request_env:='<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>
<HelloWorld xmlns="http://tempuri.org/" />
</soap:Body>
</soap:Envelope>';
dbms_output.put_line('Length of Request:' length(request_env));
dbms_output.put_line ('Request: ' request_env);
Just some debug statements at end to check all is set correctly. - Set the HTTP Request Parameters. The parameters should match the 1st statement displayed in your browser. The only difference is that the browser displays it in 2 statements : 1st with POST and second with a "Host" Soap Header Tag. For web service calls we will include the "Host" in the url itself.
http_req := utl_http.begin_request('http://<your_url>/myWeb/testws/Service.asmx', 'POST', 'HTTP/1.1');
Note: Please remember that your web service is on your localhost but the Oracle database is most likely not in your localhost but in some remote server. For the database server, your webservice hosted on your machine is a remote server so use the IP or hostname of your machine instead of "localhost" (spent a lot of time just solving this small issue ;)). - Set the Content-Type as 'text/xml' as displayed in the browser. You can set the charset too but I did not find it mandatory, so skipping it.
utl_http.set_header(http_req, 'Content-Type', 'text/xml'); - Set the Content-Length to length of the SOAP request. Luckily the length() function of Oracle does that for you. If you check in the Browser, it marks 'length' as a placeholder to be replaced by the actual value when the request is sent. So in Oracle we do so using the length function.
utl_http.set_header(http_req, 'Content-Length', length(request_env)); - Set the 'SOAPAction' field in your SOAP header. This is the namespace of your method appended by the actual method name (operation name). Currently its displayed on your browser. To find it out, you can open the WSDL for your service and browse to the end of the file. In there you will notice:
- <wsdl:binding name="ServiceSoap" type="tns:ServiceSoap">
<soap:binding transport="http://schemas.xmlsoap.org/soap/http" />
- <wsdl:operation name="HelloWorld">
<soap:operation soapAction="http://tempuri.org/HelloWorld" style="document" />
Thats where the soapAction is set.
utl_http.set_header(http_req, 'SOAPAction', '"http://tempuri.org/HelloWorld"'); - After setting all the SOAP Headers correctly, now write the SOAP Body for the request. As we have already written it in our 1st step, its just a copy paste function.utl_http.write_text(http_req, request_env);
- No request the response and read it. Finally close the http connection. I have received the response which is a UTL_HTTP Response object with all the XML DOM defined and with a lot of other functionality available to manipulate the object. To keep things simple, I have actually read the repose to a varchar object 'response_env'.
http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp, response_env);
utl_http.end_response(http_resp); - Thereafter all statements are just to print out the response to the DBMS Output.
- I have also caught the HTTP end of reponse exception just in case something goes wrong and closed the connection.
Hope this small piece of code helps you out in your first web service call from Oracle. Gradually you can use a lot of good code available on the internet to form your request and parse the response.
