How do we call an asp.net webservice from oracle, can we have a stored procedure for calling an asp.net webservice?
1 Answer
Here is a simple stored procedure that calls a simple ASP.NET addition web service from oracle which gives us the result of addition of two numbers which we supply from Oracle.
CREATE OR REPLACE PROCEDURE sp_WsAdd
(
P_NUM1 number,
P_NUM2 number
)
AS
V_INVELOP_TEMP VARCHAR2(32767);
V_INVELOP VARCHAR2(32767);
V_REQUEST UTL_HTTP.REQ;
V_RESPONSE UTL_HTTP.RESP;
V_WSDL_URL VARCHAR2(2000):='Address of your Webservice';
V_XML XMLTYPE;
BEGIN
V_INVELOP:='<?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>
<add xmlns="http://tempuri.org/">
<num1>'||P_NUM1||'</num1>
<num2>'||P_NUM2||'</num2>
</add>
</soap:Body>
</soap:Envelope>';
V_REQUEST:= UTL_HTTP.BEGIN_REQUEST(V_WSDL_URL, 'POST','HTTP/1.1');
UTL_HTTP.SET_BODY_CHARSET(V_REQUEST, 'UTF-8');
UTL_HTTP.SET_HEADER(V_REQUEST, 'Content-Type', 'text/xml');
UTL_HTTP.SET_HEADER(V_REQUEST, 'Content-Length', LENGTH(V_INVELOP));
UTL_HTTP.SET_HEADER(V_REQUEST, 'SOAPAction', 'http://tempuri.org/add');
UTL_HTTP.WRITE_TEXT(V_REQUEST, V_INVELOP);
V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST);
dbms_output.put_line('Response Received');
dbms_output.put_line('--------------------------');
dbms_output.put_line ( 'Status code: ' || V_RESPONSE.status_code );
dbms_output.put_line ( 'Reason phrase: ' || V_RESPONSE.reason_phrase );
UTL_HTTP.READ_TEXT(V_RESPONSE, V_INVELOP);
UTL_HTTP.END_RESPONSE(V_RESPONSE);
V_INVELOP := REPLACE(V_INVELOP,'xmlns="http://tempuri.org/"','');
V_XML:= XMLTYPE(V_INVELOP);
dbms_output.put_line('Value='||V_XML.extract('//addResult/text()').getStringVal());
commit;
END;
/
The web service for this Stored procedure is
[WebMethod]
public string add(int num1, int num2)
{
return (num1 + num2).ToString();
}